Apache OpenOffice Community Forum

Posted: **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

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.

Posted: **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

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
	Else
		oCol.isVisible = True
	End If
next i

Posted: **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 !

Amalit

Apache OpenOffice Community Forum

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

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

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

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