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" wrote:
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
--
Steve S

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

Peter wrote:
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

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

--


Gary


"Peter" wrote in message
...
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

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)

"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
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

--


Gary


"Peter" wrote in message
...
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