check if sheet exists
how would you go about checking to see if a sheet exists
of not? |
check if sheet exists
One way
(al code 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 Use it like this for example Sub Sheet_Test() Dim SName As String If SheetExists("test") = False Then ActiveSheet.Name = "test" Else MsgBox "sorry the sheet exist" End If End Sub -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Ross" wrote in message ... how would you go about checking to see if a sheet exists of not? |
check if sheet exists
Thanks guys, I really appreciate the help. I was
wondering if you could provide me qith an example of passing a workbook through the function as well. Thanks again. -----Original Message----- Ross, Try something like Function SheetExists(SheetName As String, _ Optional Book As Workbook) As Boolean Dim WB As Workbook Dim N As Long On Error Resume Next If Book Is Nothing Then Set WB = ThisWorkbook End If N = Len(WB.Worksheets(SheetName).Name) SheetExists = (N < 0) End Function You can then call this with code like If SheetExists("Sheet123") = True Then -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Ross" wrote in message ... how would you go about checking to see if a sheet exists of not? . |
All times are GMT +1. The time now is 02:46 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com