Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error 424 when trying to determine whether Sheet exists..
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error 424 when trying to determine whether Sheet exists..
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error 424 when trying to determine whether Sheet exists..
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. Geoff C wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error 424 when trying to determine whether Sheet exists..
I often wish that I could retrieve some of my posts, too. <vbg
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 -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error 424 when trying to determine whether Sheet exists..
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error 424 when trying to determine whether Sheet exists..
There are several problems with your code. I would use the following
function procedu 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) "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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error 424 when trying to determine whether Sheet exists..
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error 424 when trying to determine whether Sheet exists..
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 "Geoff C" wrote: 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error 424 when trying to determine whether Sheet exists..
I still think you had a logic error in your code.
Geoff C wrote: 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 "Geoff C" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA:: determine if UDF exists? | Excel Discussion (Misc queries) | |||
Determine if a File Exists | Excel Discussion (Misc queries) | |||
Determine if a File Exists | Excel Programming | |||
determine if value exists | Excel Programming | |||
Determine if folder exists | Excel Programming |