Another Try - Calculate from ActiveCell

[Bob Barnes]

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

 

[Chip Pearson]

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_Nime").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)

 

[Bob Barnes]

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

 

[JLatham]

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]

Absolutely beautiful - It works !!!

Thank you - Bob

 

[Bob Barnes]

I'm going to run this code in Workbook_Open instead of Worksheet_Change (it
does work in Worksheet_Change). We've decided the Excel file will be
essentially only a "snapshot" as all data will be maintained in the Access
Database.

So..Workbook_open will include code for each of the 72 Cells, IE...
Range("DNine").Select
Call SeeDiff
Range("ENine").Select
Call SeeDiff
.....

Private Sub SeeDiff()
Set t = Application.Caller <---No "Target" here...how do I set the
"ActiveCell"?
.....I tried Application.Caller & Application.ThisCell suggested in this
thread by Chip...
....but that didn't work............................
If ((t - t.Offset(0, -1).Value < 0) _
And Abs(t - t.Offset(0, -1).Value) > 9000) Then
If Len(t.Offset(0, -1)) = 4 Then
I = (10000 - t.Offset(0, -1).Value)
ElseIf Len(t.Offset(0, -1)) = 5 Then
I = (100000 - t.Offset(0, -1).Value)
ElseIf Len(t.Offset(0, -1)) = 6 Then
I = (1000000 - t.Offset(0, -1).Value)
ElseIf Len(t.Offset(0, -1)) = 7 Then
I = (10000000 - t.Offset(0, -1).Value)
End If
t.Offset(2, 0).Value = SeeDiff
Else
t.Offset(2, 0).Value = SeeDiff
End If
End Sub

 

[JLatham]

You'll need to specify the worksheet in question within the Workbook_Open()
event.

Private Sub Workbook_Open()
Dim t as Range

Set t = ThisWorkbook.Worksheets("SheetName").Range("X4")
.... more code to use 't'

So you're going to have to know where 't' needs to be also. There are
variations of this, such as doing it on all worksheets:

Private Sub Workbook_Open()
Dim anySheet As Worksheet
Dim t as Range

For Each anySheet In ThisWorkbook.Worksheets
Set t = anySheet.Range("X4")
... again, the code to work with 't' on each sheet
...
Next ' end of Each anySheet Loop
End Sub


If you need to just do certain sheets in the workbook:

Private Sub Workbook_Open()
Dim anySheet As Worksheet
Dim t as Range

For Each anySheet In ThisWorkbook.Worksheets
Select Case anySheet.Name
'one of the sheets to work with
Case Is = "Sheet1", "Sheet4", "SheetOther"
Set t = anySheet.Range("X4")
... again, the code to work with 't' on each sheet
...
Case Else
'do nothing if not a sheet we listed earlier
End Select
Next ' end of Each anySheet Loop
End Sub

Hope this does you some good.

 

[Bob Barnes]

It helps a lot.

I opened another thread (afraid there might not be more answers in this
thread).

Dim t as Range
Set t = ActiveCell
....works,
but another Post advised caution in using that.

I have code like this in Workbook_Open..
Range("AOne").Select
Call SeeDiff
Range("BOne").Select
Call SeeDiff
Range("COne").Select
Call SeeDiff

....works great.

Thank you again, Bob

 

[JLatham]

Active-anything is always iffy, especially during the workbook Open event as
you don't know for certain the condition things were in when it was closed
unless you also have code to deal with that.

Glad you've got things working now.

 

[Bob Barnes]

I've got code to Control everything in the Workbook_Open. I turned it over
to Mgmt today, and they loved it.

Thank you again, Bob