Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / New Users / June 2006

Tip: Looking for answers? Try searching our database.

Cell value change runs a macro?

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Steach91 - 18 Jun 2006 18:52 GMT
Is there a way to make it so that if a cell value changes, a macro is
called?

Signature

Steach91

Nick Hodge - 18 Jun 2006 19:12 GMT
Sure

You use a Worksheet_Change() event. To access this press alt+f11 to access
the VBE, right click on the worksheet concerned and select view code... In
the left drop-down in the resulting window select worksheet and in the right
one select Change.  You will get a template as below.  Now the rub... you
have to be able to write VBA code to do anything with it. (Obviously you
could record some and cut and paste it between the Private Sub...End Sub
lines, but eventually you will need to hand write some for sure)

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

Signature

HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS

> Is there a way to make it so that if a cell value changes, a macro is
> called?
L. Howard Kittle - 18 Jun 2006 19:16 GMT
From Worksheet Object Events help.
Example
This example changes the color of changed cells to blue.

Private Sub Worksheet_Change(ByVal Target as Range)
   Target.Font.ColorIndex = 5
End SubHTH
Regards,
Howard

> Is there a way to make it so that if a cell value changes, a macro is
> called?
Steach91 - 18 Jun 2006 19:29 GMT
Thanks for the prompt replies... As seems to always be the case, I
probably made a broad question too broad..

I'm looking for a way to run a macro if cell (L16)'s value changes. Not
the whole worksheet.

Again, thanks for the help!

Signature

Steach91

Arvi Laanemets - 18 Jun 2006 20:10 GMT
Hi

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.Column = 12 And Target.Row = 16 Then
       ' Add your code here
       MsgBox "There was a change in cell L16"
   End If
End Sub

Arvi Laanemets

> Thanks for the prompt replies... As seems to always be the case, I
> probably made a broad question too broad..
[quoted text clipped - 9 lines]
> Steach91's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30234
> View this thread: http://www.excelforum.com/showthread.php?threadid=553053
Steach91 - 18 Jun 2006 20:18 GMT
Thank you very much for the help
Nick Hodge - 18 Jun 2006 20:15 GMT
Worksheet_Change monitors all cells, you just need to tell it which one,
like this

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("L16")) Is Nothing Then
   OtherMacro
End If
End Sub

This will run a macro called OtherMacro if cell L16 is changed.

Now you need to beware if the macro makes other changes to the worksheet or
else you code will be called each time it is.  You can prevent this by
adding

Application.EnableEvents=False

but remember to turn them back on at any place where code can exit
(including errors) or you will be left with no events at all in Excel

Signature

HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS

> Thanks for the prompt replies... As seems to always be the case, I
> probably made a broad question too broad..
[quoted text clipped - 3 lines]
>
> Again, thanks for the help!
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.