CODEWITHSUNDEEP
excelvba
Yaneth Gil
Yaneth Gil

Reputation: 61

Activate macro automatically without having to click on target cell

I have a macro that hides certain rows when the values in a cell change. However this macro is not running unless you enter the target cell and click on it. I have tried several alternatives but none work for me.

Sheet

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("$b$156").Value = 1 Then Call oculta_4
If Range("$b$156").Value = 2 Then Call oculta_5
If Range("$b$156").Value = 3 Then Call oculta_6
If Range("$b$156").Value = 4 Then Call oculta_7
End Sub

Macro

Sub oculta_4()
Rows("158:176").EntireRow.Hidden = False
Range("$c$158").Select
For Each celda In Range("$c$158:$c$176")
If celda.Value = 0 Then
ActiveCell.EntireRow.Hidden = True
End If
ActiveCell.Offset(1).Select
Next
End Sub

Upvotes: 0

Views: 108

Answers (1)

chris neilsen
chris neilsen

Reputation: 53136

As others have said, to respond to a value changed by a Formula, you need to use Worksheet_Calculate.

As Worksheet_Calculate does not have a Target property, you need to create your own detection of certain cells changing. Use a Static variable to track last value.

You should also declare all your other variables too.

Repeatedly referencing the same cell is slow and makes code more difficult to update. Put it in a variable once, and access that

Select Case avoids the need to use many If's

Don't use Call, it's unnecessary and obsolete.

Adding Application.ScreenUpdating = False will make your code snappy, without flicker

Writing the hidden state of a row takes a lot longer than reading it. So only write it if you need to.

Something like this (put all this code in the code-behind your sheet (that's Hoja1, right?)

Private Sub Worksheet_Calculate()
    Static LastValue As Variant
    Dim rng As Range

    Set rng = Me.Range("B156")

    If rng.Value2 <> LastValue Then
        LastValue = rng.Value2
        Select Case LastValue 
            Case 1: oculta_4
            Case 2: oculta_5
            Case 3: oculta_6
            Case 4: oculta_7
        End Select
    End If
End Sub


Sub oculta_4()
    Dim celda As Range
    Application.ScreenUpdating = False
    For Each celda In Me.Range("C158:C176")
        With celda.EntireRow
            If celda.Value = 0 Then
                If Not .Hidden Then .Hidden = True
            Else
                If .Hidden Then .Hidden = False
            End If
        End With
    Next
    Application.ScreenUpdating = True
End Sub

Upvotes: 1

Related Questions