vba yes no code

[ryan.fitzpatrick3]

I have an excel export button that works fine, i would like to add a
prompt that when I click the export to excel a yes/no prompt comes up,
yes to export no to exit prompt, this way i can eliminate accidental
clicks. Thanks in advance.

Ryan

 

[Dirk Goldgar]

I have an excel export button that works fine, i would like to add a
prompt that when I click the export to excel a yes/no prompt comes up,
yes to export no to exit prompt, this way i can eliminate accidental
clicks. Thanks in advance.

Check out the MsgBox function in the online help. You can call it with
arguments set to ask a yes/no question, then examine the result to see which
of the answers was chosen and act accordingly. Here's an example:

If MsgBox( _
"Do you want to export to Excel?", _
vbQuestion+vbYesNo, _
"Export to Excel?") _
= vbYes _
Then
' continue with the export ...
Else
' don't do it.
End If

 

[ryan.fitzpatrick3]

Would the code look like this with mine?


Private Sub Export_Click()
On Error GoTo Err_Export_Click


Dim dbCurr As DAO.Database
Dim qdfTemp As DAO.QueryDef
Dim lngOrderBy As Long
Dim strQueryName As String
Dim strSQL As String

If MsgBox( _
"Do you want to export to Excel?", _
vbQuestion+vbYesNo, _
"Export to Excel?") _
= vbYes _
Then
' continue with the export ...
Else
' don't do it.
End If

' You only need to go to this effort if there's a filter
If Len(Me.Filter) > 0 Then
Set dbCurr = CurrentDb

' Get the SQL for the existing query
strSQL = dbCurr.QueryDefs("QryAdageVolumeSpend").SQL

' Check whether there's an ORDER BY clause in the SQL.
' If there is, we need to put the WHERE clause in front of it.
lngOrderBy = InStr(strSQL, "ORDER BY")
If lngOrderBy > 0 Then
strSQL = Left(strSQL, lngOrderBy - 1) & _
" WHERE " & Me.Filter & " " & _
Mid(strSQL, lngOrderBy)

Else
' There's no ORDER BY in the SQL.
' Remove the semi-colon from the end, then append the WHERE clause
strSQL = Left(strSQL, InStr(strSQL, ";") - 1) & _
" WHERE " & Me.Filter
End If

' By using the current date and time, hopefully that means
' a query by that name won't already exist
strQueryName = "qryTemp" & Format(Now, "yyyymmddhhnnss")

' Create the temporary query
Set qdfTemp = dbCurr.CreateQueryDef(strQueryName, strSQL)

' Export the temporary query
DoCmd.TransferSpreadsheet transfertype:=acExport, _
spreadsheettype:=acSpreadsheetTypeExcel9, _
tableName:=strQueryName, FileName:= _
"C:\Documents and Settings\All Users\Desktop\Adage
Downloaded On" & Format(Now, "mm" & "-" & "dd" & "-" & "yyyy" & "@" &
"hh" & "nn") & ".xls", _
hasfieldnames:=True

' Delete the temporary query
dbCurr.QueryDefs.Delete strQueryName

Else

DoCmd.TransferSpreadsheet transfertype:=acExport, _
spreadsheettype:=acSpreadsheetTypeExcel9, _
tableName:=strQueryName, FileName:= _
"C:\Documents and Settings\All Users\Desktop\Adage
Downloaded On" & Format(Now, "mm" & "-" & "dd" & "-" & "yyyy" & "@" &
"hh" & "nn") & ".xls", _
hasfieldnames:=True

End If

Exit_Export_Click:
Set dbCurr = Nothing
Exit Sub

Err_Export_Click:
MsgBox Err.Description
Resume Exit_Export_Click

End Sub

 

[Dirk Goldgar]

Would the code look like this with mine?

You need to modify the code I posted to bypass the rest of the processing if
the user answers No. In this particular case, as I understand it, you just
want to exit the procedure in that case, so try it like this:

'----- start of revised code -----
Private Sub Export_Click()

On Error GoTo Err_Export_Click


Dim dbCurr As DAO.Database
Dim qdfTemp As DAO.QueryDef
Dim lngOrderBy As Long
Dim strQueryName As String
Dim strSQL As String

' Get confirmation from user.
If MsgBox( _
"Do you want to export to Excel?", _
vbQuestion+vbYesNo, _
"Export to Excel?") _
= vbNo _
Then
Exit Sub
End If

' ... continue with rest of original procedure ...

'----- end of revised code -----

I haven't looked at the rest of your procedure code; I'll just assume that
works.

 

[ryan.fitzpatrick3]

Thanks that worked!

You need to modify the code I posted to bypass the rest of the processingif
the user answers No.  In this particular case, as I understand it, you just
want to exit the procedure in that case, so try it like this:

'----- start of revised code -----
Private Sub Export_Click()

    On Error GoTo Err_Export_Click

    Dim dbCurr As DAO.Database
    Dim qdfTemp As DAO.QueryDef
    Dim lngOrderBy As Long
    Dim strQueryName As String
    Dim strSQL As String

    ' Get confirmation from user.
    If MsgBox( _
            "Do you want to export to Excel?", _
            vbQuestion+vbYesNo, _
            "Export to Excel?") _
        = vbNo _
    Then
        Exit Sub
    End If

    ' ... continue with rest of original procedure ...

'----- end of revised code -----

I haven't looked at the rest of your procedure code;  I'll just assume that
works.