Is there an easier way to fill this listbox
 

I want to fill it with the first row values of the active sheet . I came up
with this but isn't there a way to set the row source value?
Also, this is a listbox in a dialog that I want to load when the dialog
loads, is there a onload event or something that I can use to trigger this?

Thanks

Public Sub Load()

Dim Rng As Range
Const rw As Long = 1

With Sheets(ActiveSheet.Name)
If IsEmpty(ActiveSheet.Cells(rw, 1)) Then
Set Rng = .Cells(rw, 1)
Else
Set Rng = .Cells(rw, Columns.Count).End(xlToLeft)(1, 2)
End If
End With

Dim i As Integer
For i = 1 To Rng.Column - 1
ListBox_Describe.AddItem Cells(1, i)
Next i

End Sub

Is there an easier way to fill this listbox
 

"D" wrote in message
...
I want to fill it with the first row values of the active sheet . I came
up
with this but isn't there a way to set the row source value?

Public Sub Load()
Dim Rng As Range
Const rw As Long = 1

With ActiveSheet
If IsEmpty(.Cells(rw, 1)) Then
Set Rng = .Cells(rw, 1)
Else
Set Rng = .Cells(rw, Columns.Count).End(xlToLeft)(1, 2)
End If
End With

ListBox_Describe.RowSource = Rng.Address(False, False)

End Sub

Also, this is a listbox in a dialog that I want to load when the dialog
loads, is there a onload event or something that I can use to trigger
this?

There is a Userform_Activate event that fires when the form is shown, and a
Userform_Initialize event that fires when the form is loaded into memory
(but not necessarily shown).

Is there an easier way to fill this listbox
 

"Bob Phillips" wrote in message
...

"D" wrote in message
...
I want to fill it with the first row values of the active sheet . I came
up
with this but isn't there a way to set the row source value?

Public Sub Load()
Dim Rng As Range
Const rw As Long = 1

With ActiveSheet
If IsEmpty(.Cells(rw, 1)) Then
Set Rng = .Cells(rw, 1)
Else
Set Rng = .Cells(rw, Columns.Count).End(xlToLeft)(1, 2)
End If
End With

ListBox_Describe.RowSource = Rng.Address(False, False)

End Sub

Also, this is a listbox in a dialog that I want to load when the dialog
loads, is there a onload event or something that I can use to trigger
this?

There is a Userform_Activate event that fires when the form is shown, and
a
Userform_Initialize event that fires when the form is loaded into memory
(but not necessarily shown).



Thanks Bob

Unfortunatel the Rng.Address didnt load the list box. I found this piece of
code that does it though
With ActiveSheet
ListBox_Describe.List = Application.Transpose(.Range(.Range("A1"),
..Range("A1").End(xlToRight)).Value)
End With

I've got to read up on this more, I can see that.


Happy New Year.

Is there an easier way to fill this listbox
 

It did in my tests. There must be more to this :-).

--

HTH

RP
(remove nothere from the email address if mailing direct)


"D" wrote in message
...

"Bob Phillips" wrote in message
...

"D" wrote in message
...
I want to fill it with the first row values of the active sheet . I
came
up
with this but isn't there a way to set the row source value?

Public Sub Load()
Dim Rng As Range
Const rw As Long = 1

With ActiveSheet
If IsEmpty(.Cells(rw, 1)) Then
Set Rng = .Cells(rw, 1)
Else
Set Rng = .Cells(rw, Columns.Count).End(xlToLeft)(1, 2)
End If
End With

ListBox_Describe.RowSource = Rng.Address(False, False)

End Sub

Also, this is a listbox in a dialog that I want to load when the dialog
loads, is there a onload event or something that I can use to trigger
this?

There is a Userform_Activate event that fires when the form is shown,
and
a
Userform_Initialize event that fires when the form is loaded into memory
(but not necessarily shown).



Thanks Bob

Unfortunatel the Rng.Address didnt load the list box. I found this piece
of
code that does it though
With ActiveSheet
ListBox_Describe.List = Application.Transpose(.Range(.Range("A1"),
.Range("A1").End(xlToRight)).Value)
End With

I've got to read up on this more, I can see that.


Happy New Year.