Hiding and unhiding rows

[Johanna Gronlund]

Hello,

I have been trying to work out how to do this by reading previous posts but
this has defeated me.

I have a sheet called 'inputs'. Based on a value on a cell D30, I want to
hide/unhide rows. The possible values for D30 are 1, 2 or 3.

If cell value is 1, I want to show all rows

If cell value is 2, I want to hide rows 51-61 and show all others, including
32-52 if value 3 has previously been selected.

If cell value is 3, I want to hide rows 32-52 and show all others, including
51-61 if value 2 has previously been selected.

If there is anyone who knows how to do this, please help. Or if this is just
not do-able (for someone with my skills), that would be helpful to know as
well.

Many thanks if advance!

 

[Jacob Skaria]

Select the sheet tab which you want to work with. Right click the sheet tab
and click on 'View Code'. This will launch VBE. Paste the below code to the
right blank portion. Get back to to workbook and try out.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$D$30" Then
UsedRange.EntireRow.Hidden = False
If Target.Value = 2 Then Range("A51:A61").EntireRow.Hidden = True
If Target.Value = 3 Then Range("A32:A52").EntireRow.Hidden = True
End If
End Sub

 

[Gary''s Student]

Try this Event macro:

Private Sub Worksheet_Change(ByVal Target As Range)
Set t = Target
Set d30 = Range("D30")
If Intersect(t, d30) Is Nothing Then Exit Sub
Application.EnableEvents = False
Rows.Hidden = False
If d30.Value = 2 Then
Rows("51:61").Hidden = True
End If
If d30.Value = 3 Then
Rows("32:52").Hidden = True
End If
Application.EnableEvents = True
End Sub


Because it is worksheet code, it is very easy to install and automatic to use:

1. right-click the tab name near the bottom of the Excel window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you have any concerns, first try it on a trial worksheet.

If you save the workbook, the macro will be saved with it.


To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

 

[Johanna Gronlund]

Thanks, that worked really well! I am very pleased!

However, now that I have shared it with other people they would like to have
it looking better visually. They would like to have a combobox where they can
select the values from. Is this possible? I have managed to create a combobox
which excel has named 'dropdown91' but was unable to modify the macro to take
the values from that box.

Many thanks again!

 

[Jacob Skaria]

Instead add a combobox from Vew>ToolBars>ControlToolBox... and add the below
code to sheet module and Workbook as below

'Add the below in your Sheet module
Dim varData As Variant
Private Sub ComboBox1_Change()
If varData <> ComboBox1.Value Then
UsedRange.EntireRow.Hidden = False
Select Case ComboBox1.Value
Case 2
Range("A51:A61").EntireRow.Hidden = True
Case 3
Range("A32:A52").EntireRow.Hidden = True
End Select
End If
End Sub

Private Sub Worksheet_Activate()
varData = ComboBox1.Value
End Sub


'Add the below in Workbook ('ThisWorkbook') Open
Private Sub Workbook_Open()
Me.Sheets("Sheet3").ComboBox1.Add 1
Me.Sheets("Sheet3").ComboBox1.Add 2
Me.Sheets("Sheet3").ComboBox1.Add 3
End Sub

 

[Johanna Gronlund]

Ops! Something went wrong. I got an error message: Run time error '424'
object not found. I thought that I followed the instructions but obviously
something is missing.

Thanks again.

 

[Jacob Skaria]

---Check out the combo box name ...I assumed that as ComboBox1

---From workbook press Alt+F11 to launch VBE (Visual Basic Editor). From the
left treeview search for the workbook name and click on + to expand it.
Within that you should see the following

VBAProject(Your_Filename)
Microsoft Excel Objects
Sheet1(Sheet1)
Sheet2(Sheet2)
Sheet3(Sheet3)
This Workbook

Double click 'This WorkBook' and paste the below code to the right code pane.

Private Sub Workbook_Open()
Me.Sheets("Sheet3").ComboBox1.Add 1
Me.Sheets("Sheet3").ComboBox1.Add 2
Me.Sheets("Sheet3").ComboBox1.Add 3
End Sub

Double click on the Sheet where you have the combox control and place the
below code (2 Subs)

Dim varData As Variant
Private Sub ComboBox1_Change()
If varData <> ComboBox1.Value Then
UsedRange.EntireRow.Hidden = False
Select Case ComboBox1.Value
Case 2
Range("A51:A61").EntireRow.Hidden = True
Case 3
Range("A32:A52").EntireRow.Hidden = True
End Select
End If
End Sub

Private Sub Worksheet_Activate()
varData = ComboBox1.Value
End Sub

 

[Johanna Gronlund]

Thanks for your continuing assistance.

I have followed your instructions step by step and the same message
persists. I re-named the combo box to ComboBox1 as it was easier than
changing the code so I don't think this is problem.

In the code that goes into ThisWorkbook, there are references to 'Sheet3'.
Do I need to change that? The sheet where I have my combobox (and where I
have pasted the two other subs) is called Sheet3 (Inputs) in the VBE view.

If you have any further suggestions of what I might want to try, they would
be gratefully received!

 

[Jacob Skaria]

You need to change that to

Private Sub Workbook_Open()
Me.Sheets("Inputs").ComboBox1.Add 1
Me.Sheets("Inputs").ComboBox1.Add 2
Me.Sheets("Inputs").ComboBox1.Add 3
End Sub