I have a group of validation list boxes, all with the same choices, that when a user selects a certain item from them I want a popup box with extra info in it to appear.
Is this possible?
Is it possible for the user to click on the popup box once to make it disappear again?
Re: Display Popup Box When User Picks Item From List
I put the letters A - J and a blank cell in J1:J11. Then used that list as a validation for cell A1. The following code fires when the user selects the letter H from the drop-down list.
Re: Display Popup Box When User Picks Item From List
Thats amazing thanks for the quick response!
Go Reds![hr]*[/hr] Auto Merged Post;[dl]*[/dl]Its working great except for two problems.
1. How do I apply this code to a range of cells, with validation lists, from B4:B12. All of the lists have the same selections
2. I currently have an option to double click on the validation list to pop up a combo box. If I use that option to choose the items in the lsit then the popup message box does not show. Any suggestions on how to fix that?
Re: Display Popup When User Picks Item From List
Solution to problem #1:
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 SubQuoteI currently have an option to double click on the validation list to pop up a combo box.
I'm not familiar with that option so I don't know how to intercept the event(s) taking place in order to insert validation/informational code.
Re: Display Popup When User Picks Item From List
Thanks Mavyak, that helps a lot
Does anyone else know how to "intercept the event(s) taking place on the combobox?
Re: Display Popup When User Picks Item From List
What steps did you take to be able to double click on a validation list and it pops up a combo box? I'd like to duplicate that work just to see it in action. If I can do that I may be able to further assist you.
Re: Display Popup When User Picks Item From List
I got this code from Contextures.com
http://www.contextures.com/xlDataVal11.html
You can double click on any cell on the worksheet "BOM" that has a validation list in it and it will pop up the combo box so that you can view more lines than the 8 that a validation list gives you.
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("BOM")
Cancel = 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
On Error GoTo errHandler
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
str = Target.Validation.Formula1
str = Right(str, Len(str) - 1)
With cboTemp
.Visible = True
.Left = Target.Left
.Top = Target.Top
.Width = Target.Width + 15
.Height = Target.Height + 5
.ListFillRange = str
.LinkedCell = Target.Address
End With
cboTemp.Activate
End If
errHandler:
Application.ScreenUpdating = True
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 = False
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.ScreenUpdating = True
Application.EnableEvents = True
Exit Sub
End SubIf you want I can send you the excel file. Its too big to put on the site.
Thanks for you help
Don’t have an account yet? Register yourself now and be a part of our community!