Hide a column based on cells value

[Dennis1234]

I’m famailar with VBA and some aspects of macro in MS applications, not as much in Libreoffice. I have a sheet with about 12 or so columns. I need to have hide cells based on the value in the cell. Currently, I have it set up with a list using Validity. It is the list of months. I would like to hide or show columns associated with the particular month. Say, I select Feb in cell A1 and columns B, D, E, F etc… hide and column C has no change (the column for Feb).

First I thought I would set up a simple macro that hides cells and I would try to re-code it to do what I needed, then fiddle with it until I got the hide and show features, suspect lots of if then statements.
I could have a button for each month with it’s own macro, but not the best option.

This is what I have so far.

sub Hide2
dim document   as object
dim dispatcher as object
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
dispatcher.executeDispatch(document, ".uno:HideColumn", "", 0, Array())
end sub

I found this on this forum, kinda what I need, but based on rows, was going to replace rows with colums.

Sub MyDoLoop
Dim oCell as Object, oSheet as Variant, oSheets as Variant, oString as String
oSheets = ThisComponent.getSheets(0)
oSheet = oSheets.getByIndex(0)
oRows = oSheet.Columns
oRow = oRows.GetByIndex(0)
oCell = oSheet.GetCellByPosition(0, 0)
oString = oCell.getString()
If oString = "Hello" Then
oRow.IsVisible = False
Else
oRow.IsVisible = True
End If
End Sub

[jimk]

The code works – it hides column A if A1 equals Hello. So what is the question?

[Dennis1234]

Well, according to someone else it ‘worked’ and you did read the original question, right? I did exchange all the words row(s) with Column(s) and it did not work to hid the column when the word hello was entered in A1.

I’m looking to hide the columns (months) that is not selected in a cell. ie A1 says Mar or 3 and the columns for Jan-Feb, Apr-Dec hide.

Thanks

[jimk]

Enter “Hello” (upper case) in A1, not “hello” (lower case), and then running the macro hides the column.

[Lupp]

You may check this attached example.
However, if relying on user code in LibreOffice you should study the API. It is very different from MS Office / VBA.
Unfortunately I do not know a good compendium (and I#m not an expert at all).
The API can be accessed from different programming languages for which the needed bridges exist (see developers guide). Emphasizing the ways LibO BASIC does it, you may start with the famous texts by Andrew Pitonyak (“Book” and “Useful Macro Information”). They are available here.

[Dennis1234]

I could not get that macro to work on your example. I’ve seen that book you referenced, not light reading :), but will have to take a look at it nonetheless.

[Lupp]

The macro (it’s a user function) worked flawlessly in the attached example here.
Did you not succeed with moving the function code elswhere and to apply it in one of your spreadsheets?

[librebel]

Hello @Dennis1234,

in writing a macro involving months, it would be better to work with the month number instead of local month names.
For this purpose it is assumed here below that your cell A2 contains the month number ( = 2 ) that corresponds to your month name ( =“Feb” ) in cell A1.

In that case you could write the following formula into any unused cell:

=HIDECOLUMNSEXCEPT(1;12;A2)

using the Basic function:

Function hideColumnsExcept( lColumnStart As Long, lColumnEnd As Long, lColumnExcept As Long )
REM Hides a range of Columns except one Column in the currently active Sheet.
REM CALC FORMULA : =HIDECOLUMNSEXCEPT(1;12;2)	REM Hides columns B to M except C.
	Dim i As Long
	For i = lColumnStart To lColumnEnd
		ThisComponent.CurrentController.ActiveSheet.Columns.getByIndex( i ).IsVisible = ( i = lColumnExcept )
	Next i
End Function

[Dennis1234]

Thanks so much, I’ll give this a try. It looks better to use the name, but I can change the formatting of the cell that is ‘used’ and have another cell do the work.

Thanks,
d

[Dennis1234]

I added a new macro and called it Macro9, placed the code in between the sub, endsub. I labeled B1 as 1 and C1 as 2 etc… to 12
I entered the =HIDECOLU… function in a cell below, it gave me an #name error.
Then in A2 I entered numbers, 1 and then 2 and then 3 etc, no change.
D

[librebel]

The macro should be called “hideColumnsExcept” … else the calc formula cannot find it.
The macro is a function, even tho it doesn’t return anything.
Just Copy-paste it integrally.

[Dennis1234]

I figured that out and got rid of the sub, end sub. The macro is called hideColumnsExcept. Unfortunately, it still does not work.
When I double click on it on tools, macro, I get and error (basic runtime error. Argument is not optional. at this location: For i = lColumnStart To lColumnEnd

d

[librebel]

yes, because this macro has non-optional arguments which must be specfied within the call. ( Which is not possible via the Run Macro dialog ).

That’s why i gave the example call from a Calc Formula, where all the required arguments are specified within brackets.
NB. in my locale (NL) the arguments are separated by a semicolon ( “;”) , in other locales this could be a comma (",") …

[Dennis1234]

Am I not labeling the cells correctly, or using the correct cell to pass the information to the function. In what cells are the column months and what cell is the cell that in the input. Which part of the code in the function points to the cell I’m manipulating. (1;12;A2) ?

D

[Dennis1234]

Ok, so I started over with a test sheet. I got it to work with placing a 2 in A2, it hid a bunch of rows. When I went to show them, I got errors.

What I’m looking to do is place a number or month name in a particular cell and have the other 11 columns hide. Then when need be, put another number in there and have the other 11 columns hide and the that one show.

Thanks so far, for your help.

[librebel]

That is exactly what the above macro + formula does.

From all the columns ranging from 1 to 12 ( that is from B to M) , it hides 11 columns and shows 1 column based on the number inside cell A2.

When i show the hidden columns again, it does not result in errors.