Wrong Name

[rucker222]

Hi guys,

I'm having trouble with some VBA I've put together. It basically just hides and Unhides worksheets which all works perfectly. The problem I'm having is with the message box.

"el" is always returning the next sheet in my mylist not the not the actual sheet which is not in my list...

For example if i change the name of sheet "B6 FFE" to "B FFE" the error message will say "Sheet B9 FFE Not Found!!!..." when i want it to say "Sheet B6 FFE Not Found!!!..."
Bad explanation but hopefully you will understand what I mean when you look at the below...

Private Sub Worksheet_Activate()
Application.ScreenUpdating = False

mylist = Array("Cover Sheet", "Summary", "FFE Equipment", "Fire & Smoke Doors", "B6 FFE", "B9 FFE", "B10 FFE", "B11 FFE", _
    "B12 FFE", "B13 FFE", "B14 FFE", "B16 FFE")
    
Dim ws As Worksheet
For Each ws In Worksheets
If ws.Name <> ActiveSheet.Name Then
ws.Visible = xlSheetVeryHidden
End If
Next ws


For Each el In mylist
If Err Then
      MSG1 = MsgBox("Sheet " & el & " Not Found!!" & vbNewLine & _
      el & " may have been deleted or renamed" & vbNewLine & _
      "would you like to unhide all worksheets?", vbYesNo + vbExclamation, "Sheet Not Found!")
      If MSG1 = vbYes Then
      Application.Run ("UnhideAllSheets")
      Exit Sub
    Else
    On Error Resume Next
      'MsgBox "E-mail successfully sent", vbInformation
    End If
    End If
    On Error Resume Next

    If Sheets(el).Visible = xlSheetVeryHidden Then
    Sheets(el).Visible = xlSheetVisible
    End If
    Next el
Application.ScreenUpdating = True
End Sub

 

[dmt32]

See if changes to your code helps:

Private Sub Worksheet_Activate()
    Dim el As Integer
    Dim ws As Worksheet
    
    mylist = Array("Cover Sheet", "Summary", "FFE Equipment", "Fire & Smoke Doors", "B6 FFE", "B9 FFE", "B10 FFE", "B11 FFE", _
                   "B12 FFE", "B13 FFE", "B14 FFE", "B16 FFE")


    
    Application.ScreenUpdating = False


    For Each ws In Worksheets
        If ws.Name <> ActiveSheet.Name Then
             ws.Visible = xlSheetVeryHidden
        End If
    Next ws


    On Error Resume Next
    For el = LBound(mylist) To UBound(mylist)
        Set ws = Sheets(mylist(el))
        If Err.Number = 9 Then
            MSG1 = MsgBox("Sheet " & mylist(el) & " Not Found!!" & vbNewLine & _
                          mylist(el) & " may have been deleted or renamed" & vbNewLine & _
                          "would you like to unhide all worksheets?", vbYesNo + vbExclamation, "Sheet Not Found!")
            If MSG1 = vbYes Then
                  Application.Run ("UnhideAllSheets")
                GoTo exitsub
            Else
                Sheets(mylist(el)).Visible = xlSheetVisible
                'MsgBox "E-mail successfully sent", vbInformation


            End If
        End If
        Set ws = Nothing
    Next el


exitsub:
    Application.ScreenUpdating = True
End Sub

Dave

 

[rucker222]

Hi Dave,

Thanks for the response. I gave that a go this morning and now it is correctly returning the error message box for the sheet that is missing/renamed. However it's also then returning the error message box for every subsequent sheet even though they are not missing/renamed.

Also all of the sheets in 'mylist' proceeding the missing/renamed sheet are not being made visible.

Any ideas?

Thanks again,

 

[dmt32]

see if this change helps:

Private Sub Worksheet_Activate()
    Dim el As Integer
    Dim ws As Worksheet
    mylist = Array("Cover Sheet", "Summary", "FFE Equipment", "Fire & Smoke Doors", "B6 FFE", "B9 FFE", "B10 FFE", "B11 FFE", _
                   "B12 FFE", "B13 FFE", "B14 FFE", "B16 FFE")

    Application.ScreenUpdating = False
    For Each ws In Worksheets
        If ws.Name <> ActiveSheet.Name Then
             ws.Visible = xlSheetVeryHidden
        End If
    Next ws
    On Error Resume Next
    For el = LBound(mylist) To UBound(mylist)
        Set ws = Sheets(mylist(el))
        If Err > 0 Then
            If Err.Number = 9 Then
                MSG1 = MsgBox("Sheet " & mylist(el) & " Not Found!!" & vbNewLine & _
                              mylist(el) & " may have been deleted or renamed" & vbNewLine & _
                              "would you like to unhide all worksheets?", vbYesNo + vbExclamation, "Sheet Not Found!")
                If MSG1 = vbYes Then
                     Application.Run ("UnhideAllSheets")
                    GoTo exitsub
                End If
                Err.Clear
            End If
        Else
            Sheets(mylist(el)).Visible = xlSheetVisible
            'MsgBox "E-mail successfully sent", vbInformation
        End If
        Set ws = Nothing
    Next el
exitsub:
    Application.ScreenUpdating = True
End Sub

Dave

 

[rucker222]

Dave,

You are a legend, that works perfectly!

Thank you very much.
Lee.

 

[dmt32]

Dave,

You are a legend, that works perfectly!

Thank you very much.
Lee.

would not go that far but thank-you for your kind feedback

Dave