Hi,

Trying to get a listbox to work in a form... but not quite there yet!

The list should read the info from a range, but it empty... The code I
am using is below

Private Sub clients_select_Initialize()
Dim rng As Range, cel As Range
Set rng = ActiveWorkbook.Sheets("clients").Range("b2:b200")
With Me.clients_select_list
.Clear
For Each cel In rng.Cells
.AddItem cel.Value
Next
End With
End Sub

thanks a million

I'm trying to test your code, but what is the name of the form and
what is the name if the listbox?

--JP

On Oct 6, 11:29*am, PA wrote:
Hi,

Trying to get a listbox to work in a form... but not quite there yet!

The list should read the info from a range, but it empty... The code I
am using is below

Private Sub clients_select_Initialize()
* * Dim rng As Range, cel As Range
* * Set rng = ActiveWorkbook.Sheets("clients").Range("b2:b200")
* * With Me.clients_select_list
* * * * .Clear
* * * * For Each cel In rng.Cells
* * * * * * .AddItem cel.Value
* * * * Next
* * End With
End Sub

thanks a million

Hi thanks for your (very) quick reply!!!

My form is "clients_select" and the list is "clients_select_list"

Thanks-

PA

Provided everything else is correct replace ".AddItem cel.Value" with
".AddItem(cel.Value)"

Regards,
The Code Cage Team
http://www.thecodecage.com/forumz/

"PA" wrote:

Hi,

Trying to get a listbox to work in a form... but not quite there yet!

The list should read the info from a range, but it empty... The code I
am using is below

Private Sub clients_select_Initialize()
Dim rng As Range, cel As Range
Set rng = ActiveWorkbook.Sheets("clients").Range("b2:b200")
With Me.clients_select_list
.Clear
For Each cel In rng.Cells
.AddItem cel.Value
Next
End With
End Sub

thanks a million

You should not try to type the procedure headers in long hand; rather, use
the dropdown box on the left side of the code window to pick the object and
use the dropdown box on the right side of the code window to pick the event
procedure. If you would have done that, you would have found that the proper
header for the UserForm's Initialize event was not this...

Private Sub clients_select_Initialize()

but, rather, that it was this instead...

Private Sub UserForm_Initialize()

Also, this part of the code (the header and footer) would not have required
you to type anything (especially useful for events having long headers such
as MouseDown).

--
Rick (MVP - Excel)


"PA" wrote in message
...
Hi,

Trying to get a listbox to work in a form... but not quite there yet!

The list should read the info from a range, but it empty... The code I
am using is below

Private Sub clients_select_Initialize()
Dim rng As Range, cel As Range
Set rng = ActiveWorkbook.Sheets("clients").Range("b2:b200")
With Me.clients_select_list
.Clear
For Each cel In rng.Cells
.AddItem cel.Value
Next
End With
End Sub

thanks a million

No, you should not use parentheses here nor anywhere else where they are not
required by syntax. While it would work here without problem, doing so can
get you in trouble in other situations (by either generating errors for
calls requiring multiple arguments or generating incorrect results in ByRef
arguments used to pass values from the called code back into the calling
code).

--
Rick (MVP - Excel)


"The Code Cage Team" wrote in
message ...
Provided everything else is correct replace ".AddItem cel.Value" with
".AddItem(cel.Value)"

Regards,
The Code Cage Team
http://www.thecodecage.com/forumz/

"PA" wrote:

Hi,

Trying to get a listbox to work in a form... but not quite there yet!

The list should read the info from a range, but it empty... The code I
am using is below

Private Sub clients_select_Initialize()
Dim rng As Range, cel As Range
Set rng = ActiveWorkbook.Sheets("clients").Range("b2:b200")
With Me.clients_select_list
.Clear
For Each cel In rng.Cells
.AddItem cel.Value
Next
End With
End Sub

thanks a million

The correct code should be:

Private Sub UserForm_Initialize()
Dim rng As Range, cel As Range
Set rng = ActiveWorkbook.Sheets("clients").Range("b2:b200")
With Me.clients_select_list
.Clear
For Each cel In rng.Cells
.AddItem cel.Value
Next
End With
End Sub

You should review Rick's post -- never try to type out the names of
objects on your form, they are all available from the dropdowns at the
top of the code box.

--JP

On Oct 6, 11:49*am, PA wrote:
Hi thanks for your (very) quick reply!!!

My form is "clients_select" and the list is "clients_select_list"

Thanks-

PA