Page 1 of 2 1 2 LastLast
Results 1 to 10 of 17

Thread: How to hide rows with drop-down selection

  1. #1

    How to hide rows with drop-down selection



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

    Property Setup Help.xlsx

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,204
    Articles
    0
    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.
    Attached Files Attached Files

  3. #3
    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!

  4. #4
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,204
    Articles
    0
    Quote Originally Posted by Leo View Post
    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.



    Quote Originally Posted by Leo View Post
    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 by p45cal; 2017-08-08 at 09:38 PM.

  5. #5
    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.

  6. #6
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,204
    Articles
    0
    Make sure the name of the dropdown tallies:"Drop Down 1" Adjust code or name of dropdown.
    Click image for larger version. 

Name:	Capture3.JPG 
Views:	7 
Size:	16.4 KB 
ID:	7178

  7. #7
    It's the same name. Maybe something else has to be adjusted?

  8. #8
    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
    Click image for larger version. 

Name:	Capture.PNG 
Views:	11 
Size:	13.8 KB 
ID:	7181

  9. #9
    @p45cal

    Code:
    Sub blah()
        Rows("8:33").Hidden = DropDowns("Drop Down 1").Value > 1 
    End Sub
    Last edited by snb; 2017-08-09 at 08:09 AM.

  10. #10
    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

Page 1 of 2 1 2 LastLast

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •