Showing messages in Excel

Today’s question is about displaying prompts in Excel.

Miranda asks:

How can I show a message when I click on a particular cell?

Well, there are three ways that I can think of. I call them standard, intermediate, and advanced.

How many ways can you think of?

Why not watch the video below, and see if you are right.

(If you want the video in a bigger window, then the “full screen” button is on the bottom-right of the video.

 

Let’s have a look at these three methods:

Comments are the first and more obvious way to add comments:

  • Adding, editing or deleting comments are easy – just right-hand click with your mouse on a cell.
  • If a cell has a comment, there is a triangle in the top-right hand corner of the cell.
  • Comments can also be printed – see the fourth tab in the Page Setup dialog box.
  • The downside – comments are the most obvious way to add comments – and for someone else to edit them.

Data validation can be a clever way of adding messages:

  • This can be added using Data – Data Validation – Data Validation…
  • This sounds like an odd way of adding a message – what has data validation got to do with showing prompts?
  • However, the second tab is called “Input Message” – this allows you to “Show input message when cell is selected”.
  • Just add a Title and Input message, and it will be shown whenever you click on a cell.
  • You don’t have to do any Data Validation – so you can leave the first cell on “Allow: Any Value”.
  • As a less obvious method, it will be less likely that someone else will edit your text.

The third way is using the MsgBox statement in VBA to display messages as well.

  • All you have to do is put, in the right bit of code.

MsgBox “This is my message”

  • However, as it is a macro, you will have to save the file as a .xlsm or .xlsb – you can’t save it as a .xlsx file.
  • Additionally, whoever opens the file will have to enable macros to get the MsgBox to display.

So these are my three ways. If you can think of any other ways, why not let me know in the comments section below.

Thank you very much for reading, and please look at answers to other questions.


 

If you would like to know more about Excel, why not take one of my courses – and using the coupon code SQLINTRO will reduce the price to only $10 per course (down from $25).

My course Microsoft Excel – go from Beginner to Specialist certificate takes you through the first 5 levels of Excel functions in a little over 11 hours long. Data Validation, as used in the video above, is in Level 3 Section 1.

Alternatively, my course Excel VBA (Visual Basic for Applications) – An Introduction takes you through the first steps of programming in Excel, such as the MsgBox demonstrated in the video.


Leave a Reply

Your email address will not be published. Required fields are marked *