|
Add an Input Message to a Cell - Message Appears When the Cell is Selected in Excel - Data Validation Macro
Add data validation input messages to cells in Excel with this free Excel macro. These messages appear when a specific cell has been selected. The benefit of this type of pop up message in Excel is that when a user goes to enter something in a particular cell, you can display a reminder to that user which can better help them fill out that cell. This works great when it comes to user forms and data entry forms in Excel.
There are two versions of this macro listed below. The first Excel macro adds an input message to a cell that is hard-coded into the macro whereas the second macro adds an input message to any cell that you select before you run the macro.
To use these macros simply change A1 in the first macro to the cell reference where you want to add an input message and change Message Title Goes Here and Message Contents Go Here in both macros in order to have the desired input message displayed.
Where to install the macro: Module
Free Excel Macro to Add an Input Message to a Specified Cell in Excel
Sub Add_Cell_Input_Message_Cell()
With Range("A1").Validation
.Add Type:=xlValidateInputOnly
.InputTitle = "Message Title Goes Here"
.InputMessage = "Message Contents Go Here"
End With
End Sub
Free Excel Macro to Add an Input Message to a Selected Cell in Excel
Sub Add_Cell_Input_Message_Selection()
With Selection.Validation
.Add Type:=xlValidateInputOnly
.InputTitle = "Message Title Goes Here"
.InputMessage = "Message Contents Go Here"
End With
End Sub
How to Install the Macro
- Select and copy the text from within the grey box above.
- Open the Microsoft Excel file in which you would like the Macro to function.
- Press "Alt + F11" - This will open the Visual Basic Editor - Works for all Excel Versions.
Or For other ways to get there, Click Here.
For Excel Versions Prior to Excel 2007
Go to Tools > Macros > Visual Basic Editor
For Excel 2007
Go to Office Button > Excel Options > Popular > Click Show Developer tab in the Ribbon. Then go to the Developer tab on the ribbon menu and on the far left Click Visual Basic
- On the new window that opens up, go to the left side where the vertical pane is located. Locate your Excel file; it will be called VBAProject (YOUR FILE'S NAME HERE) and click this.
- If the Macro goes in a Module, Click Here, otherwise continue to Step 8.
- Go to the menu at the top of the window and click Insert > Module
- Another window should have opened within the Visual Basic Editor's window. Within this new window, paste the macro code. Make sure to paste the code underneath the last line of anything else that is in the window.
- Go to Step 8.
- If the Macro goes in the Workbook or ThisWorkbook, Click Here, otherwise continue to Step 8.
- Directly underneath your excel file called VBAProject(your file's name here), click the Microsoft Excel Objects folder icon to open that drop-down list.
- Then, at the bottom of the list that appears, double-click the ThisWorkbook text.
- A new window inside the Visual Basic Editor's window will appear. In this new window, paste the code for the macro. Make sure to paste this code underneath the last line of any other code which is already in the window.
- Go to Step 8.
- If the Macro goes in the Worksheet Code, Click Here, otherwise continue to Step 8.
- Directly underneath your excel file called VBAProject(your file's name here), click the Microsoft Excel Objects folder icon to open that drop-down list.
- Within the list that appears you will see every worksheet that is in that excel file. They will be listed as such: Sheet1(NAME OF SHEET HERE) and under that will be Sheet2(NAME OF SHEET HERE). Select the sheet in which you want the macro to run and double-click that sheet.
- A new window inside the Visual Basic Editor's window will appear. In this new window, paste the code for the macro. Make sure to paste this code underneath the last line of any other code which is already in the window.
- Repeat steps b and c for every sheet you want the macro to work in. Putting the macro in one sheet will not enable it for any other sheets in the workbook.
- Go to Step 8.
- Close the Microsoft Visual Basic Editor window and save the Excel file. When you close the Visual Basic Editor window, the regular Excel window will not close.
- You are now ready to run the macro.
Similar Helpful Excel Resources
Is it possible to create a UDF that will return the data validation input message title as a cell value?
Thanks
1. Select Data - Data Validation (in Data Tools Group).
2. Select the Input Message tab.
3. Fill the two text boxes Title and Input message and click OK.
hii
i am facing a problem for using a pop up box for the T column.i need it for all the rows and a pop up should display watever text present in the T colomn dynamically.
Conditions a
1.the pop up box shuld display for all rows > 42(since the data starts from that row).
2.it should be done using data validation-->input message-->title-->message(which is a pop up) method dynamically for all rows of T column.
3.The pop up boxes should be disabled so that user wont be able to modify anything in the pop up box.
4.The pop up box should display watever text present in the T column for each and every cell dynamically.
5.The looping should stop upto where the data ends dynamically and even the pop up boxes should be terminated when the data ends.
6.Basically It should show wateever data present in the T column in form of a pop up box using data validation control method but not comment box method dynamically.
I am attaching an excel sheet for a clear demonstration of a pop up box in T column for one cell.
I want the same to happen for every cell in T column upto where the data ends dynamically using data validation method.
I Dnt Want it in comment method please.
Thank you.
Hi everyone,
This looks like a really useful board, already got some good tips reading other threads but couldn't find this specific problem that I am having...
I have used Data Validation to place an Input Message on a bunch of fields in my worksheet. When I first set this the message always appeared right next to the cell to which it referred, then I accidently clicked on the input message and moved it. Now all input messages appear at the same place where I moved it to on the screen and I cannot find how to tie them back to cells.
Can anyone please help?
Many thanks,
Stuey.
p.s. sorry if this is a stupid noob question!
Sure I've seen this before somewhere, but I can't find it.
I have a leave chart that is used by a number of people. They input simple code to say where they are on a particular day. So AL for Annual Leave, NWD for Non-working Day etc. What I would like is for data validation to show a message explaining the code when a cell is selected, and the message would change depending on what hasd been input, but would show no message if an unexpexted entry had been input. So if 'AL' was input, the message would show "Annual Leave", if 'NWD' the message would show "Non-working Day", but if say 'Meeting' had been input, it would show nothing.
Hi,
What I require is the macro code (which I will be running from a button) that will pop up a message box asking the user for the password (which will be hidden in A1), after verifying the password is valid the user will be then prompted for the following information:-
(a) cell reference e.g. F1, G9, AB56 etc etc
(b) data to be input in cell range defined e.g. test data etc etc
When the user is happy with their choices and presses the OK button the data will be entered in to the cell selected.
Any help would be greatly appreciated
Many thanks in advance
Rob
N.B. Excel version 2003
Hi,
I have a shared spreadsheet that I have built with Input Messages on certain cells. The messages display beautifully on my machine but appear as a black box on other machines.
Anyone come across this before?
Cheers,
Kev
Whenever I create data validation input messages assigned to individual cells, the actual messages which come up (depending on the cell selected) all seem to appear in the same place. If I move the message box for one of the cells, guess what? I click on different cells with input messages assigned to them and they all move to that specific place. This is very frustrating. What can I do?
I have had need to amend my spreadsheet and the Input message in cells where data validation is enabled shows
"No need to enter details, figure is automatically calculated"
Prior to my fiddling with sheet this message came up adjacent to the cell that was clicked whereas now it is in the one spot which can be several cells away from the cell highlighted.
Validation was done to assist avoid deletion of formulae by others using spreadsheet.
Assistance appreciated once again.
Pedro
Hello,
I have been working with a Data validation input message box and it does not display my text correctly.
for example what should be displayed all on one line goes to two lines.
Is there a way to make the input message box bigger or a way to display my text all on one line.
Here is a example of my text:
12345 - Apples
4567 - Oranges
12348 - Pears
when i type the input message text it displays like this:
1234-
Apples
It jumps down a line instead of staying on one line
Thanks for the help
|
|
