+ Reply to Thread
Results 1 to 10 of 10

Click yes on an Excel dialog box

  1. #1
    Registered User
    Join Date
    06-29-2022
    Location
    Canada
    MS-Off Ver
    365
    Posts
    5

    Click yes on an Excel dialog box

    Hi,

    I'm using a commercially produced macro (RunDataInterpolateSheet) to pull data from a database. I have no ability to modify this macro. When run, this macro generates a dialog box:

    dialog.JPG

    I want to run this macro for a number of different columns. I wrote this short script to do that:

    Please Login or Register  to view this content.

    I need my macro to click the yes button produced by the RunDataInterpolateSheet macro. I have unsuccessfully tried "Application.DisplayAlerts = False" and "Application.SendKeys "y", True". Does anyone know how I can do this?

    Thanks,
    Mark
    Last edited by xkyml; 06-29-2022 at 11:37 AM.

  2. #2
    Valued Forum Contributor
    Join Date
    10-12-2021
    Location
    CT
    MS-Off Ver
    365
    Posts
    462

    Re: Click yes on an Excel dialog box

    Hey Mark - I have a potential solution - what I think is happening is that once your system runs the macro "ThisWorkbook.RunDataInterpolateSheet", it's setting the application.DisplayAlerts = True. Try putting application.screenupdating = False right before that line and set it back to true right after that line. Let me know if that works.

  3. #3
    Registered User
    Join Date
    06-29-2022
    Location
    Canada
    MS-Off Ver
    365
    Posts
    5

    Re: Click yes on an Excel dialog box

    Thank you for the suggestion, it is greatly appreciated. I tried the following code, but the dialog box still appeared, and required me to click the button. I'd love to hear any other ideas you may have.

    Please Login or Register  to view this content.
    Last edited by xkyml; 06-29-2022 at 01:15 PM.

  4. #4
    Valued Forum Contributor
    Join Date
    10-12-2021
    Location
    CT
    MS-Off Ver
    365
    Posts
    462

    Re: Click yes on an Excel dialog box

    The problem is running a macro with application.run returns exactly what that macro is programmed to do...
    Here is my last ditch effort:
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    06-29-2022
    Location
    Canada
    MS-Off Ver
    365
    Posts
    5

    Re: Click yes on an Excel dialog box

    Unfortunately, this didn't change the result (dialog box still pops up). Thanks again for the suggestion!

  6. #6
    Valued Forum Contributor
    Join Date
    10-12-2021
    Location
    CT
    MS-Off Ver
    365
    Posts
    462

    Re: Click yes on an Excel dialog box

    danggg... how about this:

    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    06-29-2022
    Location
    Canada
    MS-Off Ver
    365
    Posts
    5

    Re: Click yes on an Excel dialog box

    This worked. The dialog box appears for the first column, and I need to click the yes button. But then for subsequent columns, the dialog box does not seem to popup, but the macro does work as if the yes button had been clicked. This is certainly a workable solution (I have no problem clicking the button once). But out of interest, do you know why I need to click one of the dialog boxes before the macro handles the rest?

    As mentioned in my original post, I had tried "Application.SendKeys "y", True". I am surprised that "{ENTER}" worked where "y" did not. When running the 3rd party macro manually, I can press either y or enter on the keyboard to progress.

  8. #8
    Valued Forum Contributor
    Join Date
    10-12-2021
    Location
    CT
    MS-Off Ver
    365
    Posts
    462

    Re: Click yes on an Excel dialog box

    Glad it's working!
    I thought enter would work because the option "Yes" is highlighted by default... maybe you needed to have "{y}" but I am really not sure.

    I couldn't begin to guess why it doesn't automatically do the first one... maybe program it to delay the first enter with something like....

    Please Login or Register  to view this content.
    before the application.sendkeys line. just a hunch

  9. #9
    Registered User
    Join Date
    06-29-2022
    Location
    Canada
    MS-Off Ver
    365
    Posts
    5

    Re: Click yes on an Excel dialog box

    I tried the time delay and a few other things, but could avoid clicking the yes button the first time. The one limitation of the sendkeys solution is that it stops working if my computer locks due to inactivity. This means that I can not run this macro overnight. It's not a perfect solution, but it is a solution.

    Thanks for your help.

  10. #10
    Forum Contributor
    Join Date
    02-25-2022
    Location
    Dallas, Texas
    MS-Off Ver
    Office 365
    Posts
    174

    Re: Click yes on an Excel dialog box

    When I have a problem I look at using AutoIT (free). You can create a .exe that pushes the button and call it from VBA. It is very powerful.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Image Placeholder (Dialog Box upon click)
    By olga6542 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-21-2020, 03:37 AM
  2. How cal i populate the Dialog Box when the Right Click mouse Button
    By karthisucc9 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-05-2012, 08:54 AM
  3. When i click the Hyper link The Dialog Box should populate
    By karthisucc9 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-12-2012, 10:16 AM
  4. Replies: 3
    Last Post: 02-01-2010, 07:02 PM
  5. [SOLVED] 'Unprotect' dialog box appears on every click
    By danmahoney in forum Excel General
    Replies: 0
    Last Post: 02-15-2006, 12:10 PM
  6. [SOLVED] Right click dialog
    By Adam in forum Excel General
    Replies: 3
    Last Post: 08-09-2005, 07:05 PM
  7. how to capture button click from file dialog
    By chawla12amit in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-13-2005, 06:02 PM

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