Worksheet_change hide rows when value in cell is changed by form control element-CodePudding

I am doing some changes in wb created by someone else and need do as less harm as possible as this excel will be used company wide. My issue is, that there are two form controls buttons which changes value in Z1 to 1 or 2, based on the selection.

Option 2 is for one row only, so I would need to hide several lines before this row. I am trying this through worksheet change, but without no luck as the value is not changed in proper way for VBA. The change macro works when I use F2 enter manually, but not when I change it by button only.

I tried to overpass F2 Enter through

Range("Z1").FormulaR1C1 = Range("Z1").FormulaR1C1

But with no luck. I tried several versions of code. My favourite one is this one

    If Target.Address = "$Z$1" Then
        Range("Z1").FormulaR1C1 = Range("Z1").FormulaR1C1
        If Target = "2" Then
            Rows("5:13").EntireRow.Hidden = True
        Else
            Rows("5:13").EntireRow.Hidden = False
        End If
    End If

My others are

    If Not Intersect(Target, Range("Z1")) Is Nothing Then
        Rows("5:13").EntireRow.Hidden = CBool(Range("Z1").Value = 2)
    End If

========

If Not Application.Intersect(Target, Range(Target.Address)) Is Nothing Then ', Range(Target.Address)
        Application.EnableEvents = False
        Range("Z1").FormulaR1C1 = Range("Z1").FormulaR1C1
        Select Case Target.Value
        Case Is = "2":  Rows("5:13").EntireRow.Hidden = True

        Case Is = "1":  Rows("5:13").EntireRow.Hidden = False

        End Select
        Application.EnableEvents = True
End If

========

If Intersect(Range("Z1"), Target) Is Nothing Then Exit Sub
Range("Z1").FormulaR1C1 = Range("Z1").FormulaR1C1
Select Case Range("Z1").Value
    Case Is = 2
        Set HideRows = Rows("5:13")
        Set ViewRows = Nothing
    Case Is = 1
        Set ViewRows = Rows("5:13")
End Select

On Error Resume Next
HideRows.Hidden = True
ViewRows.Hidden = False

Neither one is working, I made a video and uploaded on YouTube

What am I missing? I need it for the user to be connected with form control selection. I am not able to pursue change of the form control.

CodePudding user response：

Changes to "linked cell" values do not trigger the worksheet_change event.

You could make it trigger the Worksheet_Calculate event by placing (eg) =Z1 in Z2, then that formula would calculate whenever the value in Z1 is changed. That would mean you'd be responding to every calculation on the sheet though, so you can make sure you only hide/unhide when the value in Z1 has changed:

Private Sub Worksheet_Calculate()
    Dim opt, cCache As Range
    Set cCache = Me.Range("Z3")                       'cell with last value
    opt = Me.Range("Z1").Value                        'get current value
    If opt <> cCache.Value Then                       'compare to last value
        Debug.Print "Rows toggle"
        Me.Rows("5:13").EntireRow.Hidden = (opt = 2)  'hide/unhide
        cCache.Value = opt                            'save this for next change
    End If
End Sub