Call a hiding macro with a checkbox-CodePudding

I wanted to make a checkbox, calling a macro that hides and unhides columns on Excel worksheet with specific value in cell, but it is not working

I tried the following VBA script

Sub Hide_Forecasts()
Dim c As Range

    For Each c In Range("E12:CF12").Cells
        If c.Value = "Forecast" Then
            c.EntireColumn.Hidden = True
        End If
    Next c

End Sub
Sub Unhide_Forecasts()
    
Dim c As Range

    For Each c In Range("E12:CF12").Cells
        If c.Value = "Forecast" Then
            c.EntireColumn.Hidden = False
        End If
    Next c

End Sub

Sub CheckBox_For()
If CheckBox1.Value = True Then
    Call Hide_Forecasts
Else
    Call Unhide_Forecasts
End If
End Sub

Please help me out

CodePudding user response：

You haven't said what type of checkbox you're using - Form Control or ActiveX Control.

For an ActiveX Control right-click the control and select View Code.
Use this code that sits behind the worksheet (CheckBox1 will be named after your checkbox).

Private Sub CheckBox1_Click()
    Dim Cell As Range
    For Each Cell In Me.Range("E12:CF12")
        If Cell.Value = "Forecast" Then
            'Checkbox returns TRUE/FALSE - Hidden takes TRUE/FALSE so just connect the two up.
            Cell.EntireColumn.Hidden = Me.CheckBox1.Value
        End If
    Next Cell
End Sub

For a Form Control right-click the control and select Assign Macro.
Place this code in a normal module.
Sheet1 is the codename for the sheet (that's the name not in brackets in the Project Explorer).

'Form Control can have three values:
' 1     = Checked
' -4146 = Unchecked
' 2     = Mixed - ignoring that this value may occur.
Public Sub Checkbox_For()

    Dim ChkValue As Boolean
    'Is value different from -4146?  Returns TRUE = Checked or Mixed / FALSE = Unchecked
    ChkValue = Sheet1.Shapes("Check Box 1").OLEFormat.Object.Value <> -4146

    Dim Cell As Range
    For Each Cell In Sheet1.Range("E12:CF12")
        If Cell.Value = "Forecast" Then
            Cell.EntireColumn.Hidden = ChkValue
        End If
    Next Cell
    
End Sub