UserForm and ListCount problem

[DonAndress]

Hello.

I'm trying to prepare a UserForm with a list of cells - easy.

Now, I need to add a button to select all non-empty cells from the ListBox1.

I know that the list always starts from cell A4, I don't know how long the list is (each macro run may generate different amount of values), there are no empty cells between cells with values.

Right now I have it defined in the ListBox1 properties: RowSource = Sheet1!A4:A100
Then a default code for a button:

For i = 0 to ListBox1.ListCount - 1
ListBox1.Selected(i) = True
Next i

But it selects all 96 cells defined above.

How do I limit only to non-empty cells?

 

[Dryver14]

If the amount of rows are variable would a combo box not be better

 

[DonAndress]

No really.
I wanted to make it possible for the user to see all values and choose between selecting all of them or just the values of his choice.
Considering this, it's not very comfortable to see only a dropdown list from a combobox instead of a list of all values at the same time in a listbox.

 

[Dryver14]

Rcel = sheets(1).cells(rows.count,1).end(xlup).row
UserForm1.ListBox1.RowSource = "Sheet1!A4:A" & Rcel.Row

 

[DonAndress]

Thanks a lot, but could you please also advise where to put this code?

 

[Dryver14]

I could be proven wrong on this as I have not done it before.

Place it in the listbox click form control.

If you have changed the name of your listbox then change the ListBox1 to match the click name,

also drop the row from UserForm1.ListBox1.RowSource = "Sheet1!A4:A" & Rcel.Row

 

[DonAndress]

But should I remove my defined range from the properties pane?
If I leave it there your code doesn't work.
If I remove it from the properties pane there is no list in the listbox.

 

[Norie]

Try this.

Private Sub UserForm_Initialize

   Listbox1.RowSource = ""

   With Sheets(1)
       Listbox1.List = .Range("A4", .Range("A" & Rows.Count).End(xlUp))
   End With

End Sub

 

[DonAndress]

I think UserForm_Initialize returns error.
It's hard to determine as the debugger can't get inside the code of the ListBox and just stops in ListBox1.Show.

 

[Norie]

Just a typo, should be this.

Option Explicit

Private Sub UserForm_Initialize()

   ListBox1.RowSource = ""

   With Sheets(1)
       ListBox1.List = .Range("A4", .Range("A" & Rows.Count).End(xlUp)).Value
   End With

End Sub