Page 2 of 2 FirstFirst 1 2
Results 11 to 17 of 17

Thread: How to hide rows with drop-down selection

  1. #11
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,240
    Articles
    0


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

    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 by p45cal; 2017-08-09 at 09:35 PM.

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

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

  4. #14
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,240
    Articles
    0
    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.

  5. #15
    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!
    Attached Files Attached Files

  6. #16
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,240
    Articles
    0
    Unlock the cells which you want users to gain access to. The rest are locked by default. Protect the sheet thus:
    Click image for larger version. 

Name:	Capture3.JPG 
Views:	7 
Size:	27.7 KB 
ID:	7202
    Attached Files Attached Files
    Last edited by p45cal; 2017-08-12 at 04:23 PM.

  7. #17
    Awesome! Thank you so much

Page 2 of 2 FirstFirst 1 2

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
  •