I am trying to have a Are You Sure Message Box before dropping the list of a ComboBox in Excel. I have the code to make the message box but list drops even when you click no.

Below is the code. I have tried this in multiple events associated with the ComboBox. DoCmd.CancelEven does not work I get a Object Required Error. Any help would be greatly appreciated.

Private Sub cmbRecipeSelect_DropButtonClick()

    Dim stMsg As String
    Dim intResponse As Integer

    If intCounter = 0 Then
        
        cmbRecipeSelect.DropDown

    Else

        stMsg = "Are You Sure You Want To Change Recipes?" & vbCrLf & vbCrLf _
        & "You Will Loose All Changes Unless you Write Them To The Database First!"

        intResponse = MsgBox(stMsg, vbYesNo, "Are You Sure?")

        If (intResponse = vbNo) Then

            'DoCmd.CancelEvent
            'DON'T KNOW WHAT TO PUT HERE!

        End If

    End If


End Sub

ok here's a solution.. not too neat, but hopefully will be what you are after.

change the property ShowDropButtonWhen to fmShowDropButtonWhenNever

then have the following code

Private Sub cmbRecipeSelect_GotFocus()

    Dim stMsg As String
    Dim intResponse As Integer

    If intCounter = 0 Then

        cmbRecipeSelect.DropDown

    Else

        stMsg = "Are You Sure You Want To Change Recipes?" & vbCrLf & vbCrLf _
        & "You Will Loose All Changes Unless you Write Them To The Database First!"

        intResponse = MsgBox(stMsg, vbYesNo, "Are You Sure?")

        If (intResponse = vbYes) Then

               cmbRecipeSelect.DropDown

        End If

    End If


not sure if this is no a form, or just on an excel sheet.. but should hopefully work.. you may have to play with moving the focus away from the combobox if you say no to the question.. Hope this helps

That Worked Thanks for the Help!