Cell Change Event
 

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

Cell Change Event
 

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" wrote in message
...
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

Cell Change Event
 

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

--
Regards
Frank Kabel
Frankfurt, Germany

Graham wrote:
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

Cell Change Event
 

Many thanks Chip. Now works a treat.

Graham

"Chip Pearson" wrote in message
...
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" wrote in message
...
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

Cell Change Event
 

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

"Frank Kabel" wrote in message
...
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

--
Regards
Frank Kabel
Frankfurt, Germany

Graham wrote:
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

Cell Change Event
 

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)

--
Regards,
Tom Ogilvy



"Graham" wrote in message
...
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

Cell Change Event
 

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

"Tom Ogilvy" wrote in message
...
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)

--
Regards,
Tom Ogilvy



"Graham" wrote in message
...
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

Cell Change Event
 

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

--
Regards
Frank Kabel
Frankfurt, Germany

Graham wrote:
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

"Frank Kabel" wrote in message
...
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

--
Regards
Frank Kabel
Frankfurt, Germany

Graham wrote:
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

Cell Change Event
 

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

--
Message posted from http://www.ExcelForum.com