Selecting A Cell Value And Displaying A Popup Or Tooltip

[Andy]

Hi Gang

I need to display messages to a user when they select certain values
in a cell. The cell data comes from a validated data list. Here's a
simple example. Say I have a list in a cell that shows the values
Yellow, Grey, Red, Blue and Green. When they select Blue, I want a
popup or a tooltip to show saying "You picked a great colour".

Can I do this in Excel?

Thanks
Andy

 

[Norman Jones]

Hi Andy,

Try something like:
'==========>>
Option Explicit

Private Sub Worksheet_SelectionChange( _
ByVal Target As Range)
Dim Rng As Range
Dim arrColours As Variant
Dim arrMsg As Variant
Dim msg As String
Dim i As Long, j As Long
Dim Res As Variant

If Selection.Count > 1 Then
Exit Sub
End If

Set Rng = Me.Range("A1:A10") '<<==== CHANGE

On Error Resume Next
Set Rng = Intersect(Rng, Target)
On Error GoTo 0

If Not Rng Is Nothing Then
arrMsg = VBA.Array("Excellent colour", _
"Great selection!", _
"Good choice", _
"Selelection could be better!", _
"I will hold my tongue!")

arrColours = VBA.Array("Blue", _
"Green", _
"Yellow", _
"Red", _
"Grey")

Res = Application.Match(Rng.Cells(1).Value, arrColours, 0)

If Not IsError(Res) Then
MsgBox Prompt:=arrMsg(Res - 1), _
Buttons:=vbInformation, _
Title:="Demo"
End If
End If
End Sub
'<<==========


This is worksheet event code and should be pasted
into the worksheets's code module (not a standard
module and not the workbook's ThisWorkbook module):

Right-click the worksheet's tab
Select 'View Code' from the menu and paste the code.
Alt-F11 to return to Excel.