Event Procedure - Data Validation List
 

Hello,

I would like to run a bit of code whenever the contents of either of two
cells is changed. For both of these cells, changes are done via a drop-list
(data validation)....

Any help getting started would be greatly appreciated!

Event Procedure - Data Validation List
 

this is standard event:

in the VBA editor
in the sheet's code
use the

Private Sub Worksheet_Change(ByVal Target As Range)
End Sub

'event

inside it, you can make a
if not ( intersect( Me.Range("A1:A2"),Target) is nothing) then
' ok my cells
end if


because Target is the changed cell
(something else as been entered inside it)

Event Procedure - Data Validation List
 

Hi Jeff,

You could use the Change event of the sheet where your cells are located.
Asuming that the cells are [A1] and [C1] try copying the below code into the
VBA module of the sheet in question:

Regards,
KL

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1,C1")) _
Is Nothing Then Exit Sub
MsgBox "Change detected!"
End Sub

Event Procedure - Data Validation List
 

Thanks for the response... Unfortunately, it doesn't fire. Here's what I used:


Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("D3,D5")) _
Is Nothing Then Exit Sub

Do something..............


End Sub


"KL" wrote:

Hi Jeff,

You could use the Change event of the sheet where your cells are located.
Asuming that the cells are [A1] and [C1] try copying the below code into the
VBA module of the sheet in question:

Regards,
KL

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1,C1")) _
Is Nothing Then Exit Sub
MsgBox "Change detected!"
End Sub

Event Procedure - Data Validation List
 

Hi Jeff,

Two things to check I guess:

1) Are you sure you put this code into the module of the worksheet where you
change the cells and not in the one of ThisWorkbook, other sheet , a normal
module (such as Module1), or even a module in another workbook?

2) Are the macros enabled?

3) Have you tried to change D3 or D5?

Regards,
KL


"JEFF" wrote in message
...
Thanks for the response... Unfortunately, it doesn't fire. Here's what I
used:


Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("D3,D5")) _
Is Nothing Then Exit Sub

Do something..............


End Sub


"KL" wrote:

Hi Jeff,

You could use the Change event of the sheet where your cells are located.
Asuming that the cells are [A1] and [C1] try copying the below code into
the
VBA module of the sheet in question:

Regards,
KL

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1,C1")) _
Is Nothing Then Exit Sub
MsgBox "Change detected!"
End Sub

Event Procedure - Data Validation List
 

Yes to all three

"KL" wrote:

Hi Jeff,

Two things to check I guess:

1) Are you sure you put this code into the module of the worksheet where you
change the cells and not in the one of ThisWorkbook, other sheet , a normal
module (such as Module1), or even a module in another workbook?

2) Are the macros enabled?

3) Have you tried to change D3 or D5?

Regards,
KL


"JEFF" wrote in message
...
Thanks for the response... Unfortunately, it doesn't fire. Here's what I
used:


Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("D3,D5")) _
Is Nothing Then Exit Sub

Do something..............


End Sub


"KL" wrote:

Hi Jeff,

You could use the Change event of the sheet where your cells are located.
Asuming that the cells are [A1] and [C1] try copying the below code into
the
VBA module of the sheet in question:

Regards,
KL

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1,C1")) _
Is Nothing Then Exit Sub
MsgBox "Change detected!"
End Sub

Event Procedure - Data Validation List
 

What version of excel are you running?

If it's xl97, take a look at Debra Dalgleish's warning:
http://www.contextures.com/xlDataVal08.html#Change

JEFF wrote:

Thanks for the response... Unfortunately, it doesn't fire. Here's what I used:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("D3,D5")) _
Is Nothing Then Exit Sub

Do something..............

End Sub

"KL" wrote:

Hi Jeff,

You could use the Change event of the sheet where your cells are located.
Asuming that the cells are [A1] and [C1] try copying the below code into the
VBA module of the sheet in question:

Regards,
KL

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1,C1")) _
Is Nothing Then Exit Sub
MsgBox "Change detected!"
End Sub




--

Dave Peterson

Event Procedure - Data Validation List
 

Hi Dave,

I'm using Excel 2003... I even tried pasting the below (found in Debra's
website) into a new workbook, but no luck.....



Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$A$2" Then
ActiveWindow.Zoom = 120
Else
ActiveWindow.Zoom = 100
End If
End Sub




"Dave Peterson" wrote:

What version of excel are you running?

If it's xl97, take a look at Debra Dalgleish's warning:
http://www.contextures.com/xlDataVal08.html#Change

JEFF wrote:

Thanks for the response... Unfortunately, it doesn't fire. Here's what I used:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("D3,D5")) _
Is Nothing Then Exit Sub

Do something..............

End Sub

"KL" wrote:

Hi Jeff,

You could use the Change event of the sheet where your cells are located.
Asuming that the cells are [A1] and [C1] try copying the below code into the
VBA module of the sheet in question:

Regards,
KL

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1,C1")) _
Is Nothing Then Exit Sub
MsgBox "Change detected!"
End Sub




--

Dave Peterson

Event Procedure - Data Validation List
 

other possibility:
are the events enabled ?

Application.EnableEvents = true

you can check the event macro is called by adding a
beep
or a
msgbox "ok"
at the very beginning of the macro

Event Procedure - Data Validation List
 

that's weird :-)

Are you sure you don't have the macro security set to High (menu
ToolsMacroSecurity)?

KL


"JEFF" wrote in message
...
Hi Dave,

I'm using Excel 2003... I even tried pasting the below (found in Debra's
website) into a new workbook, but no luck.....



Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$A$2" Then
ActiveWindow.Zoom = 120
Else
ActiveWindow.Zoom = 100
End If
End Sub




"Dave Peterson" wrote:

What version of excel are you running?

If it's xl97, take a look at Debra Dalgleish's warning:
http://www.contextures.com/xlDataVal08.html#Change

JEFF wrote:

Thanks for the response... Unfortunately, it doesn't fire. Here's what
I used:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("D3,D5")) _
Is Nothing Then Exit Sub

Do something..............

End Sub

"KL" wrote:

Hi Jeff,

You could use the Change event of the sheet where your cells are
located.
Asuming that the cells are [A1] and [C1] try copying the below code
into the
VBA module of the sheet in question:

Regards,
KL

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1,C1")) _
Is Nothing Then Exit Sub
MsgBox "Change detected!"
End Sub




--

Dave Peterson

Event Procedure - Data Validation List
 

Nope, on Low... I'm confused.

"KL" wrote:

that's weird :-)

Are you sure you don't have the macro security set to High (menu
ToolsMacroSecurity)?

KL


"JEFF" wrote in message
...
Hi Dave,

I'm using Excel 2003... I even tried pasting the below (found in Debra's
website) into a new workbook, but no luck.....



Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$A$2" Then
ActiveWindow.Zoom = 120
Else
ActiveWindow.Zoom = 100
End If
End Sub




"Dave Peterson" wrote:

What version of excel are you running?

If it's xl97, take a look at Debra Dalgleish's warning:
http://www.contextures.com/xlDataVal08.html#Change

JEFF wrote:

Thanks for the response... Unfortunately, it doesn't fire. Here's what
I used:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("D3,D5")) _
Is Nothing Then Exit Sub

Do something..............

End Sub

"KL" wrote:

Hi Jeff,

You could use the Change event of the sheet where your cells are
located.
Asuming that the cells are [A1] and [C1] try copying the below code
into the
VBA module of the sheet in question:

Regards,
KL

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1,C1")) _
Is Nothing Then Exit Sub
MsgBox "Change detected!"
End Sub




--

Dave Peterson

Event Procedure - Data Validation List
 

how about the "do something..." of your code? Can you show the full code
please? Have you tried just a msgbox (as in my original code)?

KL


"JEFF" wrote in message
...
Nope, on Low... I'm confused.

"KL" wrote:

that's weird :-)

Are you sure you don't have the macro security set to High (menu
ToolsMacroSecurity)?

KL


"JEFF" wrote in message
...
Hi Dave,

I'm using Excel 2003... I even tried pasting the below (found in
Debra's
website) into a new workbook, but no luck.....



Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$A$2" Then
ActiveWindow.Zoom = 120
Else
ActiveWindow.Zoom = 100
End If
End Sub




"Dave Peterson" wrote:

What version of excel are you running?

If it's xl97, take a look at Debra Dalgleish's warning:
http://www.contextures.com/xlDataVal08.html#Change

JEFF wrote:

Thanks for the response... Unfortunately, it doesn't fire. Here's
what
I used:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("D3,D5")) _
Is Nothing Then Exit Sub

Do something..............

End Sub

"KL" wrote:

Hi Jeff,

You could use the Change event of the sheet where your cells are
located.
Asuming that the cells are [A1] and [C1] try copying the below
code
into the
VBA module of the sheet in question:

Regards,
KL

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1,C1")) _
Is Nothing Then Exit Sub
MsgBox "Change detected!"
End Sub




--

Dave Peterson

Event Procedure - Data Validation List
 

I started over with a clean workbook and created drop lists in A1 and C1. I
pasted your code into the worksheet (below).... No luck.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1,C1")) _
Is Nothing Then Exit Sub
MsgBox "Change detected!"
End Sub






"KL" wrote:

how about the "do something..." of your code? Can you show the full code
please? Have you tried just a msgbox (as in my original code)?

KL


"JEFF" wrote in message
...
Nope, on Low... I'm confused.

"KL" wrote:

that's weird :-)

Are you sure you don't have the macro security set to High (menu
ToolsMacroSecurity)?

KL


"JEFF" wrote in message
...
Hi Dave,

I'm using Excel 2003... I even tried pasting the below (found in
Debra's
website) into a new workbook, but no luck.....



Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$A$2" Then
ActiveWindow.Zoom = 120
Else
ActiveWindow.Zoom = 100
End If
End Sub




"Dave Peterson" wrote:

What version of excel are you running?

If it's xl97, take a look at Debra Dalgleish's warning:
http://www.contextures.com/xlDataVal08.html#Change

JEFF wrote:

Thanks for the response... Unfortunately, it doesn't fire. Here's
what
I used:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("D3,D5")) _
Is Nothing Then Exit Sub

Do something..............

End Sub

"KL" wrote:

Hi Jeff,

You could use the Change event of the sheet where your cells are
located.
Asuming that the cells are [A1] and [C1] try copying the below
code
into the
VBA module of the sheet in question:

Regards,
KL

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1,C1")) _
Is Nothing Then Exit Sub
MsgBox "Change detected!"
End Sub




--

Dave Peterson

Event Procedure - Data Validation List
 

Hi Jeff,

PMFBI, but have you checked that events are not disabled as suggested by
abcd.

I ask only because I see no acknowledgement of this and, given the descibed
scenario, it seems an obvious point to check.


---
Regards,
Norman



"JEFF" wrote in message
...
I started over with a clean workbook and created drop lists in A1 and C1.
I
pasted your code into the worksheet (below).... No luck.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1,C1")) _
Is Nothing Then Exit Sub
MsgBox "Change detected!"
End Sub






"KL" wrote:

how about the "do something..." of your code? Can you show the full code
please? Have you tried just a msgbox (as in my original code)?

KL


"JEFF" wrote in message
...
Nope, on Low... I'm confused.

"KL" wrote:

that's weird :-)

Are you sure you don't have the macro security set to High (menu
ToolsMacroSecurity)?

KL


"JEFF" wrote in message
...
Hi Dave,

I'm using Excel 2003... I even tried pasting the below (found in
Debra's
website) into a new workbook, but no luck.....



Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$A$2" Then
ActiveWindow.Zoom = 120
Else
ActiveWindow.Zoom = 100
End If
End Sub




"Dave Peterson" wrote:

What version of excel are you running?

If it's xl97, take a look at Debra Dalgleish's warning:
http://www.contextures.com/xlDataVal08.html#Change

JEFF wrote:

Thanks for the response... Unfortunately, it doesn't fire.
Here's
what
I used:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("D3,D5")) _
Is Nothing Then Exit Sub

Do something..............

End Sub

"KL" wrote:

Hi Jeff,

You could use the Change event of the sheet where your cells
are
located.
Asuming that the cells are [A1] and [C1] try copying the below
code
into the
VBA module of the sheet in question:

Regards,
KL

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1,C1")) _
Is Nothing Then Exit Sub
MsgBox "Change detected!"
End Sub




--

Dave Peterson

Event Procedure - Data Validation List
 

Hi Norman,

Yes... Here is how I tried to incorporate it:


Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = True
If Intersect(Target, Range("A1,C1")) _
Is Nothing Then Exit Sub
MsgBox "Change detected!"
End Sub



"Norman Jones" wrote:

Hi Jeff,

PMFBI, but have you checked that events are not disabled as suggested by
abcd.

I ask only because I see no acknowledgement of this and, given the descibed
scenario, it seems an obvious point to check.


---
Regards,
Norman



"JEFF" wrote in message
...
I started over with a clean workbook and created drop lists in A1 and C1.
I
pasted your code into the worksheet (below).... No luck.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1,C1")) _
Is Nothing Then Exit Sub
MsgBox "Change detected!"
End Sub






"KL" wrote:

how about the "do something..." of your code? Can you show the full code
please? Have you tried just a msgbox (as in my original code)?

KL


"JEFF" wrote in message
...
Nope, on Low... I'm confused.

"KL" wrote:

that's weird :-)

Are you sure you don't have the macro security set to High (menu
ToolsMacroSecurity)?

KL


"JEFF" wrote in message
...
Hi Dave,

I'm using Excel 2003... I even tried pasting the below (found in
Debra's
website) into a new workbook, but no luck.....



Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$A$2" Then
ActiveWindow.Zoom = 120
Else
ActiveWindow.Zoom = 100
End If
End Sub




"Dave Peterson" wrote:

What version of excel are you running?

If it's xl97, take a look at Debra Dalgleish's warning:
http://www.contextures.com/xlDataVal08.html#Change

JEFF wrote:

Thanks for the response... Unfortunately, it doesn't fire.
Here's
what
I used:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("D3,D5")) _
Is Nothing Then Exit Sub

Do something..............

End Sub

"KL" wrote:

Hi Jeff,

You could use the Change event of the sheet where your cells
are
located.
Asuming that the cells are [A1] and [C1] try copying the below
code
into the
VBA module of the sheet in question:

Regards,
KL

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1,C1")) _
Is Nothing Then Exit Sub
MsgBox "Change detected!"
End Sub




--

Dave Peterson

Event Procedure - Data Validation List
 

Hi Jeff,

Reading the thread in its entirety, it would appear that:

Events are enabled
Security is set to low
*No* event code is running

I would suggest that you send me a copy of your workbook, but I doubt that
it would help, given the above. Especially as you have experienced an
analagpus problem in another, virgin, workbook.

Ok, to verify the last of the three statements above,

In a new worbook, right-click the Sheet tab, and paste following, which
represents the simplest possible event code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MsgBox "Events ARE working!"
End Sub

Alt-F11 , and sequentially select two different cells. Do you see the
message?


---
Regards,
Norman



"JEFF" wrote in message
...
Hi Norman,

Yes... Here is how I tried to incorporate it:


Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = True
If Intersect(Target, Range("A1,C1")) _
Is Nothing Then Exit Sub
MsgBox "Change detected!"
End Sub



"Norman Jones" wrote:

Hi Jeff,

PMFBI, but have you checked that events are not disabled as suggested by
abcd.

I ask only because I see no acknowledgement of this and, given the
descibed
scenario, it seems an obvious point to check.


---
Regards,
Norman

Event Procedure - Data Validation List
 

And you have this code behind the correct worksheet module?

JEFF wrote:

Hi Norman,

Yes... Here is how I tried to incorporate it:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = True
If Intersect(Target, Range("A1,C1")) _
Is Nothing Then Exit Sub
MsgBox "Change detected!"
End Sub

"Norman Jones" wrote:

Hi Jeff,

PMFBI, but have you checked that events are not disabled as suggested by
abcd.

I ask only because I see no acknowledgement of this and, given the descibed
scenario, it seems an obvious point to check.


---
Regards,
Norman



"JEFF" wrote in message
...
I started over with a clean workbook and created drop lists in A1 and C1.
I
pasted your code into the worksheet (below).... No luck.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1,C1")) _
Is Nothing Then Exit Sub
MsgBox "Change detected!"
End Sub






"KL" wrote:

how about the "do something..." of your code? Can you show the full code
please? Have you tried just a msgbox (as in my original code)?

KL


"JEFF" wrote in message
...
Nope, on Low... I'm confused.

"KL" wrote:

that's weird :-)

Are you sure you don't have the macro security set to High (menu
ToolsMacroSecurity)?

KL


"JEFF" wrote in message
...
Hi Dave,

I'm using Excel 2003... I even tried pasting the below (found in
Debra's
website) into a new workbook, but no luck.....



Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$A$2" Then
ActiveWindow.Zoom = 120
Else
ActiveWindow.Zoom = 100
End If
End Sub




"Dave Peterson" wrote:

What version of excel are you running?

If it's xl97, take a look at Debra Dalgleish's warning:
http://www.contextures.com/xlDataVal08.html#Change

JEFF wrote:

Thanks for the response... Unfortunately, it doesn't fire.
Here's
what
I used:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("D3,D5")) _
Is Nothing Then Exit Sub

Do something..............

End Sub

"KL" wrote:

Hi Jeff,

You could use the Change event of the sheet where your cells
are
located.
Asuming that the cells are [A1] and [C1] try copying the below
code
into the
VBA module of the sheet in question:

Regards,
KL

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1,C1")) _
Is Nothing Then Exit Sub
MsgBox "Change detected!"
End Sub




--

Dave Peterson











--

Dave Peterson

Event Procedure - Data Validation List
 

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = True


:-)

but, jeff, how could your code set the events on if you start events
only after an event is called - you must set it on true in a separate
sub, and ask it alone, and then be sure the events are on !

:-)

also be carreful, sometimes you are using the wrong event:
you use SelectionChange (Debra's) and not Change

The possibility are only he

- events are not activated
and/or
- the code is in the wrong sheet code (double clic on the VBA name on
the left pannel, not only clic but double clic)
- you do not change the wanted cell by typing something new inside it
- you do not do what you said
- Windows need a restart (last hand, if really sure of everything else)

This code is supposed to work and works with other people. Check again
everything and then send the book in attachement

Event Procedure - Data Validation List
 

Hi Jeff,

To definitively resolve the quetion of events being enabled, paste the
following into a standard module and run it:

Sub ForMyPeaceOfMind

Application.EnableEvents = True

End Sub

---
Regards,
Norman



"Norman Jones" wrote in message
...
Hi Jeff,

Reading the thread in its entirety, it would appear that:

Events are enabled
Security is set to low
*No* event code is running

I would suggest that you send me a copy of your workbook, but I doubt that
it would help, given the above. Especially as you have experienced an
analagpus problem in another, virgin, workbook.

Ok, to verify the last of the three statements above,

In a new worbook, right-click the Sheet tab, and paste following, which
represents the simplest possible event code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MsgBox "Events ARE working!"
End Sub

Alt-F11 , and sequentially select two different cells. Do you see the
message?


---
Regards,
Norman



"JEFF" wrote in message
...
Hi Norman,

Yes... Here is how I tried to incorporate it:


Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = True
If Intersect(Target, Range("A1,C1")) _
Is Nothing Then Exit Sub
MsgBox "Change detected!"
End Sub



"Norman Jones" wrote:

Hi Jeff,

PMFBI, but have you checked that events are not disabled as suggested by
abcd.

I ask only because I see no acknowledgement of this and, given the
descibed
scenario, it seems an obvious point to check.


---
Regards,
Norman

Event Procedure - Data Validation List
 

It worked! As God is my witness, I will never go ....... Thanks!

"Norman Jones" wrote:

Hi Jeff,

To definitively resolve the quetion of events being enabled, paste the
following into a standard module and run it:

Sub ForMyPeaceOfMind

Application.EnableEvents = True

End Sub

---
Regards,
Norman



"Norman Jones" wrote in message
...
Hi Jeff,

Reading the thread in its entirety, it would appear that:

Events are enabled
Security is set to low
*No* event code is running

I would suggest that you send me a copy of your workbook, but I doubt that
it would help, given the above. Especially as you have experienced an
analagpus problem in another, virgin, workbook.

Ok, to verify the last of the three statements above,

In a new worbook, right-click the Sheet tab, and paste following, which
represents the simplest possible event code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MsgBox "Events ARE working!"
End Sub

Alt-F11 , and sequentially select two different cells. Do you see the
message?


---
Regards,
Norman



"JEFF" wrote in message
...
Hi Norman,

Yes... Here is how I tried to incorporate it:


Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = True
If Intersect(Target, Range("A1,C1")) _
Is Nothing Then Exit Sub
MsgBox "Change detected!"
End Sub



"Norman Jones" wrote:

Hi Jeff,

PMFBI, but have you checked that events are not disabled as suggested by
abcd.

I ask only because I see no acknowledgement of this and, given the
descibed
scenario, it seems an obvious point to check.


---
Regards,
Norman