If Sheet Exist

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I'm trying to write a macro that performs a check on whether a Sheet Exists
or not.

If the Sheet named "Data" Exists the user should be prompted for accepting
the Sheet to be deleted.
If not the Sheet should be added to the Excel Workbook. The last part is
easy but checking if the Sheet exists is troublesome. I have tried the
Object.Exists(Key) but recieves an error.

I could probably use the Worksheets("Data").Delete command to simply delete
the Sheet "Data" and then continue adding the Sheet.

But then I would need to drop the Add.Sheet procedure if the user do not
accept that the Sheet gets deleted.

Could anyone help me out here
 
Peter said:
Hi,

I'm trying to write a macro that performs a check on whether a Sheet Exists
or not.
You could do a foreach over the worksheets collection checking the name, and
setting a flag if you find it, then check the flag.

Function WSexists(n As String) As Boolean
Dim f As Worksheet
Dim flag As Boolean
flag = False
For Each f In ActiveWorkbook.Worksheets
If LCase(n) = LCase(f.Name) Then
flag = True
Exit For
End If
Next f
WSexists = flag
End Function
 
Try something like this:
Dim ws as Worksheet
On Error Resume Next
Set ws = Sheets("Data")
If ws Is Nothing Then
'Data Does Not Exist
End If
On Error Goto 0

Charles
 
i think this is a similar way

Sub wsexist()
On Error Resume Next
Worksheets("data").Activate
If Worksheets("data") Is Nothing Then
MsgBox "sheet does not exist"
Else
MsgBox "sheet exists"
End If

End Sub
 
Yet another way


'-----------------------------------------------------------------
Function SheetExists(Sh As String, _
Optional wb As Workbook) As Boolean
'-----------------------------------------------------------------
Dim oWs As Worksheet
If wb Is Nothing Then Set wb = ActiveWorkbook
On Error Resume Next
SheetExists = CBool(Not wb.Worksheets(Sh) Is Nothing)
On Error GoTo 0
End Function


--
HTH

Bob Phillips

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top