I'm an Access Programmer, using automation to populate Excel data.

From within Access, as part of an AfterUpdate, I have...
where DSupply, GPrev and GDiff are Controls on an Access Form.

Will Excel allow Events on Named Cells ??

TIA - Bob

If Not IsNull(DSupply) Then
'7/2/09 - for meter turnover (Jeff said all meters have min of 4-characters)
If (GDiff < 0 And Abs(GDiff) 9000) Then
If Len(GPrev) = 4 Then
I = (10000 - GPrev)
ElseIf Len(GPrev) = 5 Then
I = (100000 - GPrev)
ElseIf Len(GPrev) = 6 Then
I = (1000000 - GPrev)
ElseIf Len(GPrev) = 74 Then
I = (10000000 - GPrev)
End If
GDiff = DSupply + I
Else
GDiff = (DSupply - GPrev)
End If
If GDiff < 0 Then
GDiff.BackColor = 255
Else
GDiff.BackColor = 16777215
End If
End If

It's not at all clear to me what problem you are having with your code,
but beyond that, Excel does have a Worksheet_Change event that includes
a parameter that is the range (cell) that changed. You could test to
see if a particular named range has the same address as the Target range
and run code if it is...


--
jamescox
------------------------------------------------------------------------
jamescox's Profile: http://www.thecodecage.com/forumz/member.php?userid=449
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=112280

Thank you.

Within the Worksheet_Change, would that Procedure cover "all possible"
changes in cells? For the Excel file we using, it's "only" 72 cells, but
that's a bunch of coding in a single Event (Worksheet_Change)?

In Access, I'll run "AfterUpdate" on separate Controls (on the Access Form,
there's only 12 Controls for 6 different Day Periods...then those 72 values
are automated into Excel).

Rather than have a simple in Excel...
=IF(LEN(F8)=0,"",F8-E8) in Cell F10, we need to run code similar to the
"AfterUpdate" I had in my initial Post.

Yoour thoughts?

"jamescox" wrote:


It's not at all clear to me what problem you are having with your code,
but beyond that, Excel does have a Worksheet_Change event that includes
a parameter that is the range (cell) that changed. You could test to
see if a particular named range has the same address as the Target range
and run code if it is...


--
jamescox
------------------------------------------------------------------------
jamescox's Profile: http://www.thecodecage.com/forumz/member.php?userid=449
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=112280

If your 72 cells are in a single row or column (or rectangular range of
cells), you can name the range and then use the Intersect function to
check if the changed cell is in the named range.

If your 72 cells aren't in a simple row, column or rectangular array,
and if there the calculation you need to perform is the same, regardless
of which cell changes you might consider a Case Select with the
calculation in its own function or subroutine - which would reduce the
amount of code to write.

Also, you might (with adequate comments added to the posts in this
thread which indicate you have moved your question) cross-post your
problem over to the Excel VBA Programming sub-group where some
higher-power experts hang out...


--
jamescox
------------------------------------------------------------------------
jamescox's Profile: http://www.thecodecage.com/forumz/member.php?userid=449
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=112280