I'm trying to "write a macro" that will allow me to hide several rows of a spreadsheet, based on which value is selected in a given cell that contains a selection list.
Actually I've copy-and-pasted my way up to hiding a single row by pressing my macrobutton. One (me) might expect that from here it's not very to toggling multiple rows, but... I am struggling with all the API reference stuff and decided to place my lame question here instead.
Here's what I've got so far:
Code: Select all
Sub Main
oSheets = ThisComponent.getSheets() ' this seems to be the reference to the open spreadsheet document
oSheet1 = oSheets.getByName("zeiten") ' that is my actual sheet
oRows = oSheet1.getRows ' these are, afaics all rows in that sheet
oChoice = oSheet1.getCellRangeByName("G1").String ' this is the cell that defines which rows shall be visible or not
oRow = oRows.getByIndex(3) ' this is row number 4. Here's the point I'm struggling
If oChoice = "Berlin" Then
oRow.isVisible = False
Else
oRows.isVisible = True
End If
End Sub
- How do I fill "oRow" with multiple values. I tried comma separated, didn't work. Would I need to define "oRow" as an array or a list beforehand?
- what's it with the lowercase characters prefixed to the variable names. I come across this in many examples and copy-pasted it right away, but is this something necessary or just a community convention?
Cheers,
Stephan
##
solved:
So. I admit I find it hard to google my way to effective results. Nonetheless, right after sending my post I skimmed some older posts here and came up with a solution that works. I am absolutely sure that it's not very elegant in terms of programming logic and so on. But just for the record (maybe someone will face the same lame problem that I did).
I initialized a variable as an array, put the lines in absolute numbers into that array, and have a for loop go over the content of that array later -> works.
Here's how it looks like:
Code: Select all
REM ***** BASIC *****
Sub Main
berl() = Array(4,6,10,11,12,13,15,21,23,24)
muni() = Array(5,7,8,9,14,16,17,18,19,20,22,25,26,27)
oSheets = ThisComponent.getSheets()
oSheet1 = oSheets.getByName("zeiten")
oRows = oSheet1.getRows
oChoice = oSheet1.getCellRangeByName("G1").String
oRow = oRows.getByIndex(3)
If oChoice = "Munich" Then
for each n in berl()
oRow = oRows.getByIndex(n)
oRow.isVisible = False
next
for each n in muni()
oRow = oRows.getByIndex(n)
oRow.isVisible = True
next
ElseIf oChoice = "Berlin" Then
for each n in muni()
oRow = oRows.getByIndex(n)
oRow.isVisible = False
next
for each n in berl()
oRow = oRows.getByIndex(n)
oRow.isVisible = True
next
Else
for each n in berl()
oRow = oRows.getByIndex(n)
oRow.isVisible = True
next
for each n in muni()
oRow = oRows.getByIndex(n)
oRow.isVisible = True
next
End If
End Sub
Nonetheless, if one spots any severe mistakes or has hints, feel free to still answer. I'll be happy for everything.
Cheers
Stephan