Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 - |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
cell with formula is not automatically updating - Excel Office 200 | Excel Worksheet Functions | |||
CELL UPDATING | Excel Worksheet Functions | |||
Updating a cell | Excel Discussion (Misc queries) | |||
updating a cell value | Excel Discussion (Misc queries) | |||
Updating the Sum of a cell | Excel Worksheet Functions |