PDA

View Full Version : Populate list box from another sheet



wibbers2000
11-25-2005, 06:38 AM
I have a workbook with 8 sheets in.

the first 7 are days in the week. The 8th has some reference data.

I would like to create a user form for populating data into the weekdays sheets.

I would like to put on this form a list box that only allows certain data to be entered. The criteria for that data is on sheet 8 column 'A'

How do I link the listbox1 to the information on page 8.



Regards

Killian
11-25-2005, 07:49 AM
Here's a possible method...

Set a name the range you want to use (e.g. "MachineNames")
In the user form initialize event:
Declare a variant
Assign the named range array to the variant
Assign the variant to the Listbox's List property
Dim varMachineNames

varMachineNames = Sheets(8).Range("MachineNames")
ListBox1.List() = varMachineNames
ListBox1.ListIndex = 0

johnske
11-25-2005, 08:44 AM
Without using range names...Option Explicit

Private Sub UserForm_Activate()
Dim M As Long
With Sheet6
For M = 1 To .Range("A" & Rows.Count).End(xlUp).Row
ListBox1.AddItem .Range("A" & M)
Next M
End With
End Sub{The eighth sheet in your example has the code name Sheet6)}