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

 

[Mike Blackman]

Hi,

Right Click on the sheet Tab in question, paste this in;

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, [B]Range("B2")[/B]) Is Nothing Then
    MyMacro
End If

End SUb

Chnage the Range("B2") to meet your specified range anf the My Macro part for your desired actions.

 

[Mike Blackman]

Hi,

Actually change that too;

Private Sub Worksheet_Change(ByVal Target As Range)

If HasFormula(Target) Then
    MyMacro
End If

End Sub

 

[Mldeuser]

Question where it says my macro do I insert the macro name or the entire macro

 

[lenze]

Could be either way, depending on what your macro does. Let's back up a minute. What do you want to do?
lenze

 

[Mldeuser]

I would like to have a macro that is currently set up, to run if a change is made to any cell that has a formula in it. Basically putting the formula right back in the cell. Locking the cells is causing other issues so I thought if there were a few macros in place then it would not matter if a user accidentally over wrote a formula.

Thanks
Mark

 

[Mike Blackman]

OK, Then my second event should work fine; either place the body of your other macro where the MyMacro is or simply type the name of the other macro to call it.

 

[lenze]

No need for a seperate macro

Private Sub WorkSheet_Change(byVal Target as Range)
If Target.hasFormula Then
     Applications. EnableEvents = False
     MsgBox. "You may not change this cell"
     Application.Undo
     Application.EnableEvents = True
End If
End Sub

untested

lenze

 

[Mldeuser]

Private Sub Worksheet_Change(ByVal Target As Range)
If HasFormula(Target) Then
Sub UpdatingFormulas()
End If
End Sub

When I run this macro I get a compile error "Sub or Function not defined"

It highlights "hasformula" with a blue bar

When I click on ok "Private Sub Worksheet_Change(ByVal Target As Range)" is highlighted in yellow.

Is there something I am doing wrong

Thanks

Mark

 

[Mldeuser]

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

When I use this macro I get a Compile Error "Syntax Error"
MsgBox. "You may not change this cell" - this is highlighted in blue
Private Sub WorkSheet_Change(ByVal Target As Range) - this is highlighted in yellow

Do I need to make any adjustments

Thanks
Mark