When I have to present my report, in some of them, I like that when I click a cell, a validation message pops out.This macro works as the input message of the data validation option. If you are looking for a message box, read Popup a message box the value of different cells of the same row using a macro in an excel report.

 

When I use the macro ?

To popup a message when I click on a specific cell.

 

How to create the macro ?

Read How to create, edit, hide and select a macro in an excel report

 

How to create the button to associate it with the macro ?

Read How to create a button and associated it to a macro in an excel report

 

How to use the macro ?

This macro should be used always with Worksheet_SelectionChange(ByVal Target As Range) and it needs to be put in the corresponding sheet.

 

How is the macro ?

Copy the code below and paste it into your macro. You will see my comments in green if exist so follow the help to adapt to your need.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Static prevRng As Range
If Not prevRng Is Nothing Then
prevRng.Validation.delete
End If
' change B2:B10 by the range where the message should appear
' for a full column, put Columns("B") instead of Range("B2:B10")
If Intersect(Target, Range("B2:B10")) Is Nothing Then Exit Sub
If Target.Value = "MS003" Then
Set prevRng = Target
With Target.Validation
.delete
.Add xlValidateInputOnly
' change E2 by the cell value to display
.InputMessage = Range("E2").Value
End With
End If
End Sub

So if the cells in the column B have a value “MS003”, each time, I click in one of the cells, a message will come out, showing the value of the cell E2.This E2 value will be put automatically into the “input message” of the “data validation”.