[Solved] Auto Un/Hiding Columns Based on Values in Cells

Creating a macro - Writing a Script - Using the API

[Solved] Auto Un/Hiding Columns Based on Values in Cells

Postby Amalit » Mon Sep 14, 2015 6:14 am

I'm having trouble creating a macro to Hide and Unhide Columns in my spreadsheet based on the cell values in a single row; I really need help.

We switched from Excel and other MSOffice apps and the transition was smooth aside from all my macros. The macros I had in Excel mostly don't work now. This particular one was easily done in Excel; the codes seem to be a lot different in open source apps. This is the only feature I really miss, since Excel "coaches" you along and "holds your hand" while you're writing macros: there are no 'helping algorithms' in OO Basic to be sure :crazy:

Basically, I have a spreadsheet that has some monthly columns that trigger reports. I have a single drop-down selection cell that allows you to select a single month, all months or none at all. There is a row that then displays either a 1 or a 0 based on this for each month ( so if you select 'all' months, they all have ones, 'no' months would show all zeroes and if a single month were selected, they would all display zero except for that single month ).

*Cells with a "0" would auto-hide, those with a "1" would auto-show.

There are a couple threads here that dealt with hiding or displaying columns, but they are each approached differently in the macro coding with one hiding all columns to the right of a given column in a loop and another that uses an 'update' feature to make it automatic to show or hide columns ( unfortunately I couldn't make it work and it crashes the spreadsheet like crazy, but otherwise looked promising ).

It's frustrating, but if anyone has any ideas or an example sheet and codes, I would really appreciate it.
ScreenCap of crossover spreadsheet
Last edited by Hagar Delest on Thu Sep 17, 2015 9:54 pm, edited 1 time in total.
Reason: tagged [Solved].
OpenOffice 4.1 with Windows 8.1
Posts: 2
Joined: Mon Sep 14, 2015 2:27 am

Re: Auto Hiding / Unhiding Columns Based on Values in Cells

Postby FJCC » Mon Sep 14, 2015 6:32 am

This macro runs over columns A through Z of Sheet1 and if the value of the cell in the first row is equal to 0, it hides the column. If the cell has any other value the column is set to visible.
Code: Select all   Expand viewCollapse view
oSheets = ThisComponent.getSheets()
oSheet1 = oSheets.getByName("Sheet1")
oColumns = oSheet1.getColumns()
for i = 0 to 25
   oCol = oColumns.getByIndex(i)
   oCell = oSheet1.getCellByPosition(i,0) 'First row has index 0
   If oCell.Value = 0 Then
      oCol.isVisible = False
      oCol.isVisible = True
   End If
next i
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Posts: 7880
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Auto Hiding / Unhiding Columns Based on Values in Cells

Postby Amalit » Thu Sep 17, 2015 2:11 am

Thank you, FJCC

This works phenomenally fast and is a truly simple, elegant solution.

I first tried to change the language and force it onto my spreadsheet, where it failed miserably. Then I tried it on a blank sheet and voila !

I have to play around some more, but I think this is just the ticket.

Now I got to knuckle down and learn OO Basic, right, lol.

Thanks again, you can mark this one totally solved !

OpenOffice 4.1 with Windows 8.1
Posts: 2
Joined: Mon Sep 14, 2015 2:27 am

Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 6 guests