Display an Alert popup.

X

Xluser@work

Please can someone assist with the following?

At present I have a data validation list in Excel (which is working fine).
However, when a user selects one of the options in the list I need Excel to
show a popup with a warning message (as this option in the list works
differently to the others). How can I make Excel do this? I can make a popup
in Excel using datavalidation but since this cell already has this (for the
list) how can I replicate again?

Any help gratefully received.

TIA.
 
A

Alex Guardiet

Hi,

The code is quite simple, but placing the code in the right place will be
more complicated if you're not used to VBA. Let's start with the code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$D$1" Then MsgBox ("cell selection")
End Sub

I'm assuming you want the message to appear when the user selects a specific
cell. You have to replace the "$D$1" bit for the cell that will trigger the
event. In my example code the event occurs when the user selects cell D1
(replace the D and the 1 but leave the dollar signs in).
Now for the placing of the code: Go to the Visual Basic Editor (Tools >
Macros > Visual Basic Editor), then select the sheet where you have your data
(where the event will happen) from the Project window (on the upper left-hand
side). It is important that you select the correct sheet and that you double
click to make sure you have selected it. Then simply paste the code. It
should work.

Regards,

Alex
 
T

Tom Ogilvy

You would have to use the Change event in the worksheet and test for the
cell with the validation being the Target in the event. Then, if the Target
has the value you are interested in you can have the code pop up a message
box.

http://www.cpearson.com/excel/events.htm
Chip Pearson's page on events if you are not familiar with them.
 
T

Tom Ogilvy

You want to use the Change event, not the selectionchange event:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$D$1" And Target.Value = "hello" Then _
MsgBox ("cell selection")
End Sub

The change event does not work in Excel 97 if you are using a from a range.
 
T

Tom Ogilvy

The OP wants to put up the message after the user makes a selection in the
validation dropdown box if the selection has a certain value. I believe he
has related that to you twice.

So the Change event appears to be the appropriate event.

Noone said your code didn't work - it just doesn't work to achieve the
functionality described by the OP - at least not as I understand the
requirement.
whereas my version makes sure this change occurs when the user clicks
on the cell and chooses an option.
Click to expand...

Yours fires before the user selects an option. As far as a change, you code
puts up a message box - it doesn't change anything. If the OP wants a
message when the user selects the cell, that is inherently provided by
Data=>Validation itself with no additional code required.
 
Top