How to "trick" VBA?

[davegb]

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.

 

[Ardus Petus]

For Each ws In wbExtrFrom.Worksheets
If ws.Name = "Top" Then
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

"davegb" <[email protected]> a écrit dans le message de (e-mail address removed)...

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.

 

[Ardus Petus]

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" <[email protected]> a écrit dans le message de (e-mail address removed)...

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.

 

[Tom Ogilvy]

Dim sh as Object
for each sh in wbExtrFrom.Sheets
if sh.Name = "Top" Then

--
Regards,
Tom Ogilvy


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]

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

 

[Tom Ogilvy]

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

 

[davegb]

Dim sh as Object
for each sh in wbExtrFrom.Sheets
if sh.Name = "Top" Then

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.