 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / April 2008cell update doesn't run macro
I've searched thru lots of posts, and I can't understand why only I seem to have an issue with starting a macro based on a cell change. This code: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "B3" Then Call volatility_rate End If End Sub -doesn't do squat, whether I change the cell thru the drop-down or manually. Why not? I have this coded in the worksheet I want it for, and nothing happens. Thanks, -Dave Are events enabled. Type Application.EnableEvents = true in the immediate window  Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > I've searched thru lots of posts, and I can't understand why only I seem > to [quoted text clipped - 11 lines] > Thanks, > -Dave You need dollars: Private Sub Worksheet_Change(ByVal Target As Range) MsgBox (Target.Address) If Target.Address = "$B$3" Then Call volatility_rate End If End Sub SignatureGary''s Student - gsnu200775 > I've searched thru lots of posts, and I can't understand why only I seem to > have an issue with starting a macro based on a cell change. This code: [quoted text clipped - 9 lines] > Thanks, > -Dave Nope; I tried both suggestions. Gary's just opens up a msg box, which I didn't need (just using the dollar signs didn't do the trick either), and Bob's events code didn't work. I tried putting it in both the worksheet and module(macro) code. > You need dollars: > [quoted text clipped - 18 lines] > > Thanks, > > -Dave After fiddling around some more, I see that Gary's dollar actually were the problem, but the macro only runs if I manually change the cell. How can I get it to work thru updating with the drop-down I have there? Thanks, guys > Nope; I tried both suggestions. Gary's just opens up a msg box, which I > didn't need (just using the dollar signs didn't do the trick either), and [quoted text clipped - 23 lines] > > > Thanks, > > > -Dave It SHOULD work with a data validation drop-down. Maybe the problem is in the called routine. Just for fun try: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$B$3" Then Application.EnableEvents = False Call volatility_rate Application.EnableEvents = True End If End Sub SignatureGary''s Student - gsnu200775 > After fiddling around some more, I see that Gary's dollar actually were the > problem, but the macro only runs if I manually change the cell. How can I get [quoted text clipped - 29 lines] > > > > Thanks, > > > > -Dave Turns out it wasn't just for fun :( Not only didn't it help, but it no longer works, even after I went back to the previous code (w/o the events code). I tried Application.EnableEvents as true and false, before and after the code, but I can't get even manual changes to call the macro, like they were doing before. What's going on? > It SHOULD work with a data validation drop-down. Maybe the problem is in the > called routine. Just for fun try: [quoted text clipped - 40 lines] > > > > > Thanks, > > > > > -Dave Maybe your code exited before events were turned back on. Execute this line... Application.EnableEvents = True in the Immediate window and see if that returns your code to (not) working the way it used to (not) work. Rick > Turns out it wasn't just for fun :( Not only didn't it help, but it no > longer works, even after I went back to the previous code (w/o the events [quoted text clipped - 57 lines] >> > > > > Thanks, >> > > > > -Dave Have a cell linked to the DV cell, =G1 for example, and then use the Worksheet_Calculate event to trap the change. Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > After fiddling around some more, I see that Gary's dollar actually were > the [quoted text clipped - 36 lines] >> > > Thanks, >> > > -Dave I am having a similar problem. When I step into the macro I am trying to run (subroutine) the value of Target is not a bunch of cells hence a range object, but the actual value that has just been entered into the range. Earlier today, however, it was sending the range value. I would have expected that this event would always get the range object, not the value. Any insights? Hi, I'm having the same problem, but only with Excel 2003 (at work). At home I use Excel 2000 and on this earlier version the Worksheet_Change code works as expected after a Validation drop down event. Regards, Bailsoft I run Excel 2003 and this code works every time for me when I select an item from the DV dropdown list. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$B$3" Then Application.EnableEvents = False Call My_Macro Application.EnableEvents = True End If End Sub Post your exact code and is B3 the DV dropdown cell? Gord Dibben MS Excel MVP >Hi, >I'm having the same problem, but only with Excel 2003 (at work). At home [quoted text clipped - 4 lines] > >*** Sent via Developersdex http://www.developersdex.com *** It would, but Value is the range default property, so if there is only one cell it will show that cell value. Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > I am having a similar problem. When I step into the macro I am trying > to run (subroutine) the value of Target is not a bunch of cells hence a [quoted text clipped - 4 lines] > > *** Sent via Developersdex http://www.developersdex.com *** |
Reply to this Message |
 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.