Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    Darmstadt, Hessen, Germany
    Posts
    112
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Sheet Change Event doesn't fire

    Dear All,

    I import stock exchange data into an Excel 2007 spreadsheet which auto-updates every minute.
    I tried to catch possible changes by the Sheet Change Event but that does not work with web linked...

    That means it does not invoke said Event upon receipt of the data by internet but if I change the value manually it will.
    Could it be that the Sheet Change Event does not recognize Web imported data?

    Please advise, thank you

    Best regards

    Wolfgang

  2. #2
    4 Star Lounger
    Join Date
    Jan 2004
    Location
    Wiltshire, UK
    Posts
    536
    Thanks
    1
    Thanked 46 Times in 44 Posts
    The Change Event does not capture everything. In particular it does not capture changes that are the result of a re-calculation.

    It is possible that the underlying Change Event, as Excel knows it, is actually taking place in another Worksheet and simply causing change by recalculation on the Worksheet where you have the code? You could check that by putting code into the ThisWorkbook module instead. Of course when you type the data, you are forcing the Event to take place in the Worksheet where you are typing.

    I have grappled with this myself on several occasions, sometimes having to resort to the clumsy technique of putting the new data alongside the old, comparing the two (and taking any action I want if there are changes) then making the new data the old data . . . etc.
    Last edited by MartinM; 2013-01-04 at 11:00.

  3. The Following User Says Thank You to MartinM For This Useful Post:

    wolfgang (2013-01-04)

  4. #3
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    Darmstadt, Hessen, Germany
    Posts
    112
    Thanks
    12
    Thanked 0 Times in 0 Posts
    Hi Martin,

    thank you very much for your reply.
    It rings a bell in my memory and I recall a similar statement many years ago.

    I use exactly the same approach that you described and YES, I do a Private Sub Worksheet_Change(ByVal Target As Range).

    Thanks again and have a nice weekend,

    Wolfgang

  5. #4
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,294
    Thanks
    47
    Thanked 257 Times in 237 Posts

    This worked

    Duplicated from the web site: http://www.ozgrid.com/forum/showthread.php?t=57855
    HTH,
    Maud

    Place in a new class module named Class1
    Code:
    Public WithEvents qt As QueryTable    
    
    Private Sub qt_AfterRefresh(ByVal Success As Boolean) 
        MsgBox ("I just refreshed") 
    End Sub
    Then in a regular module insert the following code
    Code:
    
    Dim MyQuery As New Class1
    
    Sub Initialize_It()
          Set MyQuery.qt = ThisWorkbook.Worksheets("Sheet1").QueryTables(1) 
    End Sub
    This assumes your query is the first in that sheet. If not use the name.

    Then run Initialize_It and try refreshing the web query.

  6. #5
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    Darmstadt, Hessen, Germany
    Posts
    112
    Thanks
    12
    Thanked 0 Times in 0 Posts
    Hi Maudibe,

    thank you for your advise but it does not work for me because I use DATA - FROM WEB - Connection rather than an SQL-query.

    Best regards,
    Wolfgang

  7. #6
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,294
    Thanks
    47
    Thanked 257 Times in 237 Posts
    Wolfgang. It still works for me fine using data from the web. The web data updates. Did you run the Sub Initialize_It() subroutine to start it going? Attached is a bare bones workbook demonstrating the the code only. Click on the Start button between updates as it will run the Initialize_it and the code should run. You should see the web update occur (every minute) then the msgbox appears (the code) indicating that the update has just occurred. Replace the one line of code that opens the msgbox with what you need. I have also tried placing the Initialize_It routine in the Workbook_Open event subroutine (replacing the start button) and the code automatically ran after the first 1 minute update
    HTH,
    Maud

    After first update.....
    Update1.jpg

    After Initialize_it() by pressing start button
    Update2.jpg
    Attached Files Attached Files

  8. #7
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    Darmstadt, Hessen, Germany
    Posts
    112
    Thanks
    12
    Thanked 0 Times in 0 Posts
    Hi Maudibe,

    thanks again for your valuable input.
    Sure enough your code runs fine but I can't test it with my stocks monitor as all stock exchanges are still closed and I won't be able to do so before Monday.
    I'm looking forward to check it out tomorrow.

    Best regards,
    Wolfgang

  9. #8
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    Darmstadt, Hessen, Germany
    Posts
    112
    Thanks
    12
    Thanked 0 Times in 0 Posts
    Hi Maudibe,

    there seems to be a BIG misunderstanding caused my ME so please disregard my previous reply and bear with me - I give it another try now:


    The problem is NOT the web-refresh algorithm but the processing of the imported stock data on another workbook. I copy that data over to sheet1 and link it. On sheet2 I tried to catch any changes by means of the Worksheet Change Event and some code that I got from the OZGRID site and that is not working. Sheet1 updates alright but sheet2 remains silent. So, the Worksheet Change Event doesn't trigger and the code doesn't activate.


    I only hope that I managed to express myself clearer now...


    Best regards,
    Wolfgang


  10. #9
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,294
    Thanks
    47
    Thanked 257 Times in 237 Posts
    Wolfgang,
    Thank you for clarifying. I duplicated the scenario and I see your dilemna. have some ideas on a workaround

    Maud

  11. #10
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,294
    Thanks
    47
    Thanked 257 Times in 237 Posts

    Workaround

    Wolfgang,
    Took a different approach and it works nicely. The workflow: The main workbook, StockMarket, has a worksheet, Sheet1, that is set to capture web data and update every 1 minute. Using the code as described above in the same workbook, the Sub qt_AfterRefresh runs its code after the first 1 minute update. But instead of displaying a message box (above sample code), its code calls a new routine called "moveover". Moverover runs its code by opening a second workbook called Ledger. Now here is the twist... Instead of linking the 2 workbooks which does not fire the Worksheet_Change event in Ledger, moveover will do a copy and paste of the range of data which DOES invoke the event. The range of updated web data is copied over, the Worksheet_Change event subroutine is fired and its code is executed.

    I have placed the Initialize_It subroutine in the workbook_open event subroutine for StockMarket so everything happens automatically. Ledger does not have to be open as the code will open the workbook. Substitute your workbook names, code. path names, and ranges. Files must be dowloaded and saved first with correct paths entered

    HTH,
    Maud

    Code:
    'PLACED IN A STANDARD MODULE
    Public Sub moveover()
        Dim sDirPath As String
        
        sDirPath = "C:\Users\Maudibe\Documents\"     'YOUR PATH GOES HERE AND MUST END IN \
    
        Workbooks.Open sDirPath & "Ledger.xlsm"   'CHANGE LEDGER TO SecondFileName.xlsm (macro enabled for version 2010)
        Windows("StockMarket.xlsm").Activate    'CHANGE STOCKMARKET TO MainFileName.xlsm (macro enabled for version 2010)
        Range("A1:B7").Select         'ADJUST FOR THE RANGE OF INPUTTED WEB DATA
        Selection.Copy
        Windows("Ledger.xlsm").Activate     'CHANGE NAME
        Sheets("Sheet1").Select            'CHANGE SHEET NAME IF NEEDED  
        ActiveSheet.Range("D3:C9").Select   'RECEIVING RANGE OF SAME SIZE
        ActiveSheet.Paste
    End Sub
    Attached Files Attached Files
    Last edited by Maudibe; 2013-01-06 at 15:58.

  12. #11
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    Darmstadt, Hessen, Germany
    Posts
    112
    Thanks
    12
    Thanked 0 Times in 0 Posts
    Good Morning Maude,


    I think that you came up with an excellent idea as soon as I get it to work!!!


    So far, I came up with an Error message saying:
    Compile Error - Invalid Outside Procedure
    When it comes to VBA I am a complete NoNo so I attach my workbook to make things more transparent for you. By the way, I am using Excel 2007 US-Version.


    The working path is F:\2013\TOPS


    Once you load the file you are taken to Sheet "STO" which is the main monitor sheet with the data in question range from K1 to N9.


    Sheet "ST" is the Web-Data Import sheet and works as follows:


    Click Data – Refresh All – Connection Properties – Definition – Edit Query and you will be taken to the Web Main Data page.


    This page asks for the User Name (Benutzername) and Password (Passwort.


    User Name is "alienware" – Password is "maxernst" and click "Login" - "Import" and OK.


    Select sheet STO again and click the button left of "REFRESH".


    Now, the whole page is filled with actual data and that is it. All I need to monitor externally is the range K1 to N9.


    Maybe I was able to make myself understood this time I sure hope so.


    Many "Thank Yous" for your kind help and patience so far…


    Best regards,
    Wolfgang
    Attached Files Attached Files

  13. #12
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,294
    Thanks
    47
    Thanked 257 Times in 237 Posts
    Wolfgang,
    Took a look at your spreadsheet and it is pretty intense. Received an error message upon opening that an object was unable to take the focus. Continued with your instructions but would not accept the login.

    Assuming that STO does get updated, is this when and where you are looking for the Worksheet_Change event to occur? There is no code located in the sheet's module. Briefly, what do you want to occur?

    Can you elaborate on what you mean by "All I need to monitor externally is the range K1 to N9"? Do you want that data sent to another workbook?

    Thanks,
    Maud

  14. #13
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    Darmstadt, Hessen, Germany
    Posts
    112
    Thanks
    12
    Thanked 0 Times in 0 Posts
    Sorry Maude for causing so much trouble.

    Yes, STO gets updated alright and all I want is to reflect any changes in the range K1 to N9 at another workbook.
    That is to say, on one sheet will be the current values and on the other one any changes being made like a comparison.
    I had some code for this but the Worksheet_Change event did not fire - if I manually changed any data then it would.

    Best regards,
    Wolfgang

  15. #14
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,294
    Thanks
    47
    Thanked 257 Times in 237 Posts
    Wolfgang,
    No trouble at all. Can you briefly describe what the code that you want to insert does? Also, do you want to place the code on the Worksheet_Change event subroutine of the main workbook (STO) or of the receiving sheet of the external workbook? Either way, I will set it up for you so it fires.

    YF,
    Maud

  16. #15
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    Darmstadt, Hessen, Germany
    Posts
    112
    Thanks
    12
    Thanked 0 Times in 0 Posts
    Hi Maude,

    looks like I keep you pretty busy with my stock-watching-things..

    I attach the workbook that I expected to do want I wanted but no such luck yet.

    It takes its input from the STO range and should give me the results on the CHANGES-sheet.

    Have fun and all the best...

    Wolfgang
    Attached Files Attached Files

Page 1 of 2 12 LastLast

Posting Permissions

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