Checking for valid worksheet in macro

Posted by Eli Weiss on February 12, 2002 11:56 PM

Hello,
I have a macro which prompts the user to enter a name of the worksheet he wants to update in his workbook.
The code for this is :
WorksheetName = inputBox(Enter name of Worksheet)
Sheets(WorksheetName).Select
Etc.
My problem is how to force the user to enter a correct name of one of the worksheets that is in that workbook.
In other words I want to check the input and stop the macro or alert the user, if his input is wrong.
Any help will be appreciated.
Eli

Posted by Ivan F Moala on February 13, 2002 12:22 AM

Try something like;

On Error Resume Next
TryAgain:
WorksheetName = InputBox("Enter name of Worksheet")
If WorksheetName = "" Then Exit Sub
Sheets(WorksheetName).Select
If Err Then: Err.Clear: GoTo TryAgain

Ivan

Posted by Bruno on February 13, 2002 12:46 AM

use a listbox

Hi Eli,

You could also use a listbox with the names of the sheets. Use some code like this :

Sub SelectSheet()

For Each ws In ThisWorkbook.Sheets
UserForm1.ListBox1.AddItem ws.Name
Next

UserForm1.Show
Sheets(UserForm1.ListBox1.Value).Select

End Sub

Bruno
-----

Posted by Eli Weiss on February 13, 2002 5:39 AM

Re: Error in code

I tried your code but there is an error
No. 424 Object required
Thank you
Eli

Posted by Bruno on February 13, 2002 11:16 PM

Re: Error in code

Hi Eli,
I know i've given just the information to get started...
What need at least is :

a) an userform called userform1
with on this userform :
b) a listbox called listbox1
c) a commandbutton, caption "OK"
d) a commandbutton, caption "Cancel"

dubbleclick on the OK button and enter the following code :

Private Sub cmdOK_Click()
Me.Hide
End Sub

dubbleclick on the OK button and enter the following code :

Private Sub cmdAnnuleren_Click()
End
End Sub

maybe it's easier if I send you my xls file...
just let me know your e-mail adress.
Bruno
-----

Sub SelectSheet()

For Each ws In ThisWorkbook.Sheets
UserForm1.ListBox1.AddItem ws.Name
Next

UserForm1.Show
Sheets(UserForm1.ListBox1.Value).Select

End

Posted by Eli Weiss on February 15, 2002 12:48 AM

Re: my Email- Thanks

Thanks in advance - Eli