typing in a number and converting it something else
two decimal places rather than 3 and Y in the third column
columns A and B X0000.00
Columns C Y0000.00
replace the existing code with this.
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrHandler
If Target.Column = 1 and Target.column <= 3 Then
If Target.Count 1 Then Exit Sub
If Not IsEmpty(Target.Value) Then
If IsNumeric(Target.Value) Then
if Target.Column < 3 then
sValue = Format(25.4 / Target.Value, "X0000.00;X-0000.00")
else
sValue = Format(25.4 / Target.Value, "Y0000.00;Y-0000.00")
End if
Application.EnableEvents = False
Target.Value = sValue
End If
End If
End If
ErrHandler:
Application.EnableEvents = True
End Sub
Adjust as required.
--
Regards,
Tom Ogilvy
"Darrell" wrote in message
...
Another thing in the 2 colum i would like it to come out
as X0000.00 ;-X0000.00 AND IN THE 3 COLUM i would like
it to come out as Y0000.00 ;-Y0000.00.
Darrell
-----Original Message-----
Assume you only want this to occur in one column.
Right click on the sheet tab and select view code.
Paste this code in the module:
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrHandler
If Target.Column = 3 Then
If Target.Count 1 Then Exit Sub
If Not IsEmpty(Target.Value) Then
If IsNumeric(Target.Value) Then
sValue = Format(25.4 / Target.Value, "X0000.000;X-
0000.000")
Application.EnableEvents = False
Target.Value = sValue
End If
End If
End If
ErrHandler:
Application.EnableEvents = True
End Sub
As written, it reformats any numeric entry in column C.
Change the 3 to
reflect the column where you want this behavior (A - 1,
B - 2, etc).
--
Regards,
Tom Ogilvy
"Darrell" wrote in message
...
also i would actually prefer to just enter in 15 not
15mm. I don't know if that will make a difference.
Thanks again,
Darrell
-----Original Message-----
If that would work that would be great, however I do
not
know anything about event macros.
Darrell
-----Original Message-----
you really can't do that type of math just using a
format.
Also, entering 15mm will make this a string and very
little can be done with
formatting to affect a string.
Do you want an event macro that will make the change?
--
Regards,
Tom Ogilvy
"Darrell" wrote in message
...
I am tring to make a form that I will be able to
enter
in
a number and the formula will do the math. I also
want
it to out put a negitive or positive number.
example:
i
enter in the number 15mm into the cell and it out
puts
X0001.693 So this is what I have so far in the
customize
cell featu =25.4/X0000.000;X-0000.000 Can
someone
please help.
Thanks in advanced,
Darrell
.
.
.
|