[Answered] Error with VBA code from youtube example

This thread is solved
M

Mike H

New member
Greetings. Love the video for creating tabs with in tabs. I set up 12 tabs, one for each month. 24 tabs total counting the on and off. My issue is as soon as I select any month, I get a run time error. The debug shows an error with the first line of which ever tab I selected.

1737066877395.png
Here is the debug.
1737066174013.png

I followed the pattern of the template given in the article, changin the tile names, then copying each segment to reach 12 total. I made sure for which ever tile was selected the statements were True then False, with False then True on all others.

Any thoughts on what I missed? Thanks so much for any assistance! - Mike
 
Last edited:
Hello Mike,

I reviewed the screenshot of your VBA code, and I see where the issue might be originating. The runtime error you are encountering when selecting a month tab and running the associated macro can be caused by several potential problems.

This code includes checks to ensure that the shapes exist, error handling to prevent runtime issues, and unprotects the sheet if needed before making changes.
Code: 
Sub JAN()
    On Error GoTo ErrorHandler
    With Sheet1
        ' Ensure the sheet is unprotected before making changes
        If .ProtectContents Then .Unprotect Password:="YourPassword"

        ' Set visibility for shapes (check if shapes exist)
        If ShapeExists(.Shapes, "JANOn") Then .Shapes("JANOn").Visible = msoCTrue
        If ShapeExists(.Shapes, "JANOff") Then .Shapes("JANOff").Visible = msoCFalse
        If ShapeExists(.Shapes, "FEBOn") Then .Shapes("FEBOn").Visible = msoCFalse
        If ShapeExists(.Shapes, "FEBOff") Then .Shapes("FEBOff").Visible = msoCFalse
        If ShapeExists(.Shapes, "MAROn") Then .Shapes("MAROn").Visible = msoCFalse
        If ShapeExists(.Shapes, "MAROff") Then .Shapes("MAROff").Visible = msoCFalse
        If ShapeExists(.Shapes, "APROn") Then .Shapes("APROn").Visible = msoCFalse
        If ShapeExists(.Shapes, "APROff") Then .Shapes("APROff").Visible = msoCFalse
        If ShapeExists(.Shapes, "MAYOn") Then .Shapes("MAYOn").Visible = msoCFalse
        If ShapeExists(.Shapes, "MAYOff") Then .Shapes("MAYOff").Visible = msoCFalse
        If ShapeExists(.Shapes, "JUNOn") Then .Shapes("JUNOn").Visible = msoCFalse
        If ShapeExists(.Shapes, "JUNOff") Then .Shapes("JUNOff").Visible = msoCFalse
        If ShapeExists(.Shapes, "JULOn") Then .Shapes("JULOn").Visible = msoCFalse
        If ShapeExists(.Shapes, "JULOff") Then .Shapes("JULOff").Visible = msoCFalse
        If ShapeExists(.Shapes, "AUGOn") Then .Shapes("AUGOn").Visible = msoCFalse
        If ShapeExists(.Shapes, "AUGOff") Then .Shapes("AUGOff").Visible = msoCFalse
        If ShapeExists(.Shapes, "SEPOn") Then .Shapes("SEPOn").Visible = msoCFalse
        If ShapeExists(.Shapes, "SEPOff") Then .Shapes("SEPOff").Visible = msoCFalse
        If ShapeExists(.Shapes, "OCTOn") Then .Shapes("OCTOn").Visible = msoCFalse
        If ShapeExists(.Shapes, "OCTOff") Then .Shapes("OCTOff").Visible = msoCFalse
        If ShapeExists(.Shapes, "NOVOn") Then .Shapes("NOVOn").Visible = msoCFalse
        If ShapeExists(.Shapes, "NOVOff") Then .Shapes("NOVOff").Visible = msoCFalse
        If ShapeExists(.Shapes, "DECOn") Then .Shapes("DECOn").Visible = msoCFalse
        If ShapeExists(.Shapes, "DECOff") Then .Shapes("DECOff").Visible = msoCFalse

        ' Set column visibility
        .Range("B:N").EntireColumn.Hidden = False
        .Range("O:FM").EntireColumn.Hidden = True

        ' Re-protect the sheet if it was protected
        .Protect Password:="YourPassword"
    End With
    Exit Sub

ErrorHandler:
    MsgBox "An error occurred: " & Err.Description, vbCritical
    Err.Clear
End Sub

' Helper function to check if a shape exists
Function ShapeExists(Shapes As Shapes, ShapeName As String) As Boolean
    On Error Resume Next
    ShapeExists = Not Shapes(ShapeName) Is Nothing
    On Error GoTo 0
End Function

Key Features of the Updated Code:
Shape Existence Check: Uses the ShapeExists helper function to ensure that a shape exists before attempting to modify its visibility.
Error Handling: Wraps the entire procedure in an On Error GoTo ErrorHandler block to gracefully handle unexpected issues. Displays a message box if an error occurs, providing details about the error.
Sheet Protection Management: Automatically unprotects the sheet at the start of the macro (if protected) and reprotects it at the end.
Visibility Management: Updates the visibility of shapes specific to "JANOn" while hiding others.
Column Visibility: Unhides the columns B:N and hides O:FM.
 
shamimarita said:
Hello Mike,

I reviewed the screenshot of your VBA code, and I see where the issue might be originating. The runtime error you are encountering when selecting a month tab and running the associated macro can be caused by several potential problems.

This code includes checks to ensure that the shapes exist, error handling to prevent runtime issues, and unprotects the sheet if needed before making changes.
Code: 
Sub JAN()
    On Error GoTo ErrorHandler
    With Sheet1
        ' Ensure the sheet is unprotected before making changes
        If .ProtectContents Then .Unprotect Password:="YourPassword"

        ' Set visibility for shapes (check if shapes exist)
        If ShapeExists(.Shapes, "JANOn") Then .Shapes("JANOn").Visible = msoCTrue
        If ShapeExists(.Shapes, "JANOff") Then .Shapes("JANOff").Visible = msoCFalse
        If ShapeExists(.Shapes, "FEBOn") Then .Shapes("FEBOn").Visible = msoCFalse
        If ShapeExists(.Shapes, "FEBOff") Then .Shapes("FEBOff").Visible = msoCFalse
        If ShapeExists(.Shapes, "MAROn") Then .Shapes("MAROn").Visible = msoCFalse
        If ShapeExists(.Shapes, "MAROff") Then .Shapes("MAROff").Visible = msoCFalse
        If ShapeExists(.Shapes, "APROn") Then .Shapes("APROn").Visible = msoCFalse
        If ShapeExists(.Shapes, "APROff") Then .Shapes("APROff").Visible = msoCFalse
        If ShapeExists(.Shapes, "MAYOn") Then .Shapes("MAYOn").Visible = msoCFalse
        If ShapeExists(.Shapes, "MAYOff") Then .Shapes("MAYOff").Visible = msoCFalse
        If ShapeExists(.Shapes, "JUNOn") Then .Shapes("JUNOn").Visible = msoCFalse
        If ShapeExists(.Shapes, "JUNOff") Then .Shapes("JUNOff").Visible = msoCFalse
        If ShapeExists(.Shapes, "JULOn") Then .Shapes("JULOn").Visible = msoCFalse
        If ShapeExists(.Shapes, "JULOff") Then .Shapes("JULOff").Visible = msoCFalse
        If ShapeExists(.Shapes, "AUGOn") Then .Shapes("AUGOn").Visible = msoCFalse
        If ShapeExists(.Shapes, "AUGOff") Then .Shapes("AUGOff").Visible = msoCFalse
        If ShapeExists(.Shapes, "SEPOn") Then .Shapes("SEPOn").Visible = msoCFalse
        If ShapeExists(.Shapes, "SEPOff") Then .Shapes("SEPOff").Visible = msoCFalse
        If ShapeExists(.Shapes, "OCTOn") Then .Shapes("OCTOn").Visible = msoCFalse
        If ShapeExists(.Shapes, "OCTOff") Then .Shapes("OCTOff").Visible = msoCFalse
        If ShapeExists(.Shapes, "NOVOn") Then .Shapes("NOVOn").Visible = msoCFalse
        If ShapeExists(.Shapes, "NOVOff") Then .Shapes("NOVOff").Visible = msoCFalse
        If ShapeExists(.Shapes, "DECOn") Then .Shapes("DECOn").Visible = msoCFalse
        If ShapeExists(.Shapes, "DECOff") Then .Shapes("DECOff").Visible = msoCFalse

        ' Set column visibility
        .Range("B:N").EntireColumn.Hidden = False
        .Range("O:FM").EntireColumn.Hidden = True

        ' Re-protect the sheet if it was protected
        .Protect Password:="YourPassword"
    End With
    Exit Sub

ErrorHandler:
    MsgBox "An error occurred: " & Err.Description, vbCritical
    Err.Clear
End Sub

' Helper function to check if a shape exists
Function ShapeExists(Shapes As Shapes, ShapeName As String) As Boolean
    On Error Resume Next
    ShapeExists = Not Shapes(ShapeName) Is Nothing
    On Error GoTo 0
End Function

Key Features of the Updated Code:
Shape Existence Check: Uses the ShapeExists helper function to ensure that a shape exists before attempting to modify its visibility.
Error Handling: Wraps the entire procedure in an On Error GoTo ErrorHandler block to gracefully handle unexpected issues. Displays a message box if an error occurs, providing details about the error.
Sheet Protection Management: Automatically unprotects the sheet at the start of the macro (if protected) and reprotects it at the end.
Visibility Management: Updates the visibility of shapes specific to "JANOn" while hiding others.
Column Visibility: Unhides the columns B:N and hides O:FM.
Click to expand...
Thank you so much! Sorry as I am new to VBA code in Excel. I replaced the code for sub Jan and Feb to test. Error is now gone, however the spread sheet is still not changing. Still shows January either way. MAR through DEC still have error, as code has not been replaced for those subs yet. Here is how I put the code above into the program box. Was not sure if the original coding should stay as well?

Also note, my tabs do not change color when selected. Perhaps that is the issue as the "on" tab is never visible?

1737120627085.png
 
It seems the shapes are not reflecting the visibility changes as expected, and the sheet tabs may not be working as intended either. Here's a full breakdown and updated guidance.

Code: 
Sub JAN()
    On Error GoTo ErrorHandler
    With Sheet1
        ' Unprotect sheet if protected
        If .ProtectContents Then .Unprotect Password:="YourPassword"

        ' Update shapes visibility
        UpdateShapeVisibility .Shapes, "JANOn", True
        UpdateShapeVisibility .Shapes, "JANOff", False

        ' Set other months' shapes to off
        Dim months As Variant
        months = Array("FEB", "MAR", "APR", "MAY", "JUN", "JUL", "AUG", "SEP", "OCT", "NOV", "DEC")
        Dim i As Long
        For i = LBound(months) To UBound(months)
            UpdateShapeVisibility .Shapes, months(i) & "On", False
            UpdateShapeVisibility .Shapes, months(i) & "Off", True
        Next i

        ' Show relevant columns
        .Range("B:N").EntireColumn.Hidden = False
        .Range("O:FM").EntireColumn.Hidden = True

        ' Highlight the selected tab
        .Tab.Color = RGB(255, 255, 0) ' Highlight in yellow

        ' Reprotect the sheet
        .Protect Password:="YourPassword"
    End With
    Exit Sub

ErrorHandler:
    MsgBox "An error occurred: " & Err.Description, vbCritical
    Err.Clear
End Sub

Sub FEB()
    On Error GoTo ErrorHandler
    With Sheet1
        ' Unprotect sheet if protected
        If .ProtectContents Then .Unprotect Password:="YourPassword"

        ' Update shapes visibility
        UpdateShapeVisibility .Shapes, "FEBOn", True
        UpdateShapeVisibility .Shapes, "FEBOff", False

        ' Set other months' shapes to off
        Dim months As Variant
        months = Array("JAN", "MAR", "APR", "MAY", "JUN", "JUL", "AUG", "SEP", "OCT", "NOV", "DEC")
        Dim i As Long
        For i = LBound(months) To UBound(months)
            UpdateShapeVisibility .Shapes, months(i) & "On", False
            UpdateShapeVisibility .Shapes, months(i) & "Off", True
        Next i

        ' Show relevant columns
        .Range("O:FM").EntireColumn.Hidden = False
        .Range("B:N").EntireColumn.Hidden = True

        ' Highlight the selected tab
        .Tab.Color = RGB(255, 255, 0) ' Highlight in yellow

        ' Reprotect the sheet
        .Protect Password:="YourPassword"
    End With
    Exit Sub

ErrorHandler:
    MsgBox "An error occurred: " & Err.Description, vbCritical
    Err.Clear
End Sub

' Helper Sub to Update Shape Visibility
Sub UpdateShapeVisibility(Shapes As Shapes, ShapeName As String, VisibleState As Boolean)
    On Error Resume Next
    If Not Shapes(ShapeName) Is Nothing Then
        Shapes(ShapeName).Visible = IIf(VisibleState, msoCTrue, msoCFalse)
    End If
    On Error GoTo 0
End Sub
 
You must log in or register to reply here.

Online statistics

Members online
0
Guests online
5
Total visitors
5
Totals may include hidden visitors.

Forum statistics

Threads
400
Messages
1,766
Members
815
Latest member
hendik

Share this page

Back
Top