Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
| Search | Today's Posts | Mark Forums Read
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old September 21st, 2006, 11:35 AM
Registered User
 
Join Date: Sep 2006
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default "Are you Sure" Before Dropping List in ComboBox

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
  #2 (permalink)  
Old September 21st, 2006, 01:34 PM
Authorized User
 
Join Date: Mar 2006
Location: London, , United Kingdom.
Posts: 73
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

  #3 (permalink)  
Old September 22nd, 2006, 06:57 AM
Registered User
 
Join Date: Sep 2006
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

That Worked Thanks for the Help!


Similar Threads
Thread Thread Starter Forum Replies Last Post
Combobox List Fill Range jani Access VBA 1 July 29th, 2008 05:04 PM
list of values from postgresql to combobox zamir4eva Beginning PHP 1 January 3rd, 2007 11:29 PM
Can't fill up list of Staff Name in the combobox VS2005 Visual Basic 2005 Basics 3 October 12th, 2006 09:06 AM
Dynamic Combobox List Intern06 Pro VB Databases 2 June 29th, 2006 09:41 AM
Combobox that shows what's not in its list Mitch Access 6 September 18th, 2003 11:00 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.