View Full Version : VBA: Selecting Sheet via Input Box or other way

09-09-2011, 06:49 AM
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!

09-09-2011, 07:40 AM
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

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.

09-09-2011, 07:49 AM
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"
MsgBox "You selected sheet " & uiSheet.Name
End If