400 error on protected sheet

[lesley burlingham]

I have the following VB in my workbook to hide row 68 when the answer to a dropdown is 3. The answer is stored in sheet 2. When I protect the sheet and select the drop down I get the 400 error. I need to have the sheet protected.

Sub DropDown1_Change()
If Sheet2.Range("$C$98") = 0 Then
Exit Sub
Else
Worksheets("Request").Rows(68).Hidden = (UCase(Sheet2.Range("$C$98")) = 3)
End If
End Sub

 

[Mike H..]

I have the following VB in my workbook to hide row 68 when the answer to a dropdown is 3. The answer is stored in sheet 2. When I protect the sheet and select the drop down I get the 400 error. I need to have the sheet protected.

Sub DropDown1_Change()
If Sheet2.Range("$C$98") = 0 Then
Exit Sub
Else
Worksheets("Request").Rows(68).Hidden = (UCase(Sheet2.Range("$C$98")) = 3)
End If
End Sub

Hi,

You will need to un-protect the sheet at runtime then re-protect. Note that the password is case sensitive. If you don't have a password then delete the bit after unprotect/protect on each line.

ActiveSheet.Unprotect Password:="secret"

'your code

ActiveSheet.Protect Password:="secret"

 

[NARAYANK991]

Hi Lesley ,

A couple of points :

1. Since the tab Sheet2 needs to be protected , all you have to do is unprotect the cell C98 , since that is the cell which needs to be written to by the dropdown.

2. Since the dropdown seems to be having just digits ( you are checking for values of 0 and 3 ) , the UCase function is not necessary ; the UCase function is needed to convert a lower-case letter ( a through z ) into upper-case.

3. You are checking for values of 0 and 3 ; what are the other values in the dropdown , and what is to be done if those values are selected ? If 0 and 3 are the only values , your code can be shortened to :

Sub DropDown1_Change()
    If Sheet2.Range("$C$98") = 3 Then Worksheets("Request").Rows(68).Hidden = True
End Sub

Narayan