Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Code to automatically accept OK button without clicking it

    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
    Attached Files Attached Files

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 644 Times in 588 Posts
    Try Application.DisplayAlerts = False instead

  3. #3
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Thanks for the info Maud, however the message box with the OK button still appears

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    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
    Last edited by RetiredGeek; 2014-11-02 at 10:26.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    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

  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Howard,

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

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #7
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 644 Times in 588 Posts
    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?

  8. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    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!
    headbang.gif
    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
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  9. The Following User Says Thank You to RetiredGeek For This Useful Post:

    XPDiHard (2014-11-03)

  10. #9
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    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

  11. #10
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 644 Times in 588 Posts
    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.

  12. #11
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    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
    Last edited by HowardC; 2014-11-02 at 22:14.

  13. #12
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 644 Times in 588 Posts
    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
        '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

  14. #13
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    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

  15. #14
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 644 Times in 588 Posts
    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

  16. #15
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    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

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
  •