Code to automatically accept OK button without clicking it

Topic

New Reply

I have a macro that opens several files and activates a particular macro that brings up a message box that prompts the user the “OK” button

I would like code to would automatically accept the “OK” button so the user does not even see the message box appear

See print screen sample

I tried using application.enablevents = False, but this does not resolve this issue

Your assistance in this regard is most appreciated

Reply | Quote

Replies

Howard,

Create a UserForm instead. Add a label with the message you want to convey; no button. Place the following code in the userform’s module. Change the 5 in the code to how many second you would like the message to dosplay.

Whatever ever line in your code opens the message box, change to open the userform.

ex. If a=3 then UserForm1.show

Code:

Private Sub UserForm_Activate()
[COLOR=”#008000″]’—————————————-
‘CHANGE PAUSETIME TO ADJUST SPEED[/COLOR]
Dim PauseTime, Start
    PauseTime = 5
    Start = Timer
    Do While Timer < Start + PauseTime
        DoEvents
    Loop
UserForm1.Hide
End Sub

38331-Howard_Userform 38332-Howard_Userform2

A userform is so much more flexible than a message box in that you can manipulate the size, font, foreground and background color, image, etc.

If you are programmatically creating the message you would do the same for the label. In your existing code you would have something similar that builds your message like

Code:

UserForm1.Label1.Caption= "TB IN STD FORMAT SUCCESSFULLY CREATED AS C:PULL" & _
           [COLOR="#0000FF"]filename[/COLOR] & ".csv" & CHR(13) & CHR(13) & _

           "THIS FILE CAN BE CLOSED WITHOUT SAVING AGAIN" & CHR(13) & _
           "(OR SAVE COPY AS ECMHOTByymm.csv IF YOU WISH TO ARCHIVE)"

If you need help, post your code

HTH,
Maud

Reply | Quote

Try Application.DisplayAlerts = False instead

Reply | Quote

Thanks for the info Maud, however the message box with the OK button still appears

Reply | Quote

Howard,

I think what you need to do is to modify the procedure that displays the message to accept an optional parameter. You can then test the value of that parameter and determine whether or not to display the message. Here’s some sample code that will preserve the function of the procedure if called from anywhere else in the code but allow you to make it not display the message by calling it with the parameter of False.

Code:

Option Explicit

Sub DisplayMsgBox(Optional ByVal bMsgDisp As Boolean = True)

' If no argument is passed or True is passed
' the message will be displayed.
' If False is passed the message will NOT be display.

   If bMsgDisp Then
     MsgBox "Hello I'm your message box" & vbCrLf & _
            "I was told to display!", vbOKOnly + vbInformation, _
            "Message Box Title"
   End If
   
End Sub   'DisplayMsgBox

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Hi RG

Thanks for the help. I used your code and called it from within my macro using application.run “DisplayMsgBox”, but the message box still appears and I have to select the “OK” button to accept

I want the macro to automatically select the OK button and this must be in the done in the background if at all possible

Reply | Quote

Howard,

You have to call it with the False parameter, e.g. DisplayMsgBox False

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

I am gathering that this is not a Windows generated message but one that is programmed into your code. I am missing something very obvious here. If you do not want the message box to appear, why aren’t you just removing it from your code?

Reply | Quote

Maud,

This is the problem we face when we don’t have ALL THE FACTS! What we see is not necessarily what the the user sees but the user isn’t telling the whole story but rather what they see as the problem, which of course may not be the problem at all!
37551-headbang
All we can do is provide an answer for the problem as we see it and see if it solves the user’s problem. I know it’s frustrating when you work on a solution only to be told that’s not the problem!

All that said, that is why I programmed the VBA the way I did. I’ve had code I’ve written before that I wanted to act differently depending on how it was invoked. HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Maudibe,

Your psychic powers are on turbo this evening – I was going to write just what you have written . . . taking out the line which calls up the message box is ALMOST the same as hitting “OK”. It may be that the code is dependent on the response but, as you imply, it would be easier to force it to go down the “OK” route within the code than to go round the houses creating a message box and then faking the response.

M

Reply | Quote

RG, you are correct! There may be more to this since we are given such limited facts. Not frustrating, just confusing.

My first thought was that this was a Windows generated error but the spelling was incorrect and a Google search showed no returns on the verbiage. Going on the premise that this is a message box with a message programmatically constructed using the path and the file name, it appears that that nature of the message is invoked by an event rather than a condition.

Martin, as you elude, basically the workflow seems to be pause/message/continue. That would make it easy since it becomes a straight forward matter commenting out the line or removing it from an event subroutine.

Howard, I think you need to post the code.

Reply | Quote

Hi Guys

Sorry for the confusion caused

See code below from original workbook which generates the message box in the source workbook. When calling this from the destination workbook, I want OK button to be automated so that the user does not have to click on it as there are several other workbook that are opened and this macro is being called for each of them

Code:

 Sub CSV_Export()


Application.DisplayAlerts = False
    
    Workbooks.Add
    Dim TempFile As String
    TempFile = CsvFileName & ".csv"
    ActiveWorkbook.SaveAs Filename:=CsvPath & "" & TempFile, FileFormat:= _
        xlCSV, CreateBackup:=False
    
    Windows(MainFile).Activate
        Sheets(DataSht).Select
        Calculate
    Sheets(DataSht).Select
    Application.GoTo Reference:="WQ1"
    Selection.Copy
    Windows(TempFile).Activate
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("B:B").EntireColumn.AutoFit
    
    Windows(MainFile).Activate
        Range("A1").Select
        Range("TDAYS_MNTH").Select
    Windows(TempFile).Activate
        
ActiveWorkbook.Save
    MsgBox "FILE CREATED AS " & CsvPath & "" & TempFile & Chr(13) & Chr(10) & Chr(13) & Chr(10) _
        & "       THIS FILE CAN BE CLOSED WITHOUT SAVING AGAIN" & Chr(13) & Chr(10)"
       
TempFile = ""
End Sub 

Reply | Quote

Howard,

Placing a simple apostrophe in front of the message Box statement will make it a comment and not execute. The code will bypass it and it will not be seen not require any intervention from the user. The function of the code will not be altered.

Maud

Code:

 Sub CSV_Export()


Application.DisplayAlerts = False
    
    Workbooks.Add
    Dim TempFile As String
    TempFile = CsvFileName & “.csv”
    ActiveWorkbook.SaveAs Filename:=CsvPath & “” & TempFile, FileFormat:= _
        xlCSV, CreateBackup:=False
    
    Windows(MainFile).Activate
        Sheets(DataSht).Select
        Calculate
    Sheets(DataSht).Select
    Application.GoTo Reference:=”WQ1″
    Selection.Copy
    Windows(TempFile).Activate
    Range(“A1”).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns(“B:B”).EntireColumn.AutoFit
    
    Windows(MainFile).Activate
        Range(“A1”).Select
        Range(“TDAYS_MNTH”).Select
    Windows(TempFile).Activate
        
ActiveWorkbook.Save
   [COLOR=”#008000″] ‘MsgBox “FILE CREATED AS ” & CsvPath & “” & TempFile & Chr(13) & Chr(10) & Chr(13) & Chr(10) _
        & ”       THIS FILE CAN BE CLOSED WITHOUT SAVING AGAIN” & Chr(13) & Chr(10)”[/COLOR]
       
TempFile = “”
End Sub

Reply | Quote

Thanks for pointing this out Maud. I was hoping that there may be another way. I will put an apostrophe in front of the message Box for all the files containing this message box statement

Reply | Quote

Howard,

To help me better understand could you please elaborate why you would want another way? Is there a significance for doing it the way you describe?

Maud

Reply | Quote

Hi Maud

The source files were developed by someone else so I did not want to amend their code, but I will ask the person to put an apostrophe in front of their code as this is the easiest way

Reply | Quote

Thanks Howard. No matter how you slice it, there would need to be some sort of VBA intervention to do what you are asking. The apostrophe is the most benign of all.

Reply | Quote

Hi Maud

Agree 100%. I’m going to use your suggestion by placing an apostrophe in front of the section as mentioned by you

Reply | Quote

Hi Howard

Maud’s suggestion will do as you ask i.e. ‘the user does not even see the message box appear’

There are occasions when you might want to have a message box automatically timed-out i.e. close automatically if the User does not respond. This is useful when a routine is ‘scheduled’ and may be ‘unattended’.

I have appended a simple sample file using your example code and my demo of a timed-out message box.
And just for good measure, I had the routine ‘speak’ the message in case the User wasn’t paying attention to the screen. (It sounds better if you have Windows7 i.e. ladies voice.)
..and to keep RG happy, I ‘Dim’med my variables

zeddy

Reply | Quote

Zeddy,

Neat code! :thewave:

I found the wait for the message box to show was painfully slow and thought it might be due to late binding of the script host. So I set it up to do early binding w/same results! Then I noticed (you did mention it but I don’t have speakers turned on) the speech lines. So I commented them out and instant message. This got me to wondering if there is much of a penalty in Early vs Late binding so I instrumented the code and it appears there is no penalty (at least not in this instance). For those who might be interested in this here is Zeddy’s code w/my adjustments and results.

Code:

Sub demoMessageBoxTimeout()

   Dim saywhat         As String
   Dim msgBoxTitle     As String
   Dim zTimeoutSeconds As Integer
   Dim btns            As Integer
   Dim wshMsg          As WshShell  'For Early Binding MUST set Reference to
                                    'Windows Script Host Object Model
   Dim dtStart         As Date      'Variables for timing tests
   Dim dtEnd           As Date

   'DEFINE MESSAGE TO SHOW..
   saywhat = "FILE CREATED AS " & CsvPath & "" & TempFile & vbCr & vbCr
   saywhat = saywhat & "       THIS FILE CAN BE CLOSED WITHOUT SAVING AGAIN"
   saywhat = saywhat & vbCr

   'DEFINE REQUITRED TIMEOUT IN SECONDS..
   zTimeoutSeconds = 3

   'DEFINE MESSAGE BOX HEADING..
   msgBoxTitle = "The following message box will close automatically in " _
                 & zTimeoutSeconds & " seconds"

'CHOOSE WHICH MESSAGE BOX BUTTONS TO SHOW..
'btns = vbOKOnly + vbInformation
'btns = vbOKOnly + vbExclamation
'btns = vbYesNo + vbExclamation
'btns = vbYesNo + vbExclamation + vbDefaultButton2

   btns = vbOKOnly + vbInformation

   dtStart = Now()   'Capture Start Time

   Application.Speech.Speak msgBoxTitle
   Application.Speech.Speak saywhat

   '*** Late Binding Code ***
   CreateObject("WScript.Shell").Popup saywhat, zTimeoutSeconds, msgBoxTitle, btns
 
   '*** Early Binding Code ***
   'Set wshMsg = New WshShell
   'wshMsg.Popup saywhat, zTimeoutSeconds, msgBoxTitle, btns

   dtEnd = Now()     'Capture Ending Time
   
   '*** Output Timing Results to Immediate Window, change text as appropriate! ***
   Debug.Print "Late Binding with Speech: " & Format(dtEnd - dtStart, "hh:mm:ss")

End Sub

Results:
[noparse]
Early Binding No Speech: 00:00:04
Early Binding with Speech: 00:00:14
Late Binding No Speech: 00:00:04
Late Binding with Speech: 00:00:14
[/noparse]

I’m sure this neat trick will be showing up in my code in the future thanks again Zeddy! :fanfare::clapping:

:cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Hi RG

Although it didn’t seem to make much difference in timing in this particular case, Early Binding is supposed to be a little faster. Early Binding also gives you the availability of ‘intellisense’ help as you develop the code.

In most cases, I prefer to use Late Binding since I don’t have to worry about specific versions of Object Libraries when working or distributing to users with different versions of Office, Excel, Word etc etc.

zeddy

Reply | Quote

Hey Y’all,

I liked Zeddy’s code so much I’ve developed it into a reuseable generalized function so I thought I’d share it with y’all I hope some of you may find it useful.

Code:

'                        +--------------------+                 +----------+
'------------------------| MsgBoxWithTimeout  |-----------------| 11/03/14 |
'                        +--------------------+                 +----------+
'Adapted from code by Zeddy on Windows Secrets Lounge

'Called by  : Other Modules
'Arguments  : zPopUpMsg         The message to be displayed in the box
'             zPopUpTitle       The title to be displayed in the title bar
'             iTimeoutSeconds - The number of seconds before the messate box
'                               is automatically dismissed.
'             iPopUpBtns      - The integer value of the button + icon to be
'                               displayed in the message box.
'                                                              (Optional)
'                               Buttons       Icons           Default Button
'                               -------       -----           --------------
'                               vbOKOnly      vbInformation
'                               vbYesNo       vbExclamation   vbDefaultButton2
'                               vbYesNoCancel vbCritical      vbDefaultButton3
'
'                               Pick ONLY 1 from each column and add together
'                               e.g. vbOkonly+vbCritical
'
'Returns   : If the routine times out a -1 is returned otherwise the value of
'            the pressed button is returned, e.g.:
'
'            Button      Value    VBA Constant
'            ------      -----    ------------
'            OK            1      vbOk
'            CANCEL        2      vbCancel
'            YES           6      vbYes
'            NO            7      vbNo
'
'Example Calling Stmts:
'  Dim iAns as Integer
'  Default: iAns = MsgBoxWithTimeout("This is a test", "Testing:", 3, _
                                     vbYesNoCancel + vbCritical)
'  w/Sound: iAns = MsgBoxWithTimeout("This is a test", "Testing:", 3, _
                                     vbYesNo + vbInformation, True)
'Note: if you don't care about the return value drop the iAns = & ()

Function MsgBoxWithTimeout(zPopUpMsg As String, zPopupTitle As String, _
                           iTimeoutSeconds As Integer, iPopUpBtns As Integer, _
                           Optional ByVal bSpeak As Boolean = False) As Integer

'   Dim wshMsg          As WshShell  'For Early Binding MUST set Reference to
                                    'Windows Script Host Object Model
   Dim dtStart         As Date      'Variables for timing tests
   Dim dtEnd           As Date

'   dtStart = Now()   'Capture Start Time

   If (bSpeak) Then
     Application.Speech.Speak zPopupTitle
     Application.Speech.Speak zPopUpMsg
   End If
   
   '*** Late Binding Code ***
   MsgBoxWithTimeout = CreateObject("WScript.Shell").Popup(zPopUpMsg, _
                                    iTimeoutSeconds, zPopupTitle, iPopUpBtns)
 
   '*** Early Binding Code ***
   'Set wshMsg = New WshShell
   'wshMsg.Popup zPopUpMsg, iTimeoutSeconds, zPopUpTitle, iPopUpBtns

'   dtEnd = Now()     'Capture Ending Time
   
   '*** Output Results to Immediate Window, change text as appropriate! ***
'   Debug.Print "Late Binding with Speech: " & _
                Format(dtEnd - dtStart, "hh:mm:ss")

End Function    'MsgBoxWithTimeout

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Hi Guys

Thanks for your input, much appreciated

Reply | Quote

Just as an FYI, the Shell.Popup is unreliable in my experience in terms of dismissing itself.

Reply | Quote

Hi RG

We should always pay attention when rory has something to say.

So just in case, I’ve added an updated file which also includes a Form method.
The advantage of using a Form is you can do some other stuff.
So now the displayed (form) message includes a countdown timer as well.

zeddy

Reply | Quote

Zeddy & Rory,

Zeddy, again a nice piece of code. However, it is not a versatile as the WSH version due to the need to change the design of the form for things as simple as different buttons and then there’s the problem of returning the value of the pushed button (would require a global variable not a big problem but not the best coding practice either.).

Rory, could you be more specific as to when you had problems with the WSH method? I know I have a bad habit of remembering problems from the past (yeah I know it’s a Long one!) only to find out that I haven’t retested it with newer versions of software and/or operating systems.

:cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Unfortunately not, as I never figured out why it worked sometimes and not others – it appears all over the various forums as an issue though, so it’s not just me.

We should always pay attention when rory has something to say.

I wish someone would tell my kids that. And my wife, for that matter.

Reply | Quote