VBA list sheet list by index
I have a combo box form that dynamically provides a list of worksheets in the current workbook (code below). I want to take the selected sheet and refer to it in the formula later. Since I have been playing, I cannot do this. I believe I read somewhere that you cannot take a string back into a sub and use it to reference an object. So I thought maybe I can create two lists
- for sheet name
- for leaf index
so that I can pass in the index number and maybe use it in my formula to find the elements from the sheet I want.
During my life, I cannot find a way to link them, for the elements will always change; the code will be run by multiple books by multiple operators, so the layout is likely to change between users. I can easily add a second list with index #, but I have a block on how to associate a name that will be meaningful to the user and an index that I can pass back. I understand the On-Click procedure for a list box to link the two, but with the dynamic nature of the fields, I can't think of any logic to put that in code.
For N = 1 To ActiveWorkbook.Sheets.Count
With ListBox1
.AddItem ActiveWorkbook.Sheets(N).Name
End With
Next N
source to share
Try it.
Declare a public variable above the code for the UserForm, making it available in your book from any module or code.
Public listChoice As String
Using your code to get the sheet names for the ListBox row source.
Private Sub UserForm_Activate()
For n = 1 To ActiveWorkbook.Sheets.count
With ListBox1
.AddItem ActiveWorkbook.Sheets(n).name
End With
Next n
End Sub
Including update event for ListBox
Private Sub ListBox1_AfterUpdate()
listChoice = ListBox1.Text
End Sub
I have included a test to demonstrate that the result is still valid. You don't need this, it shows the results in the screenshot.
Private Sub cmdTestChoice_Click()
MsgBox ("The choice made on the ListBox was: " & listChoice)
End Sub
edit: To access this sheet later, you can invoke it using something like this:
Some examples of different ways to access a cell using .Range or .Cells with numbers or letters.
Using lRow and lCol as Long to set row and column numbers.
Sheets(listChoice).Cells(lRow, lCol).Value = TextBox1.Value 'Set cell on sheet from TextBox
TextBox2.Value = Sheets(listChoice).Range("A2").Value 'Set TextBox From Cell on Sheet
'Set a cell on another sheet using the selected sheet as a source.
Sheets("AnotherSheet").Cells(lRow, "D") = Sheets(listChoice).Range("D2")
source to share