Absolutely beautiful - It works !!!

Thank you - Bob

"JLatham" wrote:

Bob, in reading your original question, I interpret your need to be to find 2
things:
#1 - value of the cell 1 column to the left of the active cell, on the same
row (CNine), and
#2 - value of the cell 2 rows down from the active cell in the same column
(C_D_Nine).
Perhaps the syntax I show below will be a little more understandable to you.

You could change all references to Range("CNine") to
t.Offset(0,-1)
for example,
t - Range("CNine").Value < 0
would become
t - t.Offset(0,-1).Value < 0

and similarly references to Range("C_D_Nine") would become t.Offset(2,0)

By the way, if for some reason you ever select a cell in column A, the above
code would fail because there is no column to the left of column A! So right
at the start of the routine, I'd put in a safety valve:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 1 Then
Exit Sub ' no column to the left of column A
End If
Set t = Target
... your code continues

Hope this helps.


"Bob Barnes" wrote:

Chip - thank you.

I was going to use both Worksheet_Change & Workbook_Open in an Excel file
that will have only one Worksheet. The Excel file receives data, via
automation, from an Access database.

On Workbook_Open, I was going to use (as an example..using 72 Names)
Range("DNine").Select and run a Subroutine where "DNine" would be the
ActiveCell.

I could populate all the 72 Excel Cells from Access automation, and also
have a
Worksheet_Change should the User change one of the designated 72 values.

We discussed trying to Lock Excel, but there is software that will find
Passwords (I've had to use that per the Client's need for an unknown
Password) to allow a User to "unlock" modules.

Besides all the data is kept in Access and run daily to an Excel file which
is attached to an automated Lotus Note. So, changing the data in Excel would
only appear to be a change when it's not. The Mgrs receiving the Excel file
attached to a Lotus Note would know that.

We could just let "nothing" occur if one of the 72 Cells is changed. The
formula I listed is to calculate differences in meter readings, and also when
those meters rollover from something like 9999 to 0003.

I'll try using your...
Set R = R.Worksheet.Cells(Target.Row, _
Range("C_D_Nine").Column - 1)
..below, but I'm not sure how that works.

I'm an Access Programmer and may just automate the 72 cell calculations from
within Access...although I'd like to learn more about Excel.

Thank you again - Bob

"Chip Pearson" wrote:

I would strongly recommend that you not use ActiveCell in any
calculation, because you cannot predict where the active cell will be,
let alone what worksheet and workbook might be active when Excel
decides that it is time to calculate. If you need to get a reference
to the cell in which the function was called, use either
Application.Caller or Application.ThisCell. These will return a Range
object to points to the cell in which the function was called.

From you use of the word "Target", I'm guessing that you're doing
something with the Change event. Target is a pointer to the cell(s)
that were change. For a Range object, Value is the default property,
so you can omit it, though I think that you should include it. With
that, you can use

Private Sub Worksheet_Change(ByVal Target As Range)
Dim R As Range
On Error GoTo ErrH:
If Target.Cells.Count 1 Then
Exit Sub
End If
If Application.Intersect(Target, Range("C_D_Nine")) _
Is Nothing Then
Exit Sub
End If
Application.EnableEvents = False
Set R = R.Worksheet.Cells(Target.Row, _
Range("C_D_Nine").Column - 1)
R.Value = 1234
ErrH:
Application.EnableEvents = True
End Sub

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)




On Mon, 6 Jul 2009 17:51:01 -0700, Bob Barnes
wrote:

I've read thru Archive Posts here that using "Set t = Target" (below) will
display the value for the Active Cell.

I exchanged ideas earlier today w/ another Forum guy and he got me thinking
on what I need precisely.

After using automation from Access-to-Excel, I have 72 Cells which will look
for the ActiveCell and do a calculation where, for example, the Cell Name of
"CNine" is in the same row as the ActiveCell, but always one column to the
left of the ActiveCell.
...and...
the Cell Name of "C_D_Nine" will always in the same Column of the ActiveCell
and always 2 Rows below the ActiveCell.

How can I change the syntax (below) for all
Range("CNine").Value
...and..
Range("C_D_Nine").Value
...tio something else ?? TIA - Bob

If I can get this, the solution will be found.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set t = Target
If (t - Range("CNine").Value < 0) _
And Abs(t - Range("CNine").Value) 9000) Then
If Len(Range("CNine")) = 4 Then
I = (10000 - Range("CNine").Value)
ElseIf Len(Range("CNine")) = 5 Then
I = (100000 - Range("CNine").Value)
ElseIf Len(Range("CNine")) = 6 Then
I = (1000000 - Range("CNine").Value)
ElseIf Len(Range("CNine")) = 74 Then
I = (10000000 - Range("CNine").Value)
End If
Range("C_D_Nine").Value = t + I
Else
Range("C_D_Nine").Value = (t - Range("CNine").Value)
End If
End If
End Sub