Results 1 to 15 of 15
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    React on external data dialog response (Excel 2003)

    I have a workbook that is linked to an external data source. The source is a SQL database that contains a password. When the WB is opened the password dialog prompts. After the password is typed, the WB list is refreshed and a msgbox pops up to say the list is refreshed. This msgbox is part of the Workbook_Open event macro that runs to clean and format the file at opening.

    My problem is that if the person clicks the cancel button, the macro still runs the msgbox to say that the list is updated and refreshed and that filtering can continue. I need to find out if the person clicked the cancel button. How do I check for this to allow me to branch the response off in a decision structure....if the cancel button on the pass dialog is clicked, msgbox = data not refreshed, else msgbox = data refreshed!

    Tx
    Regards,
    Rudi

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: React on external data dialog response (Excel 2003)

    I assume that the user knows whether (s)he clicked Cancel or not, so it doesn't seem necessary for you to refer to it...

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: React on external data dialog response (Excel 2003)

    Tx for your response, but it is necessary for me to get the users response! I need to capture the response of the user for use in code. To capture a VBA msgbox Yes / No button is easy, but how do I capture the response of the user in this dialog that is not generated by VBA?

    Tx
    Regards,
    Rudi

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: React on external data dialog response (Excel 2003)

    No idea.

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: React on external data dialog response (Excel 2003)

    <img src=/S/yikes.gif border=0 alt=yikes width=15 height=15> - <img src=/S/drop.gif border=0 alt=drop width=23 height=23> - <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    Tx hans
    Regards,
    Rudi

  6. #6
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: React on external data dialog response (Excel 2003)

    Hans,

    I found this code that uses VBA to interact with an Oricle DB....
    See: VBA Code

    Is there anything in here that we can pirate to help to collect the response from the user on the SQL password dialog???

    Tx
    Regards,
    Rudi

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: React on external data dialog response (Excel 2003)

    I don't think so.

  8. #8
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: React on external data dialog response (Excel 2003)

    OK....just checking...
    Regards,
    Rudi

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: React on external data dialog response (Excel 2003)

    <hr>
    I assume that the user knows whether (s)he clicked Cancel or not
    <hr>

    Boy do I wish I had your users before I retired! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Legare Coleman

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: React on external data dialog response (Excel 2003)

    <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>

  11. #11
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: React on external data dialog response (Excel 2003)

    Is this list generated through data, get external data, new database query?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  12. #12
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: React on external data dialog response (Excel 2003)

    Yes...do you have any ideas???

    [waiting in anticipation!]
    Regards,
    Rudi

  13. #13
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: React on external data dialog response (Excel 2003)

    I am sorry, not really. All I can think of is to start using code to ask for the username and password and then use ADO to retrieve the data.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  14. #14
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: React on external data dialog response (Excel 2003)

    Hi Rudi,
    If this is a QueryTable, then there is an AfterRefresh method you could use to display the messagebox rather than putting it in the Workbook's Open event. You will need a simple class with a QueryTable variable declared withevents and the relevant code in the AfterRefresh event. THen in the Workbook's Open event you can create a new instance of the class, assign the relevant QueryTable to the class object's QT variable and Bob's your uncle. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    HTH.
    Regards,
    Rory

    Microsoft MVP - Excel

  15. #15
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: React on external data dialog response (Excel 2003)

    Wow....I think I only understood half of that Rory! <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>....

    I can live with what I have now...but if this scenario becomes an issue with the users, then i will need to follow up on this!

    Tx for all your comments guys...its appreciated!
    Regards,
    Rudi

Posting Permissions

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