LinkBack URL
About LinkBacks
I have the following code to update a summary page from multiple worksheets, is it possible to have this auto update so that the users don't have to remember to click the button.
Please Login or Register to view this content.
I have the following code to update a summary page from multiple worksheets, is it possible to have this auto update so that the users don't have to remember to click the button.
Please Login or Register to view this content.
Last edited by arlu1201; 04-30-2013 at 01:17 AM.
You can call your Summary macro from any number of Events ... the question is which event to choose.
The most commonly used are WorksheetActivate, Auto_Open, SelectionChange
If you want to update automatically when the workbook opens then try this in a code module
Please Login or Register to view this content.
Elegant Simplicity............. Not Always
Hi,
How would the selection change event work? Ithink if the updates were made as the change occurs or when the workbook is saved would work.
Thanks
Selection_change is triggered when a cell selection is changed.
Worksheet_Change is triggered when a worksheet value is changed (except by formula)
Sub Auto_Open is when the workbook is opened Sub Auto_Close when closed
Workbook_beforesaveas - as part of the save routine.
I would like to use the Worksheet Change would I go about the same as the code that you provided for Auto_open above?
Yes everything Except the Sub/End Sub lines should be inside the Worksheet_Change event
Got to the worksheet module module
Select Worksheet in the top left drop down
Select "Change" in the right hand dropdown
Put the code above between the sub and end sub of worksheet_change
Running into a error at the Range("SumTC").Select code
That's probably because it's selecting multiple cells which is then triggering the error in the worksheet_change (being multicell)..
Try putting this in the code
Please Login or Register to view this content.
Last edited by AndyLitch; 04-29-2013 at 12:59 PM.
Hi, shymac,
Your opening post does not comply with Rule 3 of our Forum RULES. Use code tags around code.
Posting code in [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.
Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here
Ciao,
Holger
Use Code-Tags for showing your code: [code] Your Code here [/code]
Please mark your question Solved if there has been offered a solution that works fine for you
Hi,
Added:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Rows > 1 Or Target.Columns > 1 Then Exit Sub
And it error at the same line? Adding workbook added to the last two worksheets before the Summary tab.
Last edited by shymac; 04-29-2013 at 02:47 PM.
So I updated the code and excluded everything related to formatting in the change event and changed to selectionchange. Is there anyway to improve performance there is a 5-10 sec delay when a field is selected?
Try this
Please Login or Register to view this content.
shymac,
You were asked to put code tags in post 9.
I have added code tags to your 1st post. As per forum rule 3, you need to use them whenever you put any code in your post. Please add them in future. In order to put code tags, either type [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] at the end of it, OR you can highlight your code and click the # icon at the top of your post window.
If I have helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
Added to the existing selectionchnge code the screenupdate = false was already there, but the Application.screenupdating = true line caused a longer delay as it toggled the summary and template worksheets back and forth as it ran through the code. Added each line and it didn't make a difference in the performance the lag was still there on each click to a new cell.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks