worksheet names

S

silverfox

i am writing a program that has numerous workshhets that are hidden. I wnat
the user to enter a worksheet name and the program to make that sheet asked
for visible.

ie
Dim Message, Title, Default, MyValue
Message = "Enter a sheet name" ' Set prompt.
Title = "" ' Set title.
Default = "" ' Set default.
' Display message, title, and default value.
MyValue = InputBox(Message, Title, Default)
Worksheets(MyValue).Visible = True

any ideas
 
J

JE McGimpsey

One way:

Const nSTRING_TYPE As Long = 2
Const sTITLE As String = ""
Const sDEFAULT As String = ""
Dim vResult As Variant
Dim wsSheet As Worksheet
Dim sMessage As String

sMessage = "Enter a sheet name:"
Do
vResult = Application.InputBox( _
Prompt:=sMessage, _
Title:=sTITLE, _
Default:=sDEFAULT, _
Type:=nSTRING_TYPE)
If vResult = False Then Exit Sub ' User cancelled
On Error Resume Next
Set wsSheet = Sheets(vResult)
On Error GoTo 0
sMessage = "Sheet """ & vResult & """ does not exist." & _
vbNewLine & "Enter a valid sheet name:"
Loop Until Not wsSheet Is Nothing
wsSheet.Visible = True
 
Top