Listbox RowSource problem

[Casey]

Hi,
I'm trying to set up a listbox and I have a fair amount of VB
experience but I hardly ever use UserForms. So even the basics ar
escaping me.
I want to populate the Listbox with a Row of column headers. Thes
headers may change in the future, so I'm reluctant to hard code them.
have named this range (A1:H1); ScopeTitles. I have tried setting th
RowSource in the properties dialog box in the following ways all o
which result in only the first header (in A1) ending up in th
listbox.
Here's what I've tried

=ScopeTitles
=Scopes!$A$1:$H$1 (Scopes is the sheet name)
ScopeTitles (with no = sign

 

[Rick Hansen]

Casey, try this small bit of code. Add this code in the UserForm_
Initialize() event. This code Initialize The listbox ever time the UserForm
is Opened. Good Luck.

HTH, Rick



sub UserForm1_Initialize()
Dim Ws as Worksheet
Dim x as Integer

Set ws1 = Worksheets("Sheet1") ''<< Change to Sheet Name
Me. ListBox1.Clear
Me.ListBox1.RowSource=""

For x = 1 to 8 '' Col's A thru H
Me.ListBox1.AddItem ws1.Cells(1,x)
Next x



End Sub

 

[Guest]

Using your named range:

Private Sub UserForm_Initialize()
ListBox1.Clear
For Each cell In Range("ScopeTitles")
ListBox1.AddItem cell.Value
Next
End Sub


HTH

Casey, try this small bit of code. Add this code in the UserForm_
Initialize() event. This code Initialize The listbox ever time the UserForm
is Opened. Good Luck.

HTH, Rick



sub UserForm1_Initialize()
Dim Ws as Worksheet
Dim x as Integer

Set ws1 = Worksheets("Sheet1") ''<< Change to Sheet Name
Me. ListBox1.Clear
Me.ListBox1.RowSource=""

For x = 1 to 8 '' Col's A thru H
Me.ListBox1.AddItem ws1.Cells(1,x)
Next x



End Sub

 

[Casey]

Rick,
Thanks for the reply but no joy.
Maybe I'm putting the code in the wrong place. What I did was right
click the UserForm1 that contains my Listbox and picked view code and
pasted your code in there with the following changes.

Option Explicit

Sub UserForm1_Initialize()
Dim Ws As Worksheet
Dim x As Integer

Set ws1 = Worksheets("Scopes") ''<< Change to Sheet Name
Me.ListBox1.Clear
Me.ListBox1.RowSource = ""

For x = 1 To 8 '' Col's A thru H
Me.ListBox1.AddItem ws1.Cells(1, x)
Next x

End Sub

But when I run the code the UserForm shows but the ListBox is empty.

 

[Guest]

Casey,
It should be Userform_Initialize not Userform1: see my
previous posting.

 

[Casey]

Toppers,
Thank you. That worked great after I declared the variable.
Below is my version of the code.

Option Explicit

Private Sub UserForm_Initialize()
Dim Cell As Range
ListBox1.Clear
For Each Cell In Range("ScopeTitles")
ListBox1.AddItem Cell.Value
Next
End Sub

Thanks again to you Topper and Rick for the responses. They are greatly
appreciated.

 

[Casey]

Topper,
I'm confused as to what you are referring to. In both of your posts you
used Userform_Initialize. Or am I missing something? And the code is
working fine.

 

[Rick Hansen]

Hey Casey, Topper is Correct, Change UserForm1_Intialize() to
UserForm_Intialize(). This a event procedure for the UserForm.

 

[Dave Peterson]

Sub UserForm1_Initialize()
should be:
Sub UserForm_Initialize()

If you wanted the initialize event to fire.

 

[Casey]

I see what you were refering to. In Rick's original code.
Thanks for the help gentlemen.