Display Msgbox When Item Selected From Combo Box

RickWHaas · Dec 8th 2007

Im using code from website:

http://www.contextures.com/xlDataVal11.html

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

Code

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

Code

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
  Cancel As Boolean)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Dim wsList As Worksheet
Set ws = ActiveSheet
Set wsList = Sheets("ValidationLists")


Cancel = True
Set cboTemp = ws.OLEObjects("TempCombo")
  On Error Resume Next
  With cboTemp
  'clear and hide the combo box
    .ListFillRange = ""
    .LinkedCell = ""
    .Visible = False
  End With
On Error GoTo errHandler
  If Target.Validation.Type = 3 Then
    'if the cell contains a data validation list
    Application.EnableEvents = False
    'get the data validation formula
    str = Target.Validation.Formula1
    str = Right(str, Len(str) - 1)
    With cboTemp
      'show the combobox with the list
      .Visible = True
      .Left = Target.Left
      .Top = Target.Top
      .Width = Target.Width + 5
      .Height = Target.Height + 5
      .ListFillRange = str
      .LinkedCell = Target.Address
    End With
    cboTemp.Activate
  End If
  
errHandler:
  Application.EnableEvents = True
  Exit Sub


End Sub
'=========================================
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet
Application.EnableEvents = False
Application.ScreenUpdating = True


Set cboTemp = ws.OLEObjects("TempCombo")
  On Error Resume Next
  With cboTemp
    .Top = 10
    .Left = 10
    .Width = 0
    .ListFillRange = ""
    .LinkedCell = ""
    .Visible = False
    .Value = ""
  End With


errHandler:
  Application.EnableEvents = True
  Exit Sub


End Sub

Display More

Thanks!

**mikerickson** · Dec 8th 2007

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.

Code

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" 
    Call MakeMsgBox(Target.Value)
End Select 
End Sub


Private Sub ComboBox1_Change():Rem syntax from memory
   Call MakeMsgBox (ComboBox1.Value)
End Sub


Sub MakeMsgBox(selectedValue as String)
If selectedValue = "H" Then 
        MsgBox "This is a pop for extra information. Savvy?", vbYesNo, "Yo!" 
End If 
End Sub

Display More

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

Dave Hawley · Dec 8th 2007

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

RickWHaas · Dec 8th 2007

Re: Display Msgbox When Item Selected From Combo Box

Dave,

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.

Thanks
Rick

Dave Hawley · Dec 8th 2007

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.

Display Msgbox When Item Selected From Combo Box

Participate now!

Share