Display Msgbox When Item Selected From Combo Box

  • Im using code from website:


    to display a combobox when I double click any cell that contains a validation list in it. I'm not great with VBA so I am having problems finding what each part of the code does. This is causing problems for another bit of code that I use to display a msgbox when certain values are selected from the list.

    The MsgBox shows up great when I select an item from the validation list but does not work at all when I select the same item from the double-clicked combo box.

    It would be great if I could get the MsgBox to work both ways.

    This is the code that displays the MsgBox when target value is selected from list

    Private Sub Worksheet_Change(ByVal Target As  Range) 
        Select  Case Target.AddressLocal(False, False) 
        Case Is = "B4", "B5", "B6", "B7", "B8", "B9", "B10", "B11", "B12" 
            If Target.Value = "H" Then 
                 MsgBox "This is a pop for extra information. Savvy?", vbYesNo, "Yo!" 
            End If 
        End Select 
    End Sub

    This is the code that displays the ComboBox when I double click a validation list cell


  • Re: Display Msgbox When Item Selected From Combo Box

    Selecting an item from the non-Validation dropdown does not trigger a worksheet change event. That incarnation of the MsgBox would need to be from a ComboBox_Change routine. For debugging you might want both Change routines to call the same msgbox routine.

    (I don't have ActiveX on this machine, so don't trust the ComboBox routine's syntax as posted.)

  • Re: Display Msgbox When Item Selected From Combo Box

    Why are you doing all that? I cannot see any point in coding a validation list to switch to a ComboBox. If you want comboboxes, use them and forget about Validation.

    Perhaps what you are REALLY trying to do is this?
    AutoComplete for Data Validation List

  • Re: Display Msgbox When Item Selected From Combo Box


    That is one reason but I also need to display more than 8 list items at a time and I would also like the ability to change the font of the list. Any suggestions? I'll make a new thread if you think you can help with this.


  • Re: Display Msgbox When Item Selected From Combo Box

    Rick, this is why in my PM to you AND on the New Thread page it states to ONLY use Thread Titles to describe in keywords what you are ultimately wanting to do and NEVER what you THINK you need to achieve it.

    If you do that, the Thread can stay on topic and yet mention any number of ways to achieve your needs.

    If ALL you need is more the 8 items displayed and control over Font size simply use an ActiveX ComboBox on a UserForm, or on a Worksheet.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!