typing in a number and converting it something else
 

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

typing in a number and converting it something else
 

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

typing in a number and converting it something else
 

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


.

typing in a number and converting it something else
 

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


.

.

typing in a number and converting it something else
 

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


.

.

typing in a number and converting it something else
 

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


.

.



.

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


.

.



.