Search Contextures Sites

 

Contextures
Excel news
by email

We respect your email privacy

 

 

30 Excel Functions in 30 Days

 

 

 

 

Learn how to create Excel dashboards.

 

 

 

 

 

 

 

 

Learn how to create Excel dashboards.

 

 

 

Live-link your Excel dashboards to nearly any web data.

 

 

Learn how to create Excel dashboards.

 

 

 

Data Validation -- Display Input Messages in a Text Box

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.

Set up the Workbook

Two worksheets are required in this workbook.

  1. Delete all sheets except Sheet1 and Sheet2
  2. Rename Sheet1 as ValidationSample
  3. 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

  1. In cells A1:A7 type a list of weekdays
  2. In cells C1:C12 type a list of months

Name the lists (there are Naming instructions here: Name a Range):

  1. Name the range A1:A7 as DayList
  2. Name the range C1:C12 as MonthList    

Create a Data Validation Dropdown List

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. 

Add the Text box

  1. If the Drawing Toolbar is not visible, display it (View | Toolbars)
  2. On the Drawing Toolbar, click the Text Box tool.

  3. Draw a text box at the top of the worksheet, large enough to hold your messages.
  4. Type some sample text, e.g. "This is the Input Message"

  5. Format the text box with the font and font size you'd like.
  6. Right-click on the border of the text box, and choose Format Text Box
  7. Select the Properties tab
  8. Select Don't move or size with cells
  9. Remove the check mark from Print object
  10. Click OK  

Name the Text box

  1. Click on the border of the text box, to select it
  2. Click in the Name Box, at the left of the Formula Bar
  3. Type the text box name:   txtInputMsg
  4. Press the Enter key  

Add the Code

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:

  1. Right-click on the sheet tab, and choose View Code.

  2. Choose Edit | Paste, to paste the code onto the sheet module, where the cursor is flashing.
  3. Choose File | Close and Return to Microsoft Excel.

Test the Code

  1. Select one of the cells that contains a data validation input message.
  2. The text box will appear
  3. Select a cell that doesn't contain a data validation in put message
  4. The text box disappears.

Show Longer Messages

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.

input message longer

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.

input message long text

Download the Sample File

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 Tutorials

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  

 

       Home     Excel Tips     Excel Files     The Excel Store     Blog     Contact

 

Privacy Policy

 

Contextures Inc., Copyright ©2013
All rights reserved.