Macro for hiding rows and columns based on selection in drop down list

[hallhill]

Hi,
I need help with the macro vba coding....

My tab in the workbook is titled "Worksheet"
In cell A9, I have data validation set up where the user can select from a drop down list that has 4 values (Box, Bag, Ramp, Drive, null value)
If the user selects Box or the value is null in cell $A$9, I don't want the macro to do anything.
If the user selects Bag, Ramp, or Drive from the drop down list in cell $A$9 I want prevent them from making changes or selections in cells C12-C20 and E33-E83. I would even be happy if there was a reminder comment or something that appeared if they selected Bag, Ramp, or Drive, that said "do not make a selection in cells C12-C20 or E33-E88 based on your selection in cell A9.
NOTE: C12-C20 and E33-E83 also have data validation set up where the user can only select from a drop down list.

I considered having a macro that would hide rows 12-20 and hide column E if the user selected Bag, Ramp, or Drive from cell $A$9. But I am not sure how to write the VBA code for that.

Anyone able to assist? Thanks so much....M

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Make a selection in A9. This will hide the rows and column E. You will have to decide how and when you would want the rows and column visible again.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A9")) Is Nothing Then Exit Sub
    Select Case Target.Value
        Case "Bag", "Ramp", "Drive'"
            ActiveSheet.Rows("12:20").Hidden = True
            Columns("E").EntireColumn.Hidden = True
            MsgBox "You cannot make a selection in cells C12-C20 or E33-E88 based on your selection in cell A9."
    End Select
End Sub

 

[hallhill]

Hi, I am getting a debug error when it gets to the Row that I have in bold, underline below. The Run-time error '1004': Unable to set the Hidden property of the Range class


Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A9")) Is Nothing Then Exit Sub
Select Case Target.Value
Case "Bag", "Ramp", "Drive'"
ActiveSheet.Rows("12:20").Hidden = True
Columns("E").EntireColumn.Hidden = True
MsgBox "You cannot make a selection in cells C12-C20 or E33-E88 based on your selection in cell A9."
End Select
End Sub

 

[hallhill]

I think I figured out why it was failing....I have all the cells in the worksheet locked (except for those fields that I want the user to make changes to) and it is password protected. When I removed the password and tested it the macro worked.

I have to protect the cells I don't want the user to make changes to...if I change the setting in the protection to allow format changes to rows and columns it appears to work fine.

Would it be possible to add to the coding the ability to unhide rows 12-20 and column E if the user changes their selection in A9 to 'box" or if they leave that cell blank?

 

[mumps]

Try this macro. It hides/unhides the rows and column.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A9")) Is Nothing Then Exit Sub
    Select Case Target.Value
        Case "Bag", "Ramp", "Drive'"
            ActiveSheet.Rows("12:20").Hidden = True
            Columns("E").EntireColumn.Hidden = True
            MsgBox "You cannot make a selection in cells C12-C20 or E33-E88 based on your selection in cell A9."
        Case "Box", ""
            ActiveSheet.Rows("12:20").Hidden = False
            Columns("E").EntireColumn.Hidden = False
    End Select
End Sub

If you want to leave the rows and columns to be always visible, we could instead lock/unlock the fields that the user changes. If this is better for you, let me know and I'll modify the macro.

 

[hallhill]

Hi,
I love the suggestion of just lock/unlock the fields that the user can change based on their selection in cell A9.

One note, there are other fields in the worksheet that should be left unlocked or locked regardless of the selection in A9....but the two areas that are not required and should be left blank if the user selects Bag, Ramp or Drive includes cells B12-20 and E34-E83.

Do you mind sending me that macro code while I try the last one you sent?

 

[mumps]

Try this macro. Make sure that cell A9 and ranges B12-20 and E34-E83 are unlocked. Change "mypassword" in the code (2 occurrences) to suit your needs.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A9")) Is Nothing Then Exit Sub
    ActiveSheet.Unprotect Password:="mypassword"
    Select Case Target.Value
        Case "Bag", "Ramp", "Drive'"
            Range("C12:C20,E33:E83").Locked = True
            MsgBox "You cannot make a selection in cells C12-C20 or E33-E88 based on your selection in cell A9."
        Case "Box", ""
            Range("C12:C20,E33:E83").Locked = False
    End Select
    ActiveSheet.Protect Password:="mypassword"
End Sub