macro does not work if form protected

[Wanna Learn]

Hello
Excel 2002 I created a form with a drop down list as follows : Select
Country, US, CA
Then I created this macro
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = Range("B36").Address Then
If Target = "US " Then
Range("C49") = "State"
Else
If Target.Address = Range("B36").Address Then
If Target = "Canada " Then
Range("C49") = "Province"
End If

End If
End If
End If
End Sub

IF the form is not protected the macro works but once I protected the form
the macro does not work. (Cell C49 is locked)
Thanks in advance

 

[Jim Rech]

Your macro should unprotect and reprotect the worksheet.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$36" Then
ActiveSheet.Unprotect "pass"
If Target.Value = "US" Then
Range("C49").Value = "State"
Else
Range("C49").Value = "Province"
End If
ActiveSheet.Protect "pass"
End If
End Sub

--
Jim
| Hello
| Excel 2002 I created a form with a drop down list as follows : Select
| Country, US, CA
| Then I created this macro
| Private Sub Worksheet_Change(ByVal Target As Range)
|
| If Target.Address = Range("B36").Address Then
| If Target = "US " Then
| Range("C49") = "State"
| Else
| If Target.Address = Range("B36").Address Then
| If Target = "Canada " Then
| Range("C49") = "Province"
| End If
|
| End If
| End If
| End If
| End Sub
|
| IF the form is not protected the macro works but once I protected the form
| the macro does not work. (Cell C49 is locked)
| Thanks in advance
|

 

[Dave Peterson]

Either unlock that C49 cell--or add code that unprotects the sheet, does the
work, then reprotects the sheet.