VBA listbox select worksheet by index

Question

I have a form with listbox which dynamically provides a list of the worksheets in the current workbook (code below). I wish to take the selected Sheet and refer to it in a formula later in the process. From hours of playing around I cannot seem to accomplish this. I believe I read somewhere that you cannot take the string back to the sub and use it to refer to to an object. So I thought maybe I can create two listboxes

1. for sheet name
2. for sheet index

that I could pass the index number to and maybe use that in my formula to lookup items from the correct sheet.

For the life of my I cannot seem to find a way to connect the two since the items will always be changing; the code will be ran on multiple workbooks by multiple operators so the layout will most likely change between users. I can easily add the second list box with index #'s but I have a block on how to associate the name which will have meaning to the user and the index which I can pass back to the sub. I realize the "On click" procedure for the list box to associate the two but with the dynamic nature of the fields I cannot come up with the logic to put that into code.

For N = 1 To ActiveWorkbook.Sheets.Count
    With ListBox1
        .AddItem ActiveWorkbook.Sheets(N).Name
    End With
Next N

Answer 1

Try this out.

Declare a public variable above the code for the UserForm, making it available throughout your workbook from any module or code.

Public listChoice As String

Using your code to get the sheet names for the ListBox rowsource.

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 an update event for the ListBox

Private Sub ListBox1_AfterUpdate()

    listChoice = ListBox1.Text

End Sub

I included a test just to demonstrate that the result is still retained. You don't need this, it demonstrates the results on the screenshot.

Private Sub cmdTestChoice_Click()

    MsgBox ("The choice made on the ListBox was: " & listChoice)

End Sub

edit: To access that sheet later, you can call it using something like this:

Some examples of different ways to access a cell, using .Range, or .Cells, with numbers or letters.

Using lRow & 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")