Populating a list box with specific ranges

[Graham Whitehead]

Hi, i have a program which scans for data and then collects a list in two
columns. One column denoting a name and the adjescent column denoting the
number of occurances. The length of the list can vary quite dramatically.
My question is how to add this range into a list box at runtime. Can anyone
help? Thanks.

 

[Guest]

Hi Graham,
This is an example written by the macro recorder.
Range("E1").Select '<--ADJUST!
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=$A$13:$A$18"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub

When you defined your range, replace "=$A$13:$A$18" by the string describing
your range!

Regards,
Stefi


„Graham Whitehead†ezt írta:

 

[Guest]

assume the upper left corner of the list/range will be in Cell B9 on sheet
Data and the range will be surrounded by at least one blank column/row on
each side. The list is already built when the useform is shown. the listbox
is on a userform:

Private Sub Userform_Initialize()
with worksheets("Data")
set rng = .range("B9").CurrentRegion
End with
listbox1.RowSource = rng.Address(1,1,xlA1,True)
Listbox1.Columncount = 2
end Sub