Search Contextures Sites

 

Excel -- Data Validation -- Display Input Messages in a Text Box

Set up the Workbook    
Create a Data Validation Dropdown List    
Add the Text Box  
Name the Text Box  
 Add the Code  
Test the Code  

 

Download the zipped sample file


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.


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:

'=========================================
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.

Download the zipped sample file

 

1. Data Validation
2. Data Validation -- Create Dependent Lists
3. Hide Previously Used Items in a Dropdown List
4. Display Messages to the User
5. Use a List from Another Workbook
6. Validation Criteria Examples
7. Custom Validation Criteria Examples
8. Data Validation Tips
9. Data Validation Documentation
10. Data Validation -- Combo Box
11. Data Validation -- Combo Box using Named Ranges  
12. Data Validation -- Display Input Messages in a Text Box 
13. Data Validation -- Dependent Dropdowns from a Sorted List  
14. Data Validation -- Combo Box -- Click
15. Data Validation -- Create Dependent Lists With INDEX  

 

       Home     Excel Tips     Excel Files      Blog    Contact

RSS Feed

Privacy Policy

 

 

The Excel Store