Having a macro run if changes are made to certain cells

[Mldeuser]

Is it possible to write a macro that will run if a user enters data into any cell in the worksheet that has a formula in it.

Thanks
Mark

 

[lenze]

Sorry, my code will not work as written. Try this for the time being

Private Sub WorkSheet_Change(ByVal Target As Range)
If Not Target.HasFormula Then
     Application.EnableEvents = False
     MsgBox "You may not change this cell"
     Application.Undo
     Application.EnableEvents = True
End If
End Sub

This will keep them from overwriting with a constant, but not a formula. It's a start

Be sure and remove the . from the MsgBox line
lenze

 

[Mldeuser]

This one works, but it keeps the user fom entering data into the non formula cells.

Thanks
Mark

 

[lenze]

OK: Let's step back and simplify!!
Choose Edit>GoTo>Special>Formulas>OK
This will select all the cells with formulas. Now, with them selected, assign them a Name. Insert>Name>Define (say myFormulas) Now the code

Private Sub WorkSheet_Change(ByVal Target As Range)
If Not Intersect(Target,Range("myFormulas")) Is Nothing Then 
     Application.EnableEvents = False
     MsgBox "You may not change this cell"
     Application.Undo
     Application.EnableEvents = True
End If
End Sub

lenze

 

[Mldeuser]

Thanks this now only affects the cells with formulas.

One other question, can it be modified to allow for rows to be inserted or deleted. This is one of the reasons that locking the cells is not a viable option.

Thanks
Mark

 

[lenze]

What version of Excel are you using?
lenze

 

[Mldeuser]

We are using version 2003

Mark

 

[Mldeuser]

Is it possible to allow users to copy one row and insert in in the spreadsheet keeping the protection in place.