Macro to hide cells based on outcome

Creating a macro - Writing a Script - Using the API

Macro to hide cells based on outcome

Postby jcc1981 » Tue Mar 07, 2017 7:00 pm

So, I've been tasked with creating an automated calculation for my work. I'm not a novice at excel, but I am with macros.

Ideally, I'd like for cells to be hidden based on the three possible outcomes determined in cell D39 [=IF(B36>E9,"MOVE TO PWD DISBURSEMENT TRACKING SHEET",IF(E9=B36,"STOP! NO FURTHER ACTION NECESSARY",IF(B36<E9,"MOVE TO STEP 4. Aid to be Disbursed or Returned")))]

If "MOVE TO PWD DISBURSEMENT TRACKING SHEET" - Rows 42-82 should be hidden
If "STOP! NO FURTHER ACTION NECESSARY" - Rows 42-103 should be hidden
If "MOVE TO STEP 4. Aid to be Disbursed or Returned" - Rows 83-103 should be hidden

Any help you can provide would be GREATLY appreciated!! :D

Jason
Office 2010, Windows 7
jcc1981
 
Posts: 2
Joined: Tue Mar 07, 2017 6:37 pm

Re: Macro to hide cells based on outcome

Postby JeJe » Tue Mar 07, 2017 7:29 pm

Hide cell contents in Calc Spreadsheets:

https://forum.openoffice.org/en/forum/viewtopic.php?f=101&t=54749

If you're after help with Excel, this forum maybe:

http://www.vbforums.com/forumdisplay.php?37-Office-Development
Openoffice 4.1.2
Windows 8
JeJe
 
Posts: 121
Joined: Wed Mar 09, 2016 2:40 pm

Re: Macro to hide cells based on outcome

Postby MrProgrammer » Tue Mar 07, 2017 10:41 pm

Hi, and welcome to the forum.

jcc1981 wrote:I'd like for cells to be hidden based on the three possible outcomes determined in cell D39
If you want cell contents conditionally hidden, use Format → Conditional Formatting and a cell style which specifies number format ;;; (three semicolons).

But your explanation suggests that you want rows to be hidden, not cells. The standard way in Calc to hide cells is with a filter. Data → Filter → Advanced filter will do what you need without the need for evil macros. Open the attachment. Change either B36 or E9. The text in D39 will change accordingly. To hide the desired rows, click A41 and use Data → Refresh Range.

If you need the latter actions automated, use Tools → Macros → Record Macro to record clicking A41 and refreshing. Also see [Tutorial] Binding a macro: Shortcut key, menu or toolbar.

If you want the rows to hide/unhide automagically, without a range refresh see [Tutorial] Sorting and Filtering data with formulas.

Read about conditional formatting and/or recording macros in Help → Index or in User Guides (PDF) or searching for topics about them in the Calc Forum.

[Tutorial] Ten concepts that every Calc user should know

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.
Attachments
201703071420.ods
(8.57 KiB) Downloaded 34 times
Last edited by MrProgrammer on Wed Mar 08, 2017 7:46 pm, edited 1 time in total.
Mr. Programmer
OpenOffice 3.2.0 Build 9483 on Mac OS X 10.9.5.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Volunteer
 
Posts: 3038
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Macro to hide cells based on outcome

Postby jcc1981 » Tue Mar 07, 2017 11:05 pm

Thanks for the information!!
Office 2010, Windows 7
jcc1981
 
Posts: 2
Joined: Tue Mar 07, 2017 6:37 pm


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 12 guests