Listbox RowSource problem
 

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

--
Case

-----------------------------------------------------------------------
Casey's Profile: http://www.excelforum.com/member.php...nfo&userid=454
View this thread: http://www.excelforum.com/showthread.php?threadid=51545

Listbox RowSource problem
 

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" wrote in
message ...

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

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


--
Casey


------------------------------------------------------------------------
Casey's Profile:
http://www.excelforum.com/member.php...fo&userid=4545
View this thread: http://www.excelforum.com/showthread...hreadid=515450

Listbox RowSource problem
 

Using your named range:

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


HTH
"Rick Hansen" wrote:

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" wrote in
message ...

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

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


--
Casey


------------------------------------------------------------------------
Casey's Profile:
http://www.excelforum.com/member.php...fo&userid=4545
View this thread: http://www.excelforum.com/showthread...hreadid=515450

Listbox RowSource problem
 

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.


--
Casey


------------------------------------------------------------------------
Casey's Profile: http://www.excelforum.com/member.php...fo&userid=4545
View this thread: http://www.excelforum.com/showthread...hreadid=515450

Listbox RowSource problem
 

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

"Casey" wrote:


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.


--
Casey


------------------------------------------------------------------------
Casey's Profile: http://www.excelforum.com/member.php...fo&userid=4545
View this thread: http://www.excelforum.com/showthread...hreadid=515450

Listbox RowSource problem
 

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


------------------------------------------------------------------------
Casey's Profile: http://www.excelforum.com/member.php...fo&userid=4545
View this thread: http://www.excelforum.com/showthread...hreadid=515450

Listbox RowSource problem
 

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.


--
Casey


------------------------------------------------------------------------
Casey's Profile: http://www.excelforum.com/member.php...fo&userid=4545
View this thread: http://www.excelforum.com/showthread...hreadid=515450

Listbox RowSource problem
 

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


"Casey" wrote in
message ...

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.


--
Casey


------------------------------------------------------------------------
Casey's Profile:
http://www.excelforum.com/member.php...fo&userid=4545
View this thread: http://www.excelforum.com/showthread...hreadid=515450

Listbox RowSource problem
 

Sub UserForm1_Initialize()
should be:
Sub UserForm_Initialize()

If you wanted the initialize event to fire.

Casey wrote:

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.

--
Casey

------------------------------------------------------------------------
Casey's Profile: http://www.excelforum.com/member.php...fo&userid=4545
View this thread: http://www.excelforum.com/showthread...hreadid=515450

--

Dave Peterson

Listbox RowSource problem
 

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


--
Casey


------------------------------------------------------------------------
Casey's Profile: http://www.excelforum.com/member.php...fo&userid=4545
View this thread: http://www.excelforum.com/showthread...hreadid=515450