VBA To Select Yes on Prompt Box

[Gian624]

Hi Everyone,

I'm writing some code to open files in a loop, run a macro and select "Yes" on the prompt box that opens iimmediately after the macro is ran. I'm having trouble automatically selecting yes to continue running the macro. Any ideas?

Thanks All,
Matt

 

[Scott Huish]

You didn't say what the box was but usually you just don't show the box at all.

i.e.
Try this line before the box would come up and then the 2nd line after

Application.DisplayAlerts=False
and
Application.DisplayAlerts=True

 

[Gian624]

Good Morning,

Thanks for your response. However, the prompt box asks if you are sure you would like to continue. If "Yes" is selected, the macro runs. If "No" is selected, the sub is exited.

Matt

 

[Scott Huish]

Where is this prompt box coming from? What does it say? Can you post your code?

 

[Gian624]

I have an excel workbook that I need to open on a loop and get refreshed. This is a sample of the code from the workbook:


Sub Refresh_Report()
Dim msg As String
Dim msgPrompt As String

msg = "Would you like to refresh all the reports?"
msg = msg & vbNewLine & vbNewLine
msg = msg & "Yes - Refresh"
msg = msg & vbNewLine
msg = msg & "No - Cancel"
msg = msg & vbNewLine & vbNewLine & vbNewLine

msgPrompt = MsgBox(msg, vbYesNo, "Refresh Report?")

If xmsgPrompt= vbYes Then
Sheets("First Sheet").Select
Range("A1").Select
Application.Run ("Process")

Else
Exit Sub

End If
End Sub


This sub works great and is not the problem. The problem lies with when I use another macro on another workbook to open all the files in a folder, run the macro, dave and close the workbook and loop to the next one. I have that macro working great... except for the prompt box. When the macro launches (which is does), it stops when the message box opens. I'm trying to have 'yes" automatically selected so it will continue running the macro.

A sample of the code there is:

...
Do While myFile <> ""
Set wb = Workbooks.Open(Filename:=myPath & myFile)

wb.Application.Run ("'" & myFile & "'" & "!" & "Refresh_Report")

wb.Close SaveChanges:=True
myFile = Dir
Loop
...

I have tried putting Application.DisplayAlerts = False before the run part and Application.DisplayAlerts = True after. I have tried using SendKeys "{Enter}" after running the macro. I've tried a whole host of things. The closest I have got is using SendKeys "{Enter}". It did not work for the first file in the folder but it did on the second, not on the third and so on.

Thanks again for your help,
Matt

 

[My Aswer Is This]

If your going to have the script automatically choose "Yes" why even ask the question?
I'm having trouble automatically selecting yes to continue running the macro. Any ideas?

 

[Gian624]

Each workbook is used by an Account Manager who may or may not have experience with what the macro is updating. I left out of the part of the prompt that directs the user to contact the right person if they need the software necessary to update the files. In other words, the message box has a dual purpose. I am in charge of updating 54 workbooks every morning and want to click one button and get a cup of coffee while it runs through them all instead of doing a refresh on three sheets in 54 workbooks.

Thanks again,
Matt

 

[Gian624]

Thanks for your help everyone, I developed a solution.

 

[bp dynamite]

Thanks for your help everyone, I developed a solution.

I'm trying to do the same thing, would you mind sharing your solution? I want to incorporate that into a loop code to run through all the files in a folder too but I'm too inexperienced to do it just yet. If you don't want to share your code could you point me in the direction of a place to figure out a solution? I'm trying to code while I learn on the fly lol

 

[Gian624]

Hi bp,

You can copy the following below into your macro and change the range locations as needed. You may need to study the notes in order to figure out how to modify it. I'm sure it'll be a little trial and error, modification and reduction but it works nicely for me for years.

Good Luck,
Matt

Option Explicit

Sub RefreshSignOffSheets()

'PURPOSE: To loop through all Excel files in a chosen folder

'          and update them automatically

    Dim wb As Workbook

Dim myPath As String

Dim myFile As String

Dim myExtension As String

Dim FldrPicker As FileDialog

Dim Location As String

Dim newDate As String


'Location folder to save the file to is equal to the path listed on the spreadsheet

Location = Range("B10")


'newDate is the new date to add on to the end of the file names as they save to the new folder

newDate = Range("B13")


'Optimize Macro Speed

Application.ScreenUpdating = False

Application.EnableEvents = False

    Application.Calculation = xlCalculationManual

    'Retrieve Target Folder Path From User

With Application.FileDialog(msoFileDialogOpen)

.Title = "Select Updated Sign-Off Sheet Folder"

.AllowMultiSelect = False

.Show

myPath = CreateObject("Scripting.FileSystemObject").Getfile(.SelectedItems(1)).ParentFolder.Path & "\"

    End With

    'In Case of Cancel

NextCode:

myPath = myPath

    If myPath = "" Then GoTo ResetSettings

    'Target File Extension (must include wildcard "*")

     myExtension = "/*.xlsm"

    'Target Path with Ending Extention

     myFile = Dir(myPath & myExtension)

    'Loop through each Excel file in folder

Do While myFile <> ""

'Set variable equal to opened workbook

Set wb = Workbooks.Open(Filename:=myPath & myFile)


'Enter the code here to do whatever you want to do to the opened workbook

        '========================================================================




'========================================================================


'Save As and Close Workbook

' ActiveWorkbook.SaveAs Filename:=Location & Left(myFile, Len(myFile) - 15) & newDate & ".xlsm"

ActiveWorkbook.SaveAs Filename:=Location & "\" & Left(myFile, Len(myFile) - 15) & newDate & ".xlsm"

        wb.Close

        'Get next file name

myFile = Dir

    Loop

    'Message Box when tasks are completed

     MsgBox "ENTER WHAT YOU WANT THE MESSAGE BOX TO SAY HERE"

ResetSettings:

'Reset Macro Optimization Settings

Application.EnableEvents = True

Application.Calculation = xlCalculationAutomatic

    Application.ScreenUpdating = True

End Sub