PDA

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



jlkamikaze
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!

JKwan
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
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
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"
Else
MsgBox "You selected sheet " & uiSheet.Name
End If