Unable to run macro with protected sheet

[ropark1]

When we try to run the following Macro to enlarge the text area for comments with the sheet protected we get a "Runtime error 1004" and the macro will not execute. With the sheet protection off the Macro runs as designed. Any ideas on a work around?

Public Sub Worksheet_Change(ByVal Target As Range)
If Excel.Application.Version < "9" Then Exit Sub
Dim r As Integer, rh As Single
r = Target.Row
If r = 5 Or r = 19 Or r = 20 Or r = 25 Or r = 26 Or r = 37 Or r = 38 Then
If IsArray(Target.Value) Then
rh = 15.75
Else
rh = ((Len(Target.Value) 130) + 1) * 15.75
End If
Target.Rows(1).RowHeight = rh
End If

[Von Pookie]

You need to add a line in the macro which turns off the sheet protection: ActiveSheet.Unprotect

Then to protect it again, add: ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

And, just for future reference, I got those by running the macro recorder then copying

[Ian Mac]

On 2002-05-20 16:06, ropark1 wrote:
When we try to run the following Macro to enlarge the text area for comments with the sheet protected we get a "Runtime error 1004" and the macro will not execute. With the sheet protection off the Macro runs as designed. Any ideas on a work around?

Public Sub Worksheet_Change(ByVal Target As Range)
If Excel.Application.Version < "9" Then Exit Sub
Dim r As Integer, rh As Single
r = Target.Row
If r = 5 Or r = 19 Or r = 20 Or r = 25 Or r = 26 Or r = 37 Or r = 38 Then
If IsArray(Target.Value) Then
rh = 15.75
Else
rh = ((Len(Target.Value) 130) + 1) * 15.75
End If
Target.Rows(1).RowHeight = rh
End If

try:

Private Sub Worksheet_Change(ByVal Target As Range)

ActiveSheet.Unprotect ("password")

'your code

ActiveSheet.Protect ("password")

End Sub

change "Password" to the sheet password.

[ropark1]

Thanks for the help!