|
How to "trick" VBA?
The code I'm writing requires me to check to see if a worksheet named "Top" already exists before the macro creates one. My test for a sheet named Top isn't working because if I dim ws as a worksheet or worksheets or an object, I get an "Object doesn't support that property or method" error on the marked line. If I dim ws as sheets, I get a compile error on the .name, "Method or data member not found" since Sheets doesn't have a name property. Catch 22? wbExtrFrom is declared as a workbook. For Each ws In wbExtrFrom ß Object doesn't support this property or method If ws.Name = "Top" Then ß Method or data member not found If MsgBox("A worksheet named Top already exists in this workbook." _ & "Please remove or rename it and run the macro again.", _ vbOKOnly) = vbOK Then Exit Sub End If Next What is the trick to workaround this limitation in VBA? Thanks as always. |
How to "trick" VBA?
Thought it over:
Dim ws as Worksheet Set ws = wbExtrFrom.Worksheets("Top") if not ws is nothing then msgbox "Worksheet already exists" end if Cheers, -- AP "davegb" a écrit dans le message de news: ... The code I'm writing requires me to check to see if a worksheet named "Top" already exists before the macro creates one. My test for a sheet named Top isn't working because if I dim ws as a worksheet or worksheets or an object, I get an "Object doesn't support that property or method" error on the marked line. If I dim ws as sheets, I get a compile error on the .name, "Method or data member not found" since Sheets doesn't have a name property. Catch 22? wbExtrFrom is declared as a workbook. For Each ws In wbExtrFrom ß Object doesn't support this property or method If ws.Name = "Top" Then ß Method or data member not found If MsgBox("A worksheet named Top already exists in this workbook." _ & "Please remove or rename it and run the macro again.", _ vbOKOnly) = vbOK Then Exit Sub End If Next What is the trick to workaround this limitation in VBA? Thanks as always. |
How to "trick" VBA?
Dim sh as Object
for each sh in wbExtrFrom.Sheets if sh.Name = "Top" Then -- Regards, Tom Ogilvy "davegb" wrote in message ups.com... The code I'm writing requires me to check to see if a worksheet named "Top" already exists before the macro creates one. My test for a sheet named Top isn't working because if I dim ws as a worksheet or worksheets or an object, I get an "Object doesn't support that property or method" error on the marked line. If I dim ws as sheets, I get a compile error on the .name, "Method or data member not found" since Sheets doesn't have a name property. Catch 22? wbExtrFrom is declared as a workbook. For Each ws In wbExtrFrom ß Object doesn't support this property or method If ws.Name = "Top" Then ß Method or data member not found If MsgBox("A worksheet named Top already exists in this workbook." _ & "Please remove or rename it and run the macro again.", _ vbOKOnly) = vbOK Then Exit Sub End If Next What is the trick to workaround this limitation in VBA? Thanks as always. |
How to "trick" VBA?
for each ws in wbExtrFrom.worksheets
Should work ok. But you could use another technique, too: dim ws as worksheet set ws = nothing on error resume next set ws = wbextrfrom.worksheets("Top") on error goto 0 if ws is nothing then 'it doesn't exist else 'it does exist end if And the second version doesn't care about upper/lower case for the worksheet name. But if you really want the user to delete it, maybe just deleting it in code would be sufficient. application.displayalerts = false on error resume next wbextrfrom.worksheets("top").delete on error goto 0 application.displayalerts = true davegb wrote: The code I'm writing requires me to check to see if a worksheet named "Top" already exists before the macro creates one. My test for a sheet named Top isn't working because if I dim ws as a worksheet or worksheets or an object, I get an "Object doesn't support that property or method" error on the marked line. If I dim ws as sheets, I get a compile error on the .name, "Method or data member not found" since Sheets doesn't have a name property. Catch 22? wbExtrFrom is declared as a workbook. For Each ws In wbExtrFrom ß Object doesn't support this property or method If ws.Name = "Top" Then ß Method or data member not found If MsgBox("A worksheet named Top already exists in this workbook." _ & "Please remove or rename it and run the macro again.", _ vbOKOnly) = vbOK Then Exit Sub End If Next What is the trick to workaround this limitation in VBA? Thanks as always. -- Dave Peterson |
How to "trick" VBA?
that won't work as written - it would raise an error it Top doesn't exist,
so you have to handle the error. Dim ws as Worksheet On Error Resume Next Set ws = wbExtrFrom.Worksheets("Top") On Error goto 0 if not ws is nothing then msgbox "Worksheet already exists" end if -- Regards, Tom Ogilvy "Ardus Petus" wrote in message ... Thought it over: Dim ws as Worksheet Set ws = wbExtrFrom.Worksheets("Top") if not ws is nothing then msgbox "Worksheet already exists" end if Cheers, -- AP "davegb" a écrit dans le message de news: ... The code I'm writing requires me to check to see if a worksheet named "Top" already exists before the macro creates one. My test for a sheet named Top isn't working because if I dim ws as a worksheet or worksheets or an object, I get an "Object doesn't support that property or method" error on the marked line. If I dim ws as sheets, I get a compile error on the .name, "Method or data member not found" since Sheets doesn't have a name property. Catch 22? wbExtrFrom is declared as a workbook. For Each ws In wbExtrFrom ß Object doesn't support this property or method If ws.Name = "Top" Then ß Method or data member not found If MsgBox("A worksheet named Top already exists in this workbook." _ & "Please remove or rename it and run the macro again.", _ vbOKOnly) = vbOK Then Exit Sub End If Next What is the trick to workaround this limitation in VBA? Thanks as always. |
How to "trick" VBA?
Tom Ogilvy wrote: Dim sh as Object for each sh in wbExtrFrom.Sheets if sh.Name = "Top" Then -- Regards, Tom Ogilvy Thanks to everyone who replied. I used this one from Tom because it was closest to what I had, even though I don't understand why it works. Looking over the others was very informative. "davegb" wrote in message ups.com... The code I'm writing requires me to check to see if a worksheet named "Top" already exists before the macro creates one. My test for a sheet named Top isn't working because if I dim ws as a worksheet or worksheets or an object, I get an "Object doesn't support that property or method" error on the marked line. If I dim ws as sheets, I get a compile error on the .name, "Method or data member not found" since Sheets doesn't have a name property. Catch 22? wbExtrFrom is declared as a workbook. For Each ws In wbExtrFrom ß Object doesn't support this property or method If ws.Name = "Top" Then ß Method or data member not found If MsgBox("A worksheet named Top already exists in this workbook." _ & "Please remove or rename it and run the macro again.", _ vbOKOnly) = vbOK Then Exit Sub End If Next What is the trick to workaround this limitation in VBA? Thanks as always. |
| All times are GMT +1. The time now is 12:02 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com