Run private sub

Krystn

New Member
Joined
Nov 6, 2015
Messages
14
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

Code:
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
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Try this:
You should notice the Worksheet Change instead of Worksheet Selection change
Code:
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
 
Upvote 0
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

Code:
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.
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,726
Members
448,294
Latest member
jmjmjmjmjmjm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top