Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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). |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Fill a listbox with data from variable range | Excel Programming | |||
R1C1 seems to effect listbox fill in VBA | Excel Programming | |||
Fill a ListBox with items using VBA | Excel Programming | |||
For..next.. help to fill listbox | Excel Programming | |||
Fill a Listbox with Values from a Pivot Table Field - an Example | Excel Programming |