I want to create a macro that creates a pop up box that ask the name of
the worksheet i want to open. What statement do i use?
please help :

Ella,

It is "workbooks" that are opened, not worksheets.
Use "GetOpenFileName"...
'-------------------------
Sub MakeItHappen()
Dim FileToOpen As Variant
FileToOpen = Application.GetOpenFilename()
'do stuff
End Sub
'----------------------------

Jim Cone
San Francisco, USA


"Ella"
wrote in message
oups.com
I want to create a macro that creates a pop up box that ask the name of
the worksheet i want to open. What statement do i use?
please help :

Dim ans as String
Dim sh as Worksheet
ans = InputBox("What is the sheet name?")
if ans = "" then
' user hit cancel
exit sub
end if
On Error Resume Next
set sh = worksheets(ans)
On Error goto 0
if sh is nothing then
msgbox ans & " does not exist"
else
sh.Activate
End if

--
Regards,
Tom Ogilvy

"Ella" wrote in message
oups.com...
I want to create a macro that creates a pop up box that ask the name of
the worksheet i want to open. What statement do i use?
please help :

Try this one:


-------------------------------------------------------
Sub Select_Worksheet()

Dim SheetName As String
Dim I As Integer

SheetName = InputBox("Write the name of the Sheet to open")
For I = 1 To Worksheets.Count
If Worksheets(I).Name = SheetName The
Worksheets(SheetName).Activate
Next I

End Sub
-------------------------------------------------------

Please, note that the sheet name is case sensitive in this cod

--
baldomer
-----------------------------------------------------------------------
baldomero's Profile: http://www.excelforum.com/member.php...fo&userid=2568
View this thread: http://www.excelforum.com/showthread.php?threadid=44759

Hi Ella,
I like to supply a default when using InputBox, so that you don't have
to necessarily type in anything, in this case a sheetname.

Sub specifysheetname()
Dim response As String
response = Application.Worksheets(1).Name
response = Application.InputBox( _
"Enter the sheetname", _
"Enter sheetname:", response, , , , 1)
If response < "False" Then
On Error Resume Next
Application.Worksheets(response).Activate
If Err.Number = 9 Then MsgBox "no such sheet"
On Error GoTo 0
End If
End Sub

Are you aware that you right click a sheet navigation arrow left
of the sheetnames and select a sheet or "more sheets". Or
a dialog to directly bring up more sheets.
http://www.mvps.org/dmcritchie/excel...htm#moresheets

Sooner or later if you have a lot of sheets you will want to sort the
worksheet tabs.
http://www.mvps.org/dmcritchie/excel...#sortallsheets

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Ella" wrote in message oups.com...
I want to create a macro that creates a pop up box that ask the name of
the worksheet i want to open. What statement do i use?
please help :