How to check the existance of a worksheet? 3

[alkfhk]

Is there anybody knows how to write a VBA script to check whether a specific worksheet exists in a workbook? For example, how to check whether, say "Sheet 10" exists in the workbook or? 'cause everytime I try to use select to check, Excel comes up with error and the macro halt!

Thanks!

 

[SQLBI]

Hi,

The following is a little crude, but it shows how to loop through the sheets in a workbook and take action depending on the result.

Sub WSCheck()

Dim ws As Worksheet
Dim intCount As Integer


    For Each ws In ActiveWorkbook.Sheets
    
        If ws.Name = "Sheet10" Then intCount = intCount + 1
    
    Next ws
    
        If intCount > 0 Then
    
            MsgBox "Sheet10 is present in this workbook."
        
        Else
        
            MsgBox "Sheet10 is not present in this workbook."
        
        End If

End Sub

Hope this helps.



Leigh Moore
Solutions 4 MS Office Ltd

http://www.solutions4msoffice.co.uk

 

[xlbo]

a different way of approaching the problem....

Sub test()
Dim shNm As String
On Error GoTo notFound
    shNm = Sheets("Sheet10").Name
    MsgBox "Sheet10 exists"
    Exit Sub
notFound:
    MsgBox "Sheet10 does not exist"
End Sub

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[RobBroekhuis]

Just another variation (since I dislike jumps of all kinds, I try to avoid them even for error handling):

Function SheetExists(s As String) As Boolean
   Dim i As Integer
   On Error Resume Next
   i = Sheets(s).Index
   SheetExists = Err = 0
   Err.Clear
End Function

Rob