If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Custom Formating Cells
I want to format a cell so that when I type a number into that cell it will
take the absolute value of that number and then multiple it by 1000. Is there anyway to do this without generating a formula in a new cell? |
#2
|
|||
|
|||
Custom Formating Cells
This is called 'event code'. Copy the code, right-click the tab, and paste
it into the window that opens up: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub If Not Intersect(Target, Range("A1:A10")) Is Nothing Then If IsNumeric(Target) Then Application.EnableEvents = False Target.Value = Abs(Target.Value * 1000) Application.EnableEvents = True End If End If End Sub Notice, the range is A1:A10; change to suit your needs... Regards, Ryan--- -- RyGuy "JohnWFUBMC" wrote: I want to format a cell so that when I type a number into that cell it will take the absolute value of that number and then multiple it by 1000. Is there anyway to do this without generating a formula in a new cell? |
#3
|
|||
|
|||
Custom Formating Cells
You can't "format" a cell to multiply itself by 1000
You could use event code to do this. Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "A1:A10" Dim cell As Range On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then For Each cell In Target If cell.Value "" Then cell.Value = cell.Value * 1000 cell.NumberFormat = "###0.00" End If Next cell End If ws_exit: Application.EnableEvents = True End Sub Right-click on the sheet tab and "View Code". Copy/paste the above into that sheet module. Adjust range and numberformat to suit. Gord Dibben MS Excel MVP On Fri, 14 Dec 2007 08:36:08 -0800, JohnWFUBMC wrote: I want to format a cell so that when I type a number into that cell it will take the absolute value of that number and then multiple it by 1000. Is there anyway to do this without generating a formula in a new cell? |
Thread Tools | |
Display Modes | |
|
|