Code won't kick off

  • Thread starter Joe_Hunt via OfficeKB.com
  • Start date
J

Joe_Hunt via OfficeKB.com

I realize this is probably simple and I'm missing the obvious, but I can't
see what's going on. I have coding in a couple of sheets in my workbook that
should kick off various instructions when a particular cell is changed, but
it's not doing it although it used to. The security settings are right (set
on low actually). Can anybody give me an idea of what to look at? Here's the
coding in one of the worksheets if that helps:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "D1" Then
Application.ScreenUpdating = False
ExceptionsStart
Application.ScreenUpdating = True
End If
End Sub

In the above example when the value in cell D1 is changed it should run the
macro "ExceptionsStart" which leads into others as that one finishes. I do
appreciate any help.
 
D

Dave Peterson

The .Address will return something like $A$1--with those dollar signs.

So you can use:
If Target.Address = "$D$1" Then
or
If Target.Address(0,0) = "D1" Then
or the one I like:

if target.cells.count > 1 then exit sub 'singl cell only
if intersect(target, me.range("D1")) is nothing then exit sub
 
A

Ardus Petus

The default result of .Address method of Range object is an absolute
address,
so you should write :

If Target.Address = "$D$1"

HTH
 
J

Joe_Hunt via OfficeKB.com

I figured it would be something easy. Thanks to all three of you!

Ardus said:
The default result of .Address method of Range object is an absolute
address,
so you should write :

If Target.Address = "$D$1"

HTH
--
AP
I realize this is probably simple and I'm missing the obvious, but I can't
see what's going on. I have coding in a couple of sheets in my workbook
Click to expand...
[quoted text clipped - 20 lines]
macro "ExceptionsStart" which leads into others as that one finishes. I do
appreciate any help.
Click to expand...
Click to expand...
 
J

Joe_Hunt via OfficeKB.com

Well, it did work, now it's not working again. Maybe my computer is mad at me.
..
 
D

Dave Peterson

If you close excel and reopen it (and reopen your workbook), does it work again?

If yes, then I bet you turn off events somewhere in your code and forget to turn
them back on.

application.enableevents = false
'lots of code

And if you don't have:
application.enableevents = true

Then the excel won't be looking for any more changes (or any triggers that fire
events).
 
J

Joe_Hunt via OfficeKB.com

I don't see where it was turned off and then not turned back on, but I re-
enabled events at the end of the auto-open and everything seems to work now.
Many thanks!

Dave said:
If you close excel and reopen it (and reopen your workbook), does it work again?

If yes, then I bet you turn off events somewhere in your code and forget to turn
them back on.

application.enableevents = false
'lots of code

And if you don't have:
application.enableevents = true

Then the excel won't be looking for any more changes (or any triggers that fire
events).
Well, it did work, now it's not working again. Maybe my computer is mad at me.
.
Click to expand...
[quoted text clipped - 21 lines]
Click to expand...
 
G

Gord Dibben

Is possible that events became disabled due to an error and you have no plan
to re-enable them.

Open Immediate Window and copy this line and hit enter to re-enable events.

Application.EnableEvents = True

Now paste this code into the sheet module in place of your original.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo endit
Application.EnableEvents = False
If Target.Address = "$D$1" Then
Application.ScreenUpdating = False
ExceptionsStart
Application.ScreenUpdating = True
End If
endit:
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP
 
D

Dave Peterson

Search for .enableevents
and that may help.



Joe_Hunt via OfficeKB.com said:
I don't see where it was turned off and then not turned back on, but I re-
enabled events at the end of the auto-open and everything seems to work now.
Many thanks!

Dave said:
If you close excel and reopen it (and reopen your workbook), does it work again?

If yes, then I bet you turn off events somewhere in your code and forget to turn
them back on.

application.enableevents = false
'lots of code

And if you don't have:
application.enableevents = true

Then the excel won't be looking for any more changes (or any triggers that fire
events).
Well, it did work, now it's not working again. Maybe my computer is mad at me.
.
Click to expand...
[quoted text clipped - 21 lines]
Click to expand...
Click to expand...
 
J

Joe_Hunt via OfficeKB.com

Got it. Thanks again!

Dave said:
Search for .enableevents
and that may help.
I don't see where it was turned off and then not turned back on, but I re-
enabled events at the end of the auto-open and everything seems to work now.
Click to expand...
[quoted text clipped - 22 lines]
Click to expand...
 
Top