macro does not work if form protected
 

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

macro does not work if form protected
 

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
"Wanna Learn" wrote in message
...
| 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
|

macro does not work if form protected
 

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



Wanna Learn wrote:

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