austin350s10
Active Member
- Joined
- Jul 30, 2010
- Messages
- 321
I am working on a user form that populates a combo box with data for a user to select. To make it user friendly I wanted to populate one column with data a user can understand such as a date.
The date a user selects should correspond to a worksheet name which I would like to put in the other column so my script can reference the worksheet name instead of the date the user selected.
Below is the code I started working on but soon realized that there was way more to this that I understand. Does anyone know how to adjust this code to do what I am looking for?
Also, I know there are some settings on the combo box properties that need to be adjusted from default to make this work but I am not too sure what changes are needed.
Lastly, once a user makes a selection how can I retrieve the worksheet name and assign it to a variable?
The date a user selects should correspond to a worksheet name which I would like to put in the other column so my script can reference the worksheet name instead of the date the user selected.
Below is the code I started working on but soon realized that there was way more to this that I understand. Does anyone know how to adjust this code to do what I am looking for?
Also, I know there are some settings on the combo box properties that need to be adjusted from default to make this work but I am not too sure what changes are needed.
Lastly, once a user makes a selection how can I retrieve the worksheet name and assign it to a variable?
Code:
Private Sub UserForm_Initialize()
Dim userName, invoiceDate, sheetName As String
userName = Worksheets("MainList").Range("C2").Value
For Each ws In ActiveWorkbook.Worksheets
If ws.Range("C6").Value = userName Then
invoiceDate = ws.Range("C4").Value
sheetName = ws.Name
comboOldInvoices.AddItem invoiceDate 'want this to be in the column 2
comboOldInvoices.AddItem sheetName 'want this to be in column 1
End If
Next ws
End Sub