User enters number and 3 zeros are added at end of number

  • Thread starter Thread starter Township of East Hanover
  • Start date Start date
T

Township of East Hanover

Hi all,

I've done some vba programing and created a few Spreasheets and workbooks
but this one eludes me. I'm trying to get Excel to take a user enter number
for example 15 and automatically make it 15,000. Does anyone have an idea of
how to acomplish this? I have to save the user some data entry work.

Rui
 
How about using a worksheet_change event in the sheet module and multiply
the target *1000?
 
Ok, so I have:

Private Sub Worksheet_Change(ByVal Target As Range)
Target.Value = Target.Value * 1000
End Sub

Just one problem this code causes a change in the value of the cell so it
just keeps repeating in a loop what am I missing.

Rui
 
Thanx. Here's the final code for anyone else that might need it:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub ' Only change one cell
at a time
Application.EnableEvents = False ' Disable events so that
the code doesn't go into endless loop
Target.Value = Target.Value * 1000 ' Make changes to the
value of the current cell
Application.EnableEvents = True ' Re-enable events
End Sub

Once again thanx.
 
Forgot to check for Empty cells so here is updated:

Private Sub Worksheet_Change(ByVal Target As Range)
If IsEmpty(Target.Value) or Target.Count > 1 Then Exit Sub
Application.EnableEvents = False
Target.Value = Target.Value * 1000
Application.EnableEvents = True
End Sub
 
Rui,

You may also wish to do this only in a certain column(s):

If Not Intersect(Target, Range("A:A")) Is Nothing Then ' column A?
Application.EnableEvents = False
Target = Target * 1000
Application.EnableEvents = True
End If
 
Back
Top