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)


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


"Bob Flanagan" wrote:

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

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