How to "trick" VBA?

  • Thread starter Thread starter davegb
  • Start date Start date
D

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.
 
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.
 
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.
 
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.
 
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
 
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
 
Tom said:
Dim sh as Object
for each sh in wbExtrFrom.Sheets
if sh.Name = "Top" Then
Click to expand...

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.
 
Back
Top