Ask Your Question
0

event driven macro triggered "on cell change" WITHOUT a listener [closed]

asked 2013-06-19 20:32:41 +0100

mcanedo gravatar image

I want to make an event driven macro triggered "on cell change" WITHOUT a listener.

I know is possible to assign a macro to a sheet specific event via: sheet tab ->left click->sheet events (mine is in spanish so text might be different) So I guess I can wrap the whole macro in an IF checking if the modified cell is the one I want to monitor (e.g. "A:1").

Is there a variable I can use as an argument to check which cell is the one that has been modified???

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-02-20 19:11:20.959709

2 Answers

Sort by » oldest newest most voted
2

answered 2013-06-20 05:53:49 +0100

mcanedo gravatar image

updated 2013-06-20 05:54:14 +0100

Got it:

Must declare sub with oEvent argument, and we can evaluate it like this:

Sub cell_onChange (oEvent As Variant)
    'Macro responds to changes in B1 only
If oEvent.AbsoluteName <> "$SheetName.$B$1" Then Exit Sub 
End Sub
edit flag offensive delete link more

Comments

How do I load this macro? I have added this routine with a msgbox "Test" after the If.. to Standard->Module1 but it does not seem to work when I change B1. Thanks

zml gravatar imagezml ( 2015-04-09 18:41:35 +0100 )edit
-1

answered 2014-04-09 12:02:59 +0100

Ana gravatar image

Hi @mcanedo

I'm testing your code, but the macro runs everytime I modify a cell in the sheet no matters if it is B1 or not. I would like MyMacro to run only when ONE cell is modified...for example B1. Something like:

Sub cell_onChange (oEvent As Variant) 'Macro responds to changes in B1 only If oEvent.AbsoluteName <> "$SheetName.$B$1" Then MyMacro End Sub

But this approach run MyMacro everytime I modified any other cell on the sheet. Any ideas?

Thanks in advanced!

edit flag offensive delete link more

Question Tools

Stats

Asked: 2013-06-19 20:32:41 +0100

Seen: 6,826 times

Last updated: Apr 09 '14