populating a listbox

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

The listbox I have is populated by items on a worksheet. The items cannot be
moved in the worksheet because they are being used in a data validation. I
have code that links the listbox to the worksheet so when the item is
selected in the listox it is also selected on the worksheet. I need to be
able to reference only some of the list on the worksheet for the listbox.
e.g. I need say A6:A10 and A12:A15.
Any ideas?
 
You have a named range and change the named range before you call the user
form or you adjust the linked source and refresh the form.
 
Can you show me an example of this please?

Private Sub ListBox1_Click()
'This is the code for the listbox
'It links the listbox to the sheet
With activeworksheet
range("B63:B271")(UserForm1.ListBox1.ListIndex + 1).Select
End With
End Sub

This is my current code. I need my row source to include six columns.
B63:G271.
Not all the items in this range are needed.
 
This is the code that gets the items in. I now realise that you cannot have
multiple ranges and so therefore will either have to make another list for
the data or manually add it to the list.

Private Sub UserForm_Initialize()

ListBox1.ColumnCount = 2
ListBox1.RowSource = "Name"

ListBox1.ControlSource = "e1"
'Place the ListIndex into cell a6
ListBox1.BoundColumn = 0

End Sub
 
Back
Top