Data Validation with division

  • Thread starter sidm via OfficeKB.com
  • Start date
S

sidm via OfficeKB.com

Hi ,
I would like that when a number is entered in column N the person entering
the column is prompted to enter in format 100,000.00 ( ex 250,225) and then
once the number is entered it is converted in format 250.26 i.e. divided by
1000 and rounded off to two decimal places.
 
G

Gord Dibben

You won't be able to do that with Data Validation alone.

Without DV just format column N as Number with 2 DP and use comma separator.

When user enters 123456.78 it will become 123,456.78

To actually divide the number will have to done via event code.

If using event code why not do the formatting at the same time?

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("N:N")) Is Nothing Then
With Target
If .Value <> "" Then
.Value = .Value / 1000
.NumberFormat = "#,##0.00"
End If
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub

This is sheet event code. Right-click on the sheet tab and "View Code".

Copy/paste the above into that sheet module.


Gord Dibben MS Excel MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top