Overcome data validation input message limitations, by displaying messages in a text box, that appears only when a data validation cell is selected
You can use a Data Validation Input Message to display a message when a cell is selected. However, the font can't be changed, nor can message box size be controlled.
To overcome these limitations, you can create a text box to display the message, and use programming to make it appear if cells that contain a data validation Input Message are selected.
NOTE: The technique shown here will only show the input message text, from the data validation setup window. If you want to show a longer message, download the Long Message sample file.
NOTE: For a similar technique, that shows a message when non-data validation cells are selected, download the Selected Cells Message sample file, in the Downloads section.
Two worksheets are required in this workbook.
On the ValidationLists sheet, type the lists that will be used in the data validation dropdowns:
Tip: Use the AutoFill feature to create the lists
Name the lists (there are Naming instructions here: Name a Range):
The next step is to create the data validation dropdown lists.
There are detailed instructions here: Data Validation -- Introduction
Visual Basic for Applications (VBA) code is required to make the text box appear when you select a cell that contains a data validation input message. It copies the data validation Input Message and Input Title to the text box, and makes the title bold.
Copy the following code, and follow the instructions below, to add it to the workbook:
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim strTitle As String Dim strMsg As String Dim lDVType As Long Dim sTemp As Shape Dim ws As Worksheet Application.EnableEvents = False Set ws = ActiveSheet Set sTemp = ws.Shapes("txtInputMsg") On Error Resume Next lDVType = 0 lDVType = Target.Validation.Type On Error GoTo errHandler If lDVType = 0 Then sTemp.TextFrame.Characters.Text = "" sTemp.Visible = msoFalse Else If Target.Validation.InputTitle <> "" Or _ Target.Validation.InputMessage <> "" Then strTitle = Target.Validation.InputTitle & Chr(10) strMsg = Target.Validation.InputMessage With sTemp.TextFrame .Characters.Text = strTitle & strMsg .Characters.Font.Bold = False .Characters(1, Len(strTitle)).Font.Bold = True End With sTemp.Visible = msoTrue Else sTemp.TextFrame.Characters.Text = "" sTemp.Visible = msoFalse End If End If errHandler: Application.EnableEvents = True Exit Sub End Sub
The data validation input message is limited to 255 characters, so the technique show above is subject to that limit too.
If you need to show longer messages in the text box, you can download the Longer Message sample file, from the Download section, below.
This example uses most of the code from the original example, and adds a new feature. There is a worksheet where you can enter a list of Input Message Titles, and the message that you want to display for each of those titles.
When you click on a data validation cell in the Longer Message sample file, the Input Message title is used as a lookup in the messages table.
If there is an entry for that title, the Additional Message text is added to the end of any existing Input Message text. Then, the entire text string is shown in the text box at the top of the worksheet.
So, with this version of the sample file, you can display much longer messages when someone clicks on a data validation cell. You'll have to adjust the size of the text box, to fit the longer messages, or reduce the font size, so more characters will fit.
Last updated: December 11, 2016 3:07 PM