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

It works fine on my machine...
Make sure you are using the line
Dim x

....and that Worbook is a typo (should be spelt Workbook)

Rgds
J



"matelot" wrote in message
...
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

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

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

Is this a just typo?
Set x = ActiveWorbook.Sheets("mydata") should be
Set x = ActiveWorkbook.Sheets("mydata")

keizi

"matelot" wrote in message
...
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