Selecting Sheet via Input Box or other way

[jlkamikaze]

my apologies for poorly wording the thread title but i have yet to actually determine a possible way of performing this function

i need to be able to Set a variable to specific worksheet. possibly via Input Box or some other form

are there any other such ways of propting a user to choose a worksheet [or also row or column] besides an input box? thank you!

[JKwan]

well here is a method:

Private Sub ListBox1_Click()
Dim WS As Worksheet
With Me.ListBox1
   Set WS = Worksheets(.List(.ListIndex))
   MsgBox "You selected - " & WS.Name
End With
WS.Select
Set WS = Nothing
End Sub

Private Sub UserForm_Initialize()
Dim WS As Worksheet
Dim WB As Workbook
Set WB = ThisWorkbook
For Each WS In WB.Worksheets
   Me.ListBox1.AddItem WS.Name
   Next WS
   Set WS = Nothing
   Set WB = Nothing
End Sub

Create a form and add a Listbox to it and paste the code to it.

[mikerickson]

If you want an InputBox.

Dim uiSheet as Worksheet
On Error Resume Next
Set uiSheet = Application.InputBox("Select a cell on the key sheet.", type:=8).Parent
On Error Goto 0
If uiSheet Is Nothing Then
  MsgBox "canceled"
  Else
  MsgBox "You selected sheet " & uiSheet.Name
End If