Ask Your Question

Populate ComboBox from range of Cells IF

asked 2019-05-28 16:47:28 +0100

Gumbi gravatar image

updated 2020-07-20 23:02:12 +0100

Alex Kemp gravatar image

I have a dialog box with a combobox in it that I use for data entry.

I am populating the combobox with range P10:P131, which is quite a number of options. This range includes quite a few empty cells, as well as quite a few hidden rows.

Can you help me write an IF statement in this macro that will eliminate the cells in the hidden rows from the combo box, as well as the empty cells?

What I have adds ALL the cells in the range, including the empty cells and the cells in the hidden rows.

listbox = oDialog1.getControl("ComboBox1")

With ThisComponent.CurrentController.ActiveSheet
    For Each cell in .getCellRangeByName("P10:P131").DataArray
    listbox.addItems (cell, 1)
    Next cell
End with
edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2019-05-28 20:25:29 +0100

Gumbi gravatar image

I was able to figure it out, with snippets of code I found elsewhere. If the cell contains anything [Len(s) > 0] and if the row is visible, it will add the string in the cell to the Listbox/ComboBox.

oSheet = ThisComponent.CurrentController.ActiveSheet
oRange = ThisComponent.CurrentController.ActiveSheet.getCellRangeByName("P10:P131").RangeAddress
ip = 0
For i = 0 To oRange.EndRow - oRange.StartRow
    s = oSheet.getCellByPosition(oRange.StartColumn, oRange.StartRow + i).String
    r = oSheet.getCellByPosition(oRange.StartColumn, oRange.StartRow + i)
    If Len(s) > 0 And r.Rows.IsVisible = True Then
       listbox.addItem(s, ip)
       ip = ip + 1
    End If
Next i
edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-05-28 16:47:28 +0100

Seen: 166 times

Last updated: May 28 '19