Modify a toggle to incorporate an if statement..

[sdoppke]

Hi everyone, i have a toggle to hide/unhide sheets, but sometimes those sheets are not in the worksheet and the user will not know it. Would anyone be able to help me mod this script to incorporate an if statement the will throw up a message box alerting the user?

Thanks a ton in advance heres the code:

Private Sub ToggleButton1_Click()
     'Toggles the To Do List sheet as hidden and unhidden
     Dim ws As Worksheet
    Application.ScreenUpdating = False
     
    For Each ws In Worksheets(Array("Week 1", "Week 2", "Week 3", "Week 4", "Week 5"))
        ws.Visible = Not ws.Visible
    Next ws
    Application.ScreenUpdating = True
End Sub

 

[dk]

Hi

Here's one possible way, which uses a function to determine whether the sheet exists or not.

HTH
DK

Private Sub ToggleButton1_Click()
'Toggles the To Do List sheet as hidden and unhidden
    Dim vSheetname As Variant
    Application.ScreenUpdating = False

    For Each vSheetname In Array("Week 1", "Week 2", "Week 3", "Week 4", "Week 5")

        If DoesSheetExist(CStr(vSheetname), ActiveWorkbook) Then
            ActiveWorkbook.Worksheets(vSheetname).Visible = Not ActiveWorkbook.Worksheets(vSheetname).Visible
        Else
            MsgBox "Worksheet " & vSheetname & " does not exist.", vbExclamation, "Error"
        End If

    Next vSheetname
    Application.ScreenUpdating = True
End Sub


Private Function DoesSheetExist(SheetName As String, wb As Workbook) As Boolean

On Error Resume Next

DoesSheetExist = CBool(Len(wb.Worksheets(SheetName).Name))

End Function

 

[sdoppke]

Hi

Here's one possible way, which uses a function to determine whether the sheet exists or not.

HTH
DK

Private Sub ToggleButton1_Click()
'Toggles the To Do List sheet as hidden and unhidden
    Dim vSheetname As Variant
    Application.ScreenUpdating = False
 
    For Each vSheetname In Array("Week 1", "Week 2", "Week 3", "Week 4", "Week 5")
 
        If DoesSheetExist(CStr(vSheetname), ActiveWorkbook) Then
            ActiveWorkbook.Worksheets(vSheetname).Visible = Not ActiveWorkbook.Worksheets(vSheetname).Visible
        Else
            MsgBox "Worksheet " & vSheetname & " does not exist.", vbExclamation, "Error"
        End If
 
    Next vSheetname
    Application.ScreenUpdating = True
End Sub
 
 
Private Function DoesSheetExist(SheetName As String, wb As Workbook) As Boolean
 
On Error Resume Next
 
DoesSheetExist = CBool(Len(wb.Worksheets(SheetName).Name))
 
End Function

Tried it! Its great! You Rock!