How to hide rows with drop-down selection

Leo

New member
Joined
Jul 10, 2017
Messages
11
Reaction score
0
Points
0
Hi,

Can anyone help me with my excel problem, please? I'm trying to hide/unhide rows according to a drop-down selection. Please find more information in the excel spreadsheet.

Thank you very much,
Leo

View attachment Property Setup Help.xlsx
 
In the attached are two solutions.
In the first I've assigned that dropdown control a macro called blah which is called when you change that dropdown.
In the second, I've added a validation dropdown in cell Y6 which operates in a similar way but on row 50 of the sheet. It uses the worksheet_change event handler.

Dropdown boxes as you have them are a bit more hard work than simple validation dropdown, having said that, the drop down arrow of a validation dropdown doesn't appear until you get onto the cell to change it, so the user may be less aware that you can change it.

Both solutions' code are in Sheet1's code-module.
 

Attachments

  • ExcelGuru8158Property Setup Help.xlsm
    25.4 KB · Views: 331
Awesome, thank you so much!

I tried to copy your code into my original spreadsheet's code-module but it doesn't work and no rows are hidden according to the drop-down selection. I saved the work-book as a macro enabled workbook but that didn't help either. Am I missing anything?
Also when I choose a property type (Row 6), rows 8 to 33 remain hidden. They should only be hidden when no property type is selected at all. Thanks again!
 
Am I missing anything?
If you right-click the sheet's tab and choose View code… do you see your code? If not that's where the code should be, not in a regular code-module.



Also when I choose a property type (Row 6), rows 8 to 33 remain hidden. They should only be hidden when no property type is selected at all.
Change code to:
Code:
Sub blah()
With DropDowns("Drop Down 1")
  If .Value > 1 Then Range("8:33").EntireRow.Hidden = False Else Range("8:33").EntireRow.Hidden = True
  If .Value = 2 Or .Value = 5 Then
    Rows(25).Hidden = False
  Else
    Rows(25).Hidden = True
  End If
End With
End Sub
I don't know which of the two solutions I offered you gone for and I'm not going to keep writing code for both.
 
Last edited:
Thanks again. That's exactly where I copied the code, and I see the code when I follow your "View code" steps as described above.
 
Make sure the name of the dropdown tallies:"Drop Down 1" Adjust code or name of dropdown.
Capture3.JPG
 
It's the same name. Maybe something else has to be adjusted?
 
I also noticed that the layout of the drop-down boxes that are hidden changes so that the arrow itself gets wider and wider every time I use the "select property type option
Capture.PNG
 
@p45cal

Code:
Sub blah()
    Rows("8:33").Hidden = DropDowns("Drop Down 1").Value > 1 
End Sub

;)
 
Last edited:
And:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address = "$Y$6" Then Rows(50).Hidden = InStr("OfficeRetail Mall/Store", Target.Value) = 0
End Sub
 
It's so slow to remote-debug…
Let's find out whether the code is being triggered:
Put a break-point at the first line of the blah sub (F9), on the Sub blah line itself. Then change the dropdown's value on the sheet. Does the code window present itself with that first line highlighted in yellow?
 
Last edited:
That looks like a dropdown arrow that has been stretched horizontally. Are you also playing with hiding columns too?
You're sure this is a Forms dropdown and not an ActiveX dropdown?
It's likely to do with the control's move and size with cells options; are the borders of the check box within the boundaries of a single cell.

It would be lot easier for us if you were to supply the file otherwise it's like the blind man looking for the black cat in the dark room.
 
Thank you all. I'm going for option 2 now (drop-down in cell). It seems to be causing less graphic issues with the drop-down boxes.

Problem 1:
Only if property type "Warehouse/Distribution Centre" is selected, row 8 appears to specify the selection (another drop-down). Office and all other types should not have row 8, however, if someone chooses "Warehouse/Distribution Centre" by accident and changes it to "Office", row 8 doesn't disappear.

Problem 2:
Rows 31-35 should be hidden and only appear when drop-down in row 29 is "Yes". When any property type in row 6 is selected rows 31-35 aren't hidden but disappear when "No" is selected.

Thanks!!!
 

Attachments

  • Energy Star-RealData Portfolio Setup Macro Help.xlsm
    28.8 KB · Views: 14
Try the attached.
Code:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("D53,D51,D29,D6")) Is Nothing Then
  Application.ScreenUpdating = False
  If Range("D6").Value = "Select a Property Type" Then
    Rows("7:200").Hidden = True
  Else
    Rows("7:200").Hidden = False
    Rows("55:56").Hidden = Not (Range("D53").Value = "Yes")  'this is an example; I don't know what or if you want to do anything here.
    Rows("31:35").Hidden = Not (Range("D29").Value = "Yes")
    Rows("27:28").Hidden = Not (Range("D6").Value = "Office" Or Range("D6").Value = "Retail Mall/Store")
    Rows(8).Hidden = Not (Range("D6").Value = "Warehouse/Distribution Centre")
  End If
  Application.ScreenUpdating = True
End If
End Sub
It's a logical minefield and my offering is probably not optimal, but it should point you in the direction of a workable solution.
 

Attachments

  • ExcelGuru8158Energy Star-RealData Portfolio Setup Macro Help.xlsm
    30.4 KB · Views: 17
Thanks again! The code works without any issues. Can I lock the sheet that users can only access to the drop-downs and punch in numbers where they're asked to do so?

Cheers!
 

Attachments

  • Portfolio Setup Form Help.xlsm
    44.1 KB · Views: 13
Unlock the cells which you want users to gain access to. The rest are locked by default. Protect the sheet thus:
Capture3.JPG
 

Attachments

  • ExcelGuru8158Portfolio Setup Form Help.xlsm
    44.6 KB · Views: 16
Last edited:
Back
Top