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.

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

"Xluser@work" wrote:

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.

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.

--
Regards,
Tom Ogilvy


"Xluser@work" wrote in message
...
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.

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.

--
Regards,
Tom Ogilvy



"Xluser@work" wrote in message
...
Thanks Alex.

However, rather than the alert when the user simply goes into the cell, I
want it to display the alert if they choose a particular option from the
list
and to do nothing if they choose any other cell.

Are you able to help with this?

Matthew

"Alex Guardiet" wrote:

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

"Xluser@work" wrote:

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.

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.

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.

--
Regards,
Tom Ogilvy



"Alex Guardiet" wrote in message
...
Hi Tom,

My code works, I've tried it.
Your version works too, but it checks for a change in the contents of the
cell, whereas my version makes sure this change occurs when the user
clicks
on the cell and chooses an option.

Regards

Alex

"Tom Ogilvy" wrote:

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.

--
Regards,
Tom Ogilvy



"Xluser@work" wrote in message
...
Thanks Alex.

However, rather than the alert when the user simply goes into the
cell, I
want it to display the alert if they choose a particular option from
the
list
and to do nothing if they choose any other cell.

Are you able to help with this?

Matthew

"Alex Guardiet" wrote:

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

"Xluser@work" wrote:

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.