Worksheet Search

[GreenyMcDuff]

Hey guys,

I have a Workbook with 5 worksheets. Lets call them "Sheet 1", "Sheet 2", "Sheet 3", "Sheet 4" and "Sheet 5"

I need my macro to perform an initial check and set certain variables to False if those sheets are not there.

This is what I have so Far:

Option Explicit
Public ws_F As Boolean, ws_FI As Boolean, ws_NFI As Boolean, ws_O As Boolean, ws_X As Boolean


Sub sheet_format()

    If Sheets("Sheet 1").Value = True Then ws_FI = True Else ws_FI = False
    If Sheets("Sheet 2").Value = True Then ws_NFI = True Else ws_NFI = False
    If Sheets("Sheet 3").Value = True Then ws_F = True Else ws_F = False
    If Sheets("Sheet 4").Value = True Then ws_O = True Else ws_O = False
    If Sheets("Sheet 5").Value = True Then ws_X = True Else ws_X = False
End Sub

Unsurprisingly I get an unsuported property or method error.

I would grateful for your input as to how best solve this problem.

Kind Regards

Chris

 

[VoG]

Try like this

Function WorksheetExists(WSName As String) As Boolean
On Error Resume Next
WorksheetExists = Worksheets(WSName).Name = WSName
On Error GoTo 0
End Function

Sub test()
If Not WorksheetExists("Sheet1") Then
        MsgBox "Unable to proceed", vbExclamation, "Error"
        Exit Sub
End If
End Sub

 

[MrKowz]

Try:

Function WorksheetExists(WSName As String) As Boolean
On Error Resume Next
WorksheetExists = Worksheets(WSName).Name = WSName
On Error GoTo 0
End Function
 
Sub SheetFormat()
Dim ws_F    As Boolean, _
    ws_FI   As Boolean, _
    ws_NFI  As Boolean, _
    ws_O    As Boolean, _
    ws_X    As Boolean
    
ws_F = WorksheetExists("Sheet 1")
ws_FI = WorksheetExists("Sheet 2")
ws_NFI = WorksheetExists("Sheet 3")
ws_O = WorksheetExists("Sheet 4")
ws_X = WorksheetExists("Sheet 5")

End Sub

 

[GreenyMcDuff]

Perfect o,

Thanks VoG & MrKowz