Run private sub

[Krystn]

Hi I’m new to VBA, so new in fact that this is my first attempt so please bear with me.

I’ve applied the code below to automatically hide inapplicable rows, depending on the value in cell K6.

(That value being one of the four possible outcomes of two Combo-box cell-link results - concatenated, hence 11,12, 21 & 22. Perhaps there’s a fancier code to achieve this but it’s working, I’m just stating in case it’s relevant to my question.)

When the combo-box selection is made, my code does not deploy until a new cell is selected. Users of the spreadsheet would naturally expect the necessary change to occur on selection of the combo boxes and wouldn’t be aware that they’d need to click to activate it.

Is it possible to trigger the event without requiring cell selection?

Thanks in advance,
Krystn

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 On Error Resume Next
 
If Range("K6").Value = "11" Then
        Rows("10:12").EntireRow.Hidden = True
        
    ElseIf Range("K6").Value = "21" Or "12" Or "22" Then
        Rows("10:12").EntireRow.Hidden = False

    End If

End Sub

 

[My Aswer Is This]

Try this:
You should notice the Worksheet Change instead of Worksheet Selection change

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
 
If Range("K6").Value = "11" Then
        Rows("10:12").EntireRow.Hidden = True
        
    ElseIf Range("K6").Value = "21" Or "12" Or "22" Then
        Rows("10:12").EntireRow.Hidden = False

    End If

End Sub

 

[Fishboy]

Hi I’m new to VBA, so new in fact that this is my first attempt so please bear with me.

I’ve applied the code below to automatically hide inapplicable rows, depending on the value in cell K6.

(That value being one of the four possible outcomes of two Combo-box cell-link results - concatenated, hence 11,12, 21 & 22. Perhaps there’s a fancier code to achieve this but it’s working, I’m just stating in case it’s relevant to my question.)

When the combo-box selection is made, my code does not deploy until a new cell is selected. Users of the spreadsheet would naturally expect the necessary change to occur on selection of the combo boxes and wouldn’t be aware that they’d need to click to activate it.

Is it possible to trigger the event without requiring cell selection?

Thanks in advance,
Krystn

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 On Error Resume Next
 
If Range("K6").Value = "11" Then
        Rows("10:12").EntireRow.Hidden = True
        
    ElseIf Range("K6").Value = "21" Or "12" Or "22" Then
        Rows("10:12").EntireRow.Hidden = False

    End If

End Sub

Hi Krystn, welcome to the boards.

If you change this from a Worksheet_SelectionChange event to a Worksheet_Change event you can fire off the rules as soon as the value in K6 is changed. If the value is 11 then rows 10:12 are hidden. If the value in K6 is not 11 those rows will unhide.

If that sounds like what you are trying to achieve simply take the work Selection out of the sub name line and re-save your file.