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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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" <[email protected]> wrote in
> message news:[email protected]...
> >
> > 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
> >
>
>
>

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
>
>

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


"Casey" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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