This is function that i have put in my userform module. The idea is to populate listbox from a select range, and remove empty items within userform_init module.
Private Sub RemoveEmptyRows(lst As ListBox) 'make sure this is not an activex listbox
Dim i As Long
With lst
For i = .ListCount - 1 To 0 Step -1
If .List(i) = Empty Then
.RemoveItem i
End If
Next
End With
End Sub
I have all of my code in userform. My object hierarchy is as follows: FindReplace (Userform) => FANumbers (Listbox)
When I try to use the function in userform_init procedure to clear empty items in the listbox, I get Object Required error?
RemoveEmptyRows(Me.FANumbers)
This doesn't work even when I change data type from msforms.listbox to listbox in the function parameter.
I also tried removing Me but still gives the same error.
Now there is an alternative way that works such as:
'With Me.FANumbers
'For r = .ListCount - 1 To 0 Step -1
'If .List(r) = "" Then .RemoveItem r
'Next r
'End With
But I am trying to learn how to pass a value to a function for my own knowledge sake. Please guide me how to pass the listbox argument.
Bookmarks