 |
 | 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 / New Users / July 2007Update macro on start up
I have a macro with the following code range("h5").value=application.sum(range("a2:f3")) If a value in the range A2:F3 is cahnged I need to run the macro again so as to change the value in cell H5. Is there something like "automatic update"??? so that I don't need to run the macro whenever a value is changed. Using a macro seems like overkill to me. I'd just put =sum(a2:f3) in H5 > I have a macro with the following code > range("h5").value=application.sum(range("a2:f3")) > If a value in the range A2:F3 is cahnged I need to run the macro again so as > to change the value in cell H5. > Is there something like "automatic update"??? so that I don't need to run > the macro whenever a value is changed.  SignatureDave Peterson Hi, The idea originaly is to have the value in cell H5 being there without seeing the original formula and avoiding also to protect the sheets. I hope that this made it clear why to use VBA Code in the first place instead of just having the formla in H5 which is simpler. Khalil > Using a macro seems like overkill to me. > [quoted text clipped - 9 lines] >> Is there something like "automatic update"??? so that I don't need to run >> the macro whenever a value is changed. Right click on the worksheet tab that should have this behavior. Select view code. Paste this into the code window: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) 'one cell at a time only! If Target.Cells.Count > 1 Then Exit Sub If Intersect(Target, Me.Range("a2:f3")) Is Nothing Then Exit Sub On Error GoTo ErrHandler: Application.EnableEvents = False Me.Range("h5").Value = Application.Sum(Me.Range("a2:f3")) ErrHandler: Application.EnableEvents = True End Sub Notice that if you use this instead of a simple formula, you'll see that edit|undo is killed. > Hi, > The idea originaly is to have the value in cell H5 being there [quoted text clipped - 21 lines] > > > > Dave Peterson SignatureDave Peterson It worked fine. Thanks a lot. May sheet containes more than one cell that has different formulas and needs to have the same treatment. Can this be done?? and How?? maybe 2 formulas and I can follow the same for the rest! Khalil > Right click on the worksheet tab that should have this behavior. Select > view [quoted text clipped - 47 lines] >> > >> > Dave Peterson It can be done by modifying the code--not duplicating the procedure. Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) 'one cell at a time only! If Target.Cells.Count > 1 Then Exit Sub On Error GoTo ErrHandler: Application.EnableEvents = False If Not Intersect(Target, Me.Range("a2:f3")) Is Nothing Then Me.Range("h5").Value = Application.Sum(Me.Range("a2:f3")) ElseIf Not Intersect(Target, Me.Range("x3:y6")) Is Nothing Then Me.Range("q5").Value = Application.Sum(Me.Range("x3:y6")) End If ErrHandler: Application.EnableEvents = True End Sub > It worked fine. Thanks a lot. > May sheet containes more than one cell that has different formulas and needs [quoted text clipped - 59 lines] > > > > Dave Peterson SignatureDave Peterson Hi, It is clear for the sum formula's. in a case like this formula where no range is there: =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255) that is in cell B5, what should be instead of the range part in: > ElseIf Not Intersect(Target, Me.Range("x3:y6")) Is Nothing Then and the second line (assuming the need of 2 line for each as i concluded ) > Me.Range("q5").Value = Application.Sum(Me.Range("x3:y6")) will be: Me.Range("B5").Value= "=Mid(CELL(""filename"", A1), Find(""]"", CELL(""filename"", A1)) + 1, 255)" I'am not sure if this is right!!! I also understand that another elseIF statment should be added for each value needed. The "Range" part confuses me in the case were no Sum or Average is there! Khalil > It can be done by modifying the code--not duplicating the procedure. > [quoted text clipped - 84 lines] >> > >> > Dave Peterson This formula doesn't depend on what's in A1. It uses A1 as a reference for what sheet name to return. And since this formula returns the name of the worksheet, you could use: me.range("A1").value = me.name Maybe you can use the worksheet_calculate event to do this -- but I wouldn't. I'd use that formula. In fact, I wouldn't replace the other formulas with code either -- but I've said that already. > Hi, > It is clear for the sum formula's. in a case like this formula where no [quoted text clipped - 107 lines] > > > > Dave Peterson SignatureDave Peterson
|
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.