VBA control button to Toggle hide and unhide columns

[Shadkng]

Hi, I have a form control button called "Hide Col" that runs the code below. I would like to make the button toggle to hide and unhide. I tried to implement some code that I searched online but I'm unable to do it correctly. Also, I see it's possible to have the control button name change as well. So could we have it say "Hide Col" and then ""show Col" ? Thanks in advance

Sub DETAIL_FORM_HIDE_COL()
Dim c As Range
    For Each c In Range("I12:Z12").Cells
        If c.Value = "HIDE" Then
            c.EntireColumn.Hidden = True
    End If
    Next c
End Sub

 

[mole999]

have you tried using the GROUP function which gives you a button and no code. IN the Page Layout

 

[Shadkng]

No I am not familiar with that. I am new to VBA and know just the basics.

 

[rlv01]

One way. The button you create must be edited so that the label is initially "Hide Col"

Sub DETAIL_FORM_HIDE_COL()
    Dim c As Range
    Dim Sh As Shape

    For Each Sh In ActiveSheet.Shapes
        With Sh
            If .Type = 8 And (Sh.TextFrame.Characters.Text = "Hide Col" Or Sh.TextFrame.Characters.Text = "Show Col") Then
                Exit For
            End If
        End With
    Next Sh

    If Not Sh Is Nothing Then
        For Each c In Range("I12:Z12").Cells
            If c.Value = "HIDE" Then
                If Not c.EntireColumn.Hidden Then
                    c.EntireColumn.Hidden = True
                    Sh.TextFrame.Characters.Text = "Show Col"
                Else
                    c.EntireColumn.Hidden = False
                    Sh.TextFrame.Characters.Text = "Hide Col"
                End If
            End If
        Next c
    End If
End Sub

 

[Shadkng]

Works! Thank you very much. I have another unrelated issue...do I have to start a new post?

 

[Yongle]

Yes - best to start a new post for a different topic