Creating Pop up Box in Excel

[kaine]

Hello,
I have a spreadsheet that is set up like a questionnaire.

Within some of the cells, I want to allow a pop up box to appear when you click on a particular word that is highlighted.... like "Click here" (similar to a hyperlink type set up). Within this pop up box, I want to provide certain instructions and examples.

I assume that I am going to have to apply macros and then enter some sort of code. Are there any smarties out there that have a simple cut and paste code that I can use and alter to get this thing working?

Your help would be greatly appreciated.

Thanks,
Kaine

 

[Tom Urtis]

Well, to get things working as you say, how about this syntax for a Selection event. You did not say what range of cells in the sheet might contain "Click here" or any other words you have in mind, so if you have...

- "Click here"
- "Something"
- "Anything"

...as possible trigger values, then try this:

Right click on your sheet tab, left click on View Code, and paste the following procedure into the large white area that is the worksheet module. Press Alt+Q to return to the worksheet.


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Select Case Target.Value

Case "Click here", "Something", "Anything"
MsgBox _
"You clicked on cell " & Target.Address(0, 0) & "," & vbCrLf & _
"which contains the value ''" & Target.Value & "''." & vbCrLf & vbCrLf & _
"Whatever else you want to say goes here.", 64, "Information alert !!"

End Select
End Sub


Then, modify the code for actual trigger values, and the prompt text in the message box for whatever you really want to say.

 

[Oorang]

Right click on the cell and select "insert comment". Type your instructions in the box. When the mouse hovers ovwer the cell your instructions will appear. When it leaves they will dissappear.

 

[kaine]

Thanks very much for that Tom. I really appreciate your help.
I am almost there. I have pasted the code into the worksheet module, but I cannot seem to get it to activate. I have begun by using the "Click here" just to try to get it working, and I will alter output values later, but what do I actually need to do in the worksheet itself to make the "Click here" active?

 

[Nimrod]

Hello kaine:
Excel has 2 "pop-up" instruction windows built in :
A: Comments
Right click on cell and select "insert comments"

B: Validation Wizard:
Tools...Data ...Validation.... "Input Msssage" Tab

One difference between these 2 methods is that with Comments you just move the mouse over the cell and the message appears, with validation comments you actually click the cell .

The nice thing about both approaches is they do not require macros to be activated for them to work. :wink:

 

[Tom Urtis]

Thanks very much for that Tom. I have pasted the code into the worksheet module, but I cannot seem to get it to activate. I have begun by using the "Click here" just to try to get it working, and I will alter output values later, but what do I actually need to do in the worksheet itself to make the "Click here" active?

It sounds like you did what you were supposed to do. Make sure the cell's value is exactly "Click here" (without the quotes, case sensitive, one space in between "Click" and "here", only that value in the cell, not "Click here to see a message", etc). With the code I posted placed where I said and where it sounds like you put it (make very sure it is in the same sheet module as the sheet with these values) then just select a few cells, and when you select the cell that contains the value "Click here" the pop-up message box will appear.

Note that using VBA as like this allows you with greater flexibility for changing cell values on the fly without needing to transfer comments, data validation settings, etc, and you can make a more substantial pop-up (the message box) appear, where no other action can continue until the user clicks OK to acknowledge that message box, so you know they have seen and dealt with the message.