Excel: Self-updating cell
 

Hi,

Is there a way to make a cell so when a user puts 7 to a cell and it
automatically changes to 0.7 by multiplying 0.1 to the value it has
received and show the result in the same cell?

I thought I might be able to utilize the data validation functionality
but couldn't get it to work.

Thanks in advance,
Chan

Excel: Self-updating cell
 

Maybe this (to be put in the sheet's code module):

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If IsNumeric(Target) And Target.HasFormula = False Then
Target.Value = 0.1 * Target.Value
End If
Application.EnableEvents = True
End Sub

*******************

I wrote it so that if *the user* enters a number then that number is
automatically multiplied by 0.1 but that if a formula changes the
value of a cell then that value is left alone.

Hope that helps

-John Coleman


On Apr 13, 10:04 am, wrote:
Hi,

Is there a way to make a cell so when a user puts 7 to a cell and it
automatically changes to 0.7 by multiplying 0.1 to the value it has
received and show the result in the same cell?

I thought I might be able to utilize the data validation functionality
but couldn't get it to work.

Thanks in advance,
Chan

Excel: Self-updating cell
 

I just noticed how in your post you talked about wanting a *cell* with
this property. If you want to restrict to a single cell (say A1) , no
problem - just check the address of the Target in the If statement
(note the dollar signs):

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If IsNumeric(Target) And Target.HasFormula = False _
And Target.Address = "$A$1" Then
Target.Value = 0.1 * Target.Value
End If
Application.EnableEvents = True
End Sub



On Apr 13, 11:31 am, "John Coleman" wrote:
Maybe this (to be put in the sheet's code module):

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If IsNumeric(Target) And Target.HasFormula = False Then
Target.Value = 0.1 * Target.Value
End If
Application.EnableEvents = True
End Sub

*******************

I wrote it so that if *the user* enters a number then that number is
automatically multiplied by 0.1 but that if a formula changes the
value of a cell then that value is left alone.

Hope that helps

-John Coleman

On Apr 13, 10:04 am, wrote:



Hi,

Is there a way to make a cell so when a user puts 7 to a cell and it
automatically changes to 0.7 by multiplying 0.1 to the value it has
received and show the result in the same cell?

I thought I might be able to utilize the data validation functionality
but couldn't get it to work.

Thanks in advance,
Chan- Hide quoted text -

- Show quoted text -

Excel: Self-updating cell
 

Hi John,

Thank you so much for your reply.
I haven't tested the code yet but it looks to me it will work.

Thanks again,
Chan