VB Code does not work when sheet or workbook is protected

[novicevbaer]

I have a workbook that requires protection so that the users can onl
see the main worksheet. It wouldnt matter if they saw the other sheet
but I need to hide a code that automatically puts the users budget i
the budget cell.

When the user enters an item in the first column it automatically put
the cost and later in the row makes some calculations based on how man
they are selecting.

My problem is this. I used a small vb code that says if they chang
their mind and delete the item in the first column the entire ro
becomes null. I did this by saying

Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Column > 1 Then Exit Sub
If .Row < 7 Then Exit Sub

Application.EnableEvents = False
If .Value = "" Then
.Offset(0, 1).Value = ""
End If

If .Value = "" Then
.Offset(0, 2).Value = ""
End If

If .Value = "" Then
.Offset(0, 3).Value = ""
End If

If .Value = "" Then
.Offset(0, 4).Value = ""
End If

If .Value = "" Then
.Offset(0, 6).Value = ""
End If

If .Value = "" Then
.Offset(0, 7).Value = ""
End If

If .Value = "" Then
.Offset(0, 8).Value = ""
End If

Application.EnableEvents = True
End With
End Sub

When the protection is on sometimes when you delete from the firs
column it says "you are trying to change a read only worksheet" an
other times it just deletes that item but not the row.

All of the cells in the worksheet are unlocked and the property for th
file is not read only.

Can anyone tell me how to get around this?

Thanks a lot!!

 

[Erin]

What version of Excel are you using? I'm using 2003 and
wasn't able to replicate your problem. As long as the cell
the user is changing and the cells affected by the macro
are unlocked the macro worked as described.

A couple notes: Before exiting the macro mid-routine you
should send it to a subroutine to enable events. Also
your macro can be condensed as follows:

(Note I moved the EnableEvents commands to the beginning
and end so that there is no extraneous code within the
With / End With section.)

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False

With Target

If .Column > 1 Then GoTo Rtn1
If .Row < 7 Then GoTo Rtn1

If .Value = "" Then
Range(.Offset(0, 1), .Offset(0, 8)).Value = ""
End If
End With

Rtn1:
Application.EnableEvents = True

End Sub