Guys,
Thanks so much. It's my typo.

M
"Ed Ferrero" wrote:

Hi matelot,

Line 2 - you spell ActiveWorkbook incorrectly.

This works - do you really need two MsgBoxes?;

Sub tst()
Dim x As Worksheet

On Error Resume Next
Set x = ActiveWorkbook.Sheets("mydata")
If Err = 0 Then
MsgBox "Passed "
Else
MsgBox ActiveWorkbook.Name & " - " & Error(Err) _
&"Sheet does not exist"
End If
On Error Goto 0
End Sub

I like this better;

Sub SheetExists()
Dim sht As Worksheet
For Each sht In ActiveWorkbook.Sheets
If sht.Name = "mydata" Then
MsgBox "Passed "
Exit Sub
End If
Next sht
MsgBox "Sheet does not exist"
End Sub

In general;

- it helps if you declare all variables
- use the debug tool to pick up on spelling errors
- try not to use code that relies on generating errors
(it can be elegant, as in your example, but you forego the ability
to use error trapping for its intended purpose)

Ed Ferrero
http://www.edferrero.com


Hi,
I don't know why I can't pass thru this code. I know it's fairly straight
forward but everytime I run it's always giving the error "Object required"
even if my active workbook contains the sheetname "mydata". Can you tell
me
what's wrong with this code. All I am trying to do is to check if a
sheetname
exists.

On Error Resume Next
Set x = ActiveWorbook.Sheets("mydata")
If Err = 0 Then
MsgBox "Passed "
Else
MsgBox ActiveWorkbook.Name & " - " & Error(Err)
MsgBox "Sheet does not exist"
End If

Thanks