Cell Change Event

[Graham]

I am trying to get a cell change event procedure to make a calculation with
the data entered in one cell and place that data into another cell. I am
trying something like the procedure below as an example.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Newrange As Range
Set Newrange = Range("A1")
If Union(Target, Newrange).Address = Newrange.Address Then
Range("B1") = Range("A1") * 2.471
End If
End Sub

The problem is that this works but when I put an entry in cell A1 I must
leave the cell and return to it before the procedure is activated and the
calculation is carried out. Is there a way that the procedure can be
activated whenever the value of A1 is changed. Grateful for any guidance.

Graham Haughs
Turriff, Scotland

 

[Chip Pearson]

Graham,

Use the Change event instead of the SelectionChange event. The
SelectionChange event occurs whenever a range is selected. Change
occurs when the value is changed, either manually or by VBA code
(but not as the result of a calculation).


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Frank Kabel]

Hi
try the following
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
On Error GoTo CleanUp:
With Target
If .Value <> "" Then
Application.EnableEvents = False
.offset(0,1).Value = .value *2.471
End If
.CheckSpelling
End With
CleanUp:
Application.EnableEvents = True
End Sub

 

[Graham]

Many thanks Chip. Now works a treat.

Graham

Graham,

Use the Change event instead of the SelectionChange event. The
SelectionChange event occurs whenever a range is selected. Change
occurs when the value is changed, either manually or by VBA code
(but not as the result of a calculation).


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Graham]

Hi Frank,

Thanks for the error trapping alternative whch works perfectly. One wee
query if I may however is to ask what is the purpose of the Checkspelling in
this particular procedure?

Graham

 

[Tom Ogilvy]

Use change instead of selectionChange

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address = "$A$1" Then
Range("B1") = Range("A1") * 2.471
End If
End Sub

But why not just put a formla in B1

=if(A1="","",A1*2.471)

 

[Graham]

Hi Tom,

The answer to

But why not just put a formla in B1

=if(A1="","",A1*2.471)

is that I want to enter hectares in A1 and it will be converted to acres in
B1, (*2.471), but I also want to run a procedure from B1 so that if acres
are entered in B1 it will be converted to Hectares (/2.471), in A1.
It was to give the opportunity of entering either or and not deleting the
formula, or putting the conversion in other cells . I am sure ther will be
many other ways of doing this.
Thanks for your help.

Graham

 

[Frank Kabel]

Hi Graham
delete this line (Part of an older post left behind due to copy and
paste)

 

[cyn]

Thanks Frank! I adjusted your code to my needs and it worked great! I'
going to be the star of the office this week! Cy