Protect/unprotect certain cells with macro

[1q2w3]

Hi all,

I need help with protecting certain cells within my worksheet to prevent cells from being edited.

I have an excel sheet that have a drop down list, it hides certain rows based on the selection. I used macros to set this up and these are working fine at the moment.

I have Column C and D that I want user to be able to edit, but E and F not.

I've tried using the protect sheet method but I am getting a run time error code 1004 - unable to set the hidden property of the range class.

Please can someone help?

Thanks

 

[mumps]

If the sheet is protected, unprotect it. Unlock all the cells that you want to be editable and lock the cells in columns E and F. Protect the sheet, with a password if you wish. At the beginning of your macro, enter a line of code to unprotect the sheet and at the end of the macro, enter another line to protect it again.

 

[1q2w3]

Hi @mumps thanks for your reply.

What would the code be to unprotect/protect the sheet?

thanks

 

[mumps]

Change the password (in red) to suit your needs.

Sub test()
    ActiveSheet.Unprotect Password:="MyPassword"
    'your code here
    ActiveSheet.Protect Password:="MyPassword"
End Sub

 

[1q2w3]

@mumps

I am still getting the error code 1004. Any ideas?

 

[mumps]

Can you please post your entire code?

 

[1q2w3]

I had to rename the below, so e.g. I have to drop down box,

Drop down box 1 (Fruit or Veg) = 'Show All', 'Fruit', 'Veg'

Based on drop down box 1 then I have drop down box 2.

Drop down box 2 (Colour of item) = 'Green', 'Red', 'Other colours'

The code is below:


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim Selection, Criteria As Range
Set Fruit or Veg = Range("A2")
Set Colour of item = Range("A4")

Select Case Fruit or veg
Case Is = "Show All": Rows("6:16").EntireRow.Hidden = False 'All rows are shown
Case Is = "Fruit": Rows("10:16").EntireRow.Hidden = True 'veg range is hidden
Rows("6:9").EntireRow.Hidden = False 'fruit range is shown
Case Is = "veg":
Rows("6:9").EntireRow.Hidden = True 'fruit range is hidden
Rows("10:16").EntireRow.Hidden = False 'veg range is shown

End Select

Select Case Colour of item
Case Is = "Green": Rows("9:16").EntireRow.Hidden = True 'Other colours are hidden
Rows("6:8").EntireRow.Hidden = False 'Green range are shown
Case Is = "Red": Rows("6:8").EntireRow.Hidden = True 'Other colours is hidden
Rows("12:16").EntireRow.Hidden = True 'Other colours range is hidden
Rows("9:11").EntireRow.Hidden = False 'Red range is shown

End Select

End Sub

 

[mumps]

In which cells are the 2 drop down lists?

 

[1q2w3]

A2, and A4

 

[mumps]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A2,A4")) Is Nothing Then Exit Sub
    ActiveSheet.Unprotect Password:="MyPassword"
    Application.ScreenUpdating = False
    Select Case Target.Address
        Case "$A$2"
            Select Case Target.Value
                Case "Show All"
                    Rows("6:16").EntireRow.Hidden = False
                Case "Fruit"
                    Rows("10:16").EntireRow.Hidden = True
                Case "veg"
                    Rows("6:9").EntireRow.Hidden = True
                     Rows("10:16").EntireRow.Hidden = False
            End Select
        Case "$A$4"
            Select Case Target.Value
                Case "Green"
                    Rows("9:16").EntireRow.Hidden = True
                    Rows("6:8").EntireRow.Hidden = False
                Case "Red"
                    Rows("6:8").EntireRow.Hidden = True
                    Rows("12:16").EntireRow.Hidden = True
                    Rows("9:11").EntireRow.Hidden = False
            End Select
    End Select
    ActiveSheet.Protect Password:="MyPassword"
    Application.ScreenUpdating = True
End Sub