How to check the sheet is still valid and exist ?

[sthanawa]

Hi All ,

I have one question , in my the excel workbook. They have many sheets inside there. How can I check ? Is the sheet name "Work10" still valid and exist in this workbook ? by VBA code.

 

[patrickmuldoon99]

function sheetexists(sheetname as string) as boolean
dim s as object
for each s in sheets
if s.name = sheetname then
sheetexists = true
end if
next s
end function

Untested, but if you use this function, and pass it the sheet name you are looking for, this will return true if it exists, and false if it does not.

HTH

Patrick

 

[Richard Schollar]

Hi Sthanawa

Check out this page on John Walkenbach's site:

http://j-walk.com/ss/excel/tips/tip54.htm

Best regards

Richard

 

[erik.van.geit]

Hi,

another function

Sub yoursub()

Dim ShName As String

'other code
ShName = "Work10"
    If WksExists(ShName) Then
    'do your operations when it exists
    MsgBox "Worksheet exists", 48, "Title"
    Else
    'do your operations when it doesn't exists
    MsgBox "nope"
    End If
'code
End Sub

Function WksExists(wksName As Variant) As Boolean
    On Error Resume Next
    WksExists = CBool(Len(Worksheets(wksName).Name) > 0)
End Function

kind regards,
Erik