VBA to Hide rows based on Combo Box Result

Gem866

New Member
Joined
Feb 11, 2015
Messages
12
I have a spreadsheet that contains data in the range A10:G25
I use a combo box to make a selection eg "internal" or External". The output from this selection is stored in cell K3. The output shows either 1 or 2

I want to hide rows based on what that output value is eg if output value is 1 then hide rows 19:25 and if the output value is 2 hide rows from 10:18

Ive tried the following VAB Macro, which is stored in Microsoft excel objects Sheet 1.

The Macro doesn't seem to work (Rows are not hiding) and so I need some help please

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("K3") = "1" Then
Rows("19:25").Hidden = True
ElseIf Range("K3").Value = "2" Then
Rows("10:18").Hidden = True

End If
End Sub

Thanks in Advance for your help
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,682
Office Version
  1. 365
Platform
  1. Windows
The change event does not work if the cell is linked to a combobox.
What type of combo is it, as Form Control, or an ActiveX?
 

Gem866

New Member
Joined
Feb 11, 2015
Messages
12
Hmm Combo box accessed from the developer tab.
Screen Shot 2020-09-17 at 10.04.25 pm.jpg
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,682
Office Version
  1. 365
Platform
  1. Windows
If you right click the combo do you see an option for "Assign macro"?
 

Gem866

New Member
Joined
Feb 11, 2015
Messages
12

ADVERTISEMENT

Yes but not sure what macro name is to assign
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,682
Office Version
  1. 365
Platform
  1. Windows
OK assign this macro to the combo.
VBA Code:
Sub Gem()
   Rows("19:25").Hidden = Range("K3").Value = 1
   Rows("10:18").Hidden = Range("K3").Value = 2
End Sub
The code needs to go in a normal module.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,682
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,853
Messages
5,627,259
Members
416,236
Latest member
Lynchbox

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
Top