how to initialize a listbox in excel
how to initialize a listbox in excel
(OP)
If only someone could give me a start. I want to use combos or listboxes in my spreadsheets. I know how to code the events attached to this kind of controls. What I havent yet been able to figure out(I need too much time for this) is how can I for example fill the box initially with bolt grades for user to choose from.
Thanks in advance.
Thanks in advance.
RE: how to initialize a listbox in excel
Open the Forms toolbar and select listbox. Your curser is now a black cross. Drag on the spreadsheet the size of listbox you want. Right click on the listbox and select Format Control.
Select the Control tab on the Format Control window. The Input Range would then be entered as A1:A3. The Cell Link is the cell where you want your selected choice to be indicated. Perhaps you enter A4 for the cell link. Now, when you pick on one of the items in the box, Cell A4 reports the number of the item you select.
Hope this is what you were looking for.
RE: how to initialize a listbox in excel
RE: how to initialize a listbox in excel
Assume that your list is on a sheet called DataSheet. The first row (A1) contains the heading for the list and the first data item is on row 2 (A2).
Add this code to your form. Then, if you need to add to your list, just fill in the next empty cell in that row. Next time the program is run, you will have an updated list.
Private Sub UserForm_Activate()
Dim sVal As String
Dim dataSheet As Worksheet
Dim iDataRow As Long
Set dataSheet = Sheets("DataSheet")
'Fill in the listbox
ListBox1.Clear
'Column A contains the item list (Row 1 is the column header)
iDataRow = 2 'first item in the list
Do While Len(dataSheet.Range("A" & iDataRow).Text) > 0
sVal = dataSheet.Range("A" & iDataRow).Text
ListBox1.AddItem sVal
iDataRow = iDataRow + 1
Loop
End Sub
This will enable other users to simply add data with no knowledge of programming.
Hope this helps!
RE: how to initialize a listbox in excel
Option Explicit
Option Base 1
'Module level array
Dim sList()
Private Sub Worksheet_Activate()
Dim i As Long
Call GetDataList
ListBox1.Clear
For i = LBound(sList) To UBound(sList)
ListBox1.AddItem sList(i)
Next i
ReDim sList(1)
End Sub
Private Sub GetDataList()
Dim iItems As Long
Dim iDataRow As Long
Dim dataSheet As Worksheet
Set dataSheet = Sheets("DataSheet")
ReDim sList(1)
iDataRow = 2 'first item in the list
iItems = 0
Do While Len(dataSheet.Range("A" & iDataRow).Text) > 0
iItems = iItems + 1
ReDim Preserve sList(iItems)
sList(iItems) = dataSheet.Range("A" & iDataRow).Text
iDataRow = iDataRow + 1
Loop
Set dataSheet = Nothing
End Sub
RE: how to initialize a listbox in excel
Keep yourself in the forum among Ron and JAE. Thanks and all the best. Simply said, you are saving us big chunks of time.
RE: how to initialize a listbox in excel