Run macro on cell edit

[Guest]

I need to make a macro run whenever cell F4 (strictly speaking the merged
cells F4:F6) on the current worksheet is edited. I'm sure this must be
possible but can't find anything in the help files or online to tell me how...

Any ideas?

Thanks

Phil

 

[NickHK]

Phil,
Macros cannot run when a cell is being edited.
What are trying to achieve ?

NickHK

 

[Guest]

I have a customer spreadsheet which brings up various things depending on the
customer account number entered by the user (in cell F4.) Some of the things
I need it to bring up depend on a macro being run based on the data in that
cell. It doesn't need to run the macro while the cell is being edited but
when the Enter key is pressed. I can do it by using a button to click on but
would be better to simply do it when cell F4 is updated.

Phil

 

[Guest]

Nick

May well be... but I'm fairly new to all this malarkey and don't really
understand what that is!! Would you be able to give me an example? If it
makes a difference, there are other cells on the worksheet which can be
edited by the user but I don't want the macro to run when they are edited.

Thanks for your help

Phil

 

[NickHK]

Phil,
The Worksheet_Change event perhaps ?

NickHK

 

[Mike Fogleman]

Right click on the worksheet tab and select View Code. At the top of the
right half of the edit pane click the down arrow beside (General) and select
Worksheet. Paste this code in the edit window:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Application.Intersect(Range("F4"), Target) Is Nothing Then
Call Your Macro
End If
End Sub

The macro you want to run should be in a general code module, not this one.

Mike F

 

[Guest]

Getting closer! That runs the macro as soon as the cell is highlighted, not
after it's edited, is there any way to do it after the cell is edited?

Thanks

Phil

 

[Guest]

Instead of:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Use:
Private Sub Worksheet_Change(ByVal Target As Range)

 

[Tom Ogilvy]

Use the Change Event rather than the SelectionChange event.

 

[Guest]

Fabulous, that's got it! Many thanks Nick, Mike & David

Phil

 

[Mike Fogleman]

Oops, your right. I didn't test it. Neither is this but try:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim CurrentValue
CurrentValue = Range("F4").Value

If Application.Intersect(Range("F4"), Target) .Value <> CurrentValue
Then
Call Your Macro
End If
End Sub

Mike F