+ Reply to Thread
Results 1 to 14 of 14

Auto Update vs Button Update

  1. #1
    Registered User
    Join Date
    01-14-2013
    Location
    Ohio
    MS-Off Ver
    Excel 2010
    Posts
    14

    Auto Update vs Button Update

    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.

  2. #2
    Valued Forum Contributor
    Join Date
    03-29-2013
    Location
    United Kingdom
    MS-Off Ver
    Office/Excel 2013
    Posts
    1,749

    Re: Auto Update vs Button Update

    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

  3. #3
    Registered User
    Join Date
    01-14-2013
    Location
    Ohio
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: Auto Update vs Button Update

    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

  4. #4
    Valued Forum Contributor
    Join Date
    03-29-2013
    Location
    United Kingdom
    MS-Off Ver
    Office/Excel 2013
    Posts
    1,749

    Re: Auto Update vs Button Update

    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.

  5. #5
    Registered User
    Join Date
    01-14-2013
    Location
    Ohio
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: Auto Update vs Button Update

    I would like to use the Worksheet Change would I go about the same as the code that you provided for Auto_open above?

  6. #6
    Valued Forum Contributor
    Join Date
    03-29-2013
    Location
    United Kingdom
    MS-Off Ver
    Office/Excel 2013
    Posts
    1,749

    Re: Auto Update vs Button Update

    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

  7. #7
    Registered User
    Join Date
    01-14-2013
    Location
    Ohio
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: Auto Update vs Button Update

    Running into a error at the Range("SumTC").Select code

  8. #8
    Valued Forum Contributor
    Join Date
    03-29-2013
    Location
    United Kingdom
    MS-Off Ver
    Office/Excel 2013
    Posts
    1,749

    Re: Auto Update vs Button Update

    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.

  9. #9
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    2013 on Win10 (desktop), 2007 on Win10 (notebook)
    Posts
    8,098

    Re: Auto Update vs Button Update

    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

  10. #10
    Registered User
    Join Date
    01-14-2013
    Location
    Ohio
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: Auto Update vs Button Update

    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.
    Attached Files Attached Files
    Last edited by shymac; 04-29-2013 at 02:47 PM.

  11. #11
    Registered User
    Join Date
    01-14-2013
    Location
    Ohio
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: Auto Update vs Button Update

    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?

  12. #12
    Valued Forum Contributor
    Join Date
    03-29-2013
    Location
    United Kingdom
    MS-Off Ver
    Office/Excel 2013
    Posts
    1,749

    Re: Auto Update vs Button Update

    Try this
    Please Login or Register  to view this content.

  13. #13
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,162

    Re: Auto Update vs Button Update

    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]

  14. #14
    Registered User
    Join Date
    01-14-2013
    Location
    Ohio
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: Auto Update vs Button Update

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1