Search Contextures Sites
- Set up the Workbook
- Create a Data Validation Dropdown List
- Add the Text Box
- Name the Text Box
- Add the Code
- Test the Code
- Show Longer Messages
- Download the Sample File
- More Data Validation Tutorials
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.
Two worksheets are required in this workbook.
- Delete all sheets except Sheet1 and Sheet2
- Rename Sheet1 as ValidationSample
- Rename Sheet2 as ValidationLists
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
- In cells A1:A7 type a list of weekdays
- In cells C1:C12 type a list of months
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
- Cells C5:C15 have data validation lists with the source DayList. When a cell in this range is selected, a dropdown list of weekdays is available. Include an Input Message, as described here: Display Messages to the User . The message used in the sample file is:
Title: Activation Day
Message: Please select the weekday in which the product was originally purchased, not the weekday in which it was first used. If you are not sure, please leave this cell blank, then check with your manager, and fill in the weekday later.
- Cells D5:D15 have data validation lists with the source MonthList. When a cell in this range is selected, a dropdown list of months is available. Include an Input Message. The message used in the sample file is:
Title: Activation Month
Message: Please select the month in which the product was originally purchased, not the month in which it was first used. If you are not sure, please leave this cell blank, then check with your manager, and fill in the month later.
- If the Drawing Toolbar is not visible, display it (View | Toolbars)
- On the Drawing Toolbar, click the Text Box tool.
- Draw a text box at the top of the worksheet, large enough to hold your messages.
- Type some sample text, e.g. "This is the Input Message"
- Format the text box with the font and font size you'd like.
- Right-click on the border of the text box, and choose Format Text Box
- Select the Properties tab
- Select Don't move or size with cells
- Remove the check mark from Print object
- Click OK
- Click on the border of the text box, to select it
- Click in the Name Box, at the left of the Formula Bar
- Type the text box name: txtInputMsg
- Press the Enter key
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 '====================================
To add this code to the worksheet:
- Right-click on the sheet tab, and choose View Code.
- Choose Edit | Paste, to paste the code onto the sheet module, where the cursor is flashing.
- Choose File | Close and Return to Microsoft Excel.
- Select one of the cells that contains a data validation input message.
- The text box will appear
- Select a cell that doesn't contain a data validation in put message
- The text box disappears.
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.
For short messages (the technique described in the instructions above), download the zipped sample file. This file is in Excel 2003 format (xls), and contains macros.
For longer messages, download the Long Message sample file. This has an extra sheet where you can enter longer messages, and they will be displayed in a text box, based on the data validation input message's Title. This file is in Excel 2007 format (xlsm), and contains macros.
Data Validation Basics
Data Validation - Create Dependent Lists
Data Validation - Dependent Dropdowns from a Sorted List
Data Validation - Dependent Lists With INDEX
Hide Previously Used Items in a Dropdown List
Data Validation - Display Messages to the User
Data Validation - Display Input Messages in a Text Box
Data Validation - Use a List from Another Workbook
Data Validation Criteria Examples
Data Validation Custom Criteria Examples
Data Validation Tips
Data Validation Documentation
Data Validation Combo Box
Data Validation Combo Box - Named Ranges
Data Validation Combo Box -- Click
Data Validation - Add New Items
Contextures Inc., Copyright ©2013
All rights reserved.