Error 424 when trying to determine whether Sheet exists..

[Bob Flanagan]

What is the code for the function CBool? It sounds like a custom function.
I suspect that its argument is a text string that represents the name of a
worksheet.

Bob Flanagan
Macro Systems
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

 

[Guest]

Hi people, using various posts in this board, have created the following
code, but it's returning a 424 Object Required error on the line..
SheetExists = CBool(Workbook.Sheets(SheetName) Is Nothing)

Can you see what I'm doing wrong?

Many thanks for any suggestions,
Geoff.


Sub CopyTemplate()
Dim S_No As String
Dim Message, Title, Default, SheetName As String
Dim SheetExists As Boolean

Message = "Enter Subject Number"
Title = "InputBox"
Default = "0000"

'Get subject number
S_No = InputBox(Message, Title, Default)
'error conditions
If S_No = "" Then GoTo nosubentered
If S_No = "0000" Then GoTo nosubentered
'Create sheetname
SheetName = "Sub" & S_No
On Error Resume Next
SheetExists = CBool(Workbook.Sheets(SheetName) Is Nothing)
On Error GoTo 0
If SheetExists Then GoTo subexistsalready
'Copy template sheet into new sheet name
........(code omitted)......


subexistsalready:
Sheets(SheetName).Select

nosubentered:

End Sub

 

[Dave Peterson]

I didn't see where Workbook was dimmed or set.

But I think you missed a Not() in your logic.

SheetExists = False
On Error Resume Next
SheetExists = Not (CBool(ThisWorkbook.Sheets(SheetName) Is Nothing))
On Error GoTo 0

I added the inital "sheetexists=false" just in case you use that code in a
loop. Once that variable is changed to true, then any missing worksheet would
cause the "on error resume next" line to not change the True to false.

 

[Dave Peterson]

I often wish that I could retrieve some of my posts, too. <vbg>

 

[Guest]

It's a built-in type conversion function. It's not my code by the way, just
one of the many variants of "worksheet exists". I also tried;

If Workbook.Sheets(SheetName) Is Nothing Then SheetExists = False

and this gives the same 424 error.

 

[Chip Pearson]

There are several problems with your code. I would use the following
function procedure:

Function SheetExists(SheetName As String, _
Optional WhatWorkbook As Workbook) As Boolean
Dim WB As Workbook
If WhatWorkbook Is Nothing Then
Set WB = ThisWorkbook
Else
Set WB = WhatWorkbook
End If
On Error Resume Next
SheetExists = CBool(Len(WB.Worksheets(SheetName).Name))
End Function

Then, you can call this with code like:

If SheetExists("Sheet123",ThisWorkbook) = True Then
' sheet does exist
Else
' sheet does not exist
End If


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Chip Pearson]

You have not defined what 'Workbook' is. Don't declare a variable with the
name "Workbook" since that is also the name of a built-in Excel object. Use
"WB" or "MyWorkbook" or something like that.

The code

If Workbook.Sheets(SheetName) Is Nothing Then SheetExists = False

should be any one of the following:

If ThisWorkbook.Sheets(SheetName) Is Nothing Then SheetExists = False
' tests the workbook that contains the code, regardless of what
' workbook happens to be active

If ActiveWorkbook.Sheets(SheetName) Is Nothing Then SheetExists = False
' tests the active workbook, even if that is not the workbook that
' contains the code

If Workbooks("Book1.xls").Sheets(SheetName) Is Nothing Then SheetExists =
False
' tests Book1.xls, regardless of what workbook contains the code and
regardless
' of what workbook happens to be active.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Guest]

Oops, I've realised that the problem was in my Options, it was breaking on
all errors, not just Unhandled errors, nothing really to do with the code at
all.

Thanks for your suggestions, and apologies.
Geoff

 

[Dave Peterson]

I still think you had a logic error in your code.