How can I know if a sheet exists ?
 

Hi,
My first question is exactly the title : How can I know if a sheet exists ?
And My second question is: how can I delete a sheet without having the message from excel : "are you sure you want to delete...blablabla..... click OK to delete it".
Thanks a lot for âny help and have a hppy new year.
Benjamin

How can I know if a sheet exists ?
 

Hi Ben

Try this, copy the function and sub in a normal module

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

Sub Sheet_Test()
Dim SName As String
If SheetExists("test") = False Then
MsgBox "don't exist"
Else
MsgBox "sheet exist"
End If
End Sub


For deleting the sheet you can use this

Application.DisplayAlerts = False
ActiveSheet.Delete
Application.DisplayAlerts = True




--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Ben.C" wrote in message ...
Hi,
My first question is exactly the title : How can I know if a sheet exists ?
And My second question is: how can I delete a sheet without having the message from excel : "are you sure you want to
delete...blablabla..... click OK to delete it".
Thanks a lot for ny help and have a hppy new year.
Benjamin

How can I know if a sheet exists ?
 

You can delete this line Ben

Dim SName As String

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Ron de Bruin" wrote in message ...
Hi Ben

Try this, copy the function and sub in a normal module

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

Sub Sheet_Test()
Dim SName As String
If SheetExists("test") = False Then
MsgBox "don't exist"
Else
MsgBox "sheet exist"
End If
End Sub


For deleting the sheet you can use this

Application.DisplayAlerts = False
ActiveSheet.Delete
Application.DisplayAlerts = True




--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Ben.C" wrote in message ...
Hi,
My first question is exactly the title : How can I know if a sheet exists ?
And My second question is: how can I delete a sheet without having the message from excel : "are you sure you want to
delete...blablabla..... click OK to delete it".
Thanks a lot for ny help and have a hppy new year.
Benjamin

How can I know if a sheet exists ?
 

Thanks a lot Ron !!!!!