Auto adding zero to value

**georgecheng** · 12-27-2010, 11:59 PM

Is it possible to auto add zero to the value in the cell. Let say if i key in digit 5 in the cell, then it will auto convert to 5,000. Cause in accounting, if need to key in too many zero can mix up. Thanks

**Paul** · 12-28-2010, 12:55 AM

Hi George, welcome to the forum.

Take a look at this article, which provides a few options for you depending on what you ultimately need in the data (i.e. does it just have to look like you entered 5,000 when you typed 5, or does it actually need to be converted to 5,000 when you enter 5).

http://excelribbon.tips.net/Pages/T0...Thousands.html

**georgecheng** · 12-29-2010, 04:09 AM

Originally Posted by Paul

Hi George, welcome to the forum.

Take a look at this article, which provides a few options for you depending on what you ultimately need in the data (i.e. does it just have to look like you entered 5,000 when you typed 5, or does it actually need to be converted to 5,000 when you enter 5).

http://excelribbon.tips.net/Pages/T0...Thousands.html

Thanks paul it work, I used the macro method that is

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    Target = Target * 1000
    Application.EnableEvents = True
End Sub

But i have another problem surface when i used this, as i also created a button once click to clear the select range of cell(Range("Target").ClearContents). It will prompt out runtime error. and hightlight this line "Target = Target * 1000"

Any idea what's conflict here. Thanks

**DonkeyOte** · 12-29-2010, 05:22 AM

If you do not want the ChangeEvent to fire when you ClearContents you must toggle Events.

Sub Example

    On Error GoTo Handler
    Application.EnableEvents = False
    Range("Target").ClearContents
ExitPoint:
    Application.EnableEvents = True
    Exit Sub

Handler:
    MsgBox "Error Has Occurred" & vbLf & vbLf & _
        "Error Number: " & Err.Number & vbLf & vbLf & _
        "Error Desc.: " & Err.Description, _
        vbCritical, _
        "Fatal Error"
    Resume ExitPoint

End Sub

on an aside using "Target" as a variable name is ill advised.

further to the above you might also want to consider caching/toggling ScreenUpdating and Calculation modes... for more info. on general coding practices see: http://blogs.msdn.com/excel/archive/...practices.aspx

**arthurbr** · 12-29-2010, 05:18 AM

Please read our rules concerning use of code tags.

I added them for you this time

**georgecheng** · 12-29-2010, 10:10 AM

Sorry arthurbr and thanks for it.

Paul sorry ask you another issue, i just realised that the macro for multiple 1000 i used, is applied to whole sheet. Can I just specific to selected cell? I tried some ways, but keep having error. Thanks the code i used is keep wrong.
Sorry if keep asking simple question, still newbie and learning.

**Paul** · 12-30-2010, 03:30 PM

Hi George,

To only run the code if the cell is in a specific range, you can wrap the code within an IF clause, e.g.

If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
    ...do the code
Else
    ...do nothing or something else?
End If

When you change a cell in A1:A10, the code will run. If you change any other cell, it will return False and do the Else code (if there is any) or simply exit the Sub.