Programmatically clicking "ok" on message box

[cloh]

Hi all, I am using Access to grab some data from a database and plot a graph in Excel. When I do this, there is a message box in Excel that pop ups with an initialization error, that asks me to click ok - however the data that I want is already in the worksheet.

Is there a way to automate the "ok" so that I don't need click it every time I generate one of these sheets? I generate 13 at a time. I have tried the

application.DisplayAlerts = false

but it doesn't work. Any ideas? Thanks in advance!

[FishVal]

Hi, cloh.

Could you post your code (I guess)?

[cloh]

Hey, don't know if this will help (the code is very long and convoluted) but this is where I am trying to "hide" the messagebox...

Public Sub ProcessGUIClick(gui As Form, Optional destFolderPath As String)
    Dim fdialog As FileDialog
    Dim result As Integer
    Dim gui As Form
    Set gui = Form_LocbyCcy
    Set fdialog = Application.FileDialog(msoFileDialogFolderPicker)
        With fdialog
        .AllowMultiSelect = False
        .ButtonName = "Select"
        If .Show = -1 Then
            Excel.Application.DisplayAlerts = False
            createGlobalCcys destFolderPath:=.SelectedItems(1)
            createImpCcys destFolderPath:=.SelectedItems(1)
            Excel.Application.DisplayAlerts = True
            OpenChart39 destFolderPath:=.SelectedItems(1)
            Call MakeCharts
        End If
    End With
End Sub

[cloh]

This is a sample of the "createGlobalccys" function - the "other createImpCcys" is similar:

Public Sub createGlobalCcys(Optional destFolderPath As String)
    Dim xlapp As Excel.Application
    Dim fso As New FileSystemObject
    Dim newFolder As Folder
    Dim Filename As String
    Dim gui As Form
    Dim Date1val As String
    Dim Date2val As String
    Dim Date1value As String
    Dim Date2value As String
 
    Set gui = Form_LocbyCcy
    Date1val = gui.txt_FromDate.value
    Date2val = gui.txt_ToDate.value
    Date1value = DateAdd("d", -1, Date1val)
    Date2value = DateAdd("d", 1, Date2val)
 
    On Error Resume Next
    Set xlapp = New Excel.Application
    If Err <> 0 Then
        MsgBox "Could not start Excel", vbExclamation
        GoTo createGlobalCcys_err
    End If
 
    If Not fso.FolderExists(destFolderPath) Then
        MsgBox "Could find the specified folder!", vbExclamation
        GoTo createGlobalCcys_err
    Else
        Set newFolder = fso.GetFolder(destFolderPath)
    End If
 
    On Error GoTo createGlobalCcys_err
 
    Filename = fso.BuildPath(newFolder.path, "GlobalCcys.xls")
 
    CreateGlobalChart graphTypeID:=1, Date1:=Date1value, Date2:=Date2value, _
            Filename:=Filename, xlAppRef:=xlapp
 
createGlobalCcys_exit:
    Exit Sub
createGlobalCcys_err:
    MsgBox Err.Description
    Resume Next
End Sub

[cloh]

Sorry guys, with regards to this post basically all I need help with is the VB code to dismiss a msgbox. Any ideas?

Oh yeah, Happy Thanksgiving in advance!

[FishVal]

Several points:

• are you sure the message is thrown by Excel ?
• if you want to run method / set property of some particular object you should reference the object implicitely, code like
  Excel.Application.DisplayAlerts ....
  has a little chance to take an effect on your particular application instance
• you should localize code line throwing the error, if you have difficulties debugging code then take a look at WIP: Debugging in VBA article
• there is no simple method to close messagebox

P.S. Happy Thanksgiving.