If sheet exists, then...

[Darin Kramer]

Hi there,

Im looking for some simple VB that says if sheet xyz exist, then select
sheet and delete it, or else call Macro 1.

Any ideas...

Thanks!!!!

Darin

*** Sent via Developersdex http://www.developersdex.com ***

 

[Jim Thomlinson]

if sheetexits("xyz") then
application.displayalerts = false
sheets("xyz").delete
application.displayalerts = true
else
call Macro1
end if

Public Function SheetExists(SName As String, _
Optional ByVal Wb As Workbook) As Boolean
'Chip Pearson
On Error Resume Next
If Wb Is Nothing Then Set Wb = ThisWorkbook
SheetExists = CBool(Len(Wb.Sheets(SName).Name))
End Function

 

[Darin Kramer]

Hi Jim,

It doesnt like the first sheetexists command....?

Regards

D


*** Sent via Developersdex http://www.developersdex.com ***

 

[Bob Phillips]

typo

if sheetexists("xyz") then
application.displayalerts = false
sheets("xyz").delete
application.displayalerts = true
else
call Macro1
end if


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Jim Thomlinson]

Sorry... There was a typo sheetexits to sheetexists

Sub Test()
if sheetexists("xyz") then
application.displayalerts = false
sheets("xyz").delete
application.displayalerts = true
else
call Macro1
end if
end sub

Public Function SheetExists(SName As String, _
Optional ByVal Wb As Workbook) As Boolean
'Chip Pearson
On Error Resume Next
If Wb Is Nothing Then Set Wb = ThisWorkbook
SheetExists = CBool(Len(Wb.Sheets(SName).Name))
End Function

 

[Dave Peterson]

A spelling error.

Sheetexits should be sheetexists.

 

[Darin Kramer]

Okay typo fixed. But now for some reason Excel is thinking that the
sheet doesnt exist and is going to the "Else", but I can see the sheet
exists... any ideas...?



*** Sent via Developersdex http://www.developersdex.com ***

 

[Bob Phillips]

It takes the correct path in both situations for me.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Andrew Taylor]

There's no need to select the sheet before deleting it
(and doing so "forgets" your current selection).

On Error Resume Next
Application.DisplayAlerts = False
ActiveWorkbook.Sheets("xyz").Delete
Application.DisplayAlerts = True

 

[Dave Peterson]

I'd guess a slight change in the spelling of the name.

I'd look for leading/trailing/embedded spaces.