A few days ago I posed the following questions:

"I have a text box on a user form and I want to format it
so the user has to enter numbers (with a decimal) and the
result needs to be displayed in the text box as currency.
I don't want it to allow text. ??? SDC"

Here was an excellent answer:

"Hi SDC

Userform code:

Private Sub TextBox1_Enter()
TextBox1.SelStart = 0
TextBox1.SelLength = Len(TextBox1.Text)
End Sub

Private Sub TextBox1_KeyPress(ByVal KeyAscii As
MSForms.ReturnInteger)
Select Case KeyAscii
Case 8 To 10, 13, 27, 32 'Control characters
Case 44, 46 'comma, dot
If InStr(TextBox1.Text, ".") = 0 Then
KeyAscii = 46
Else
KeyAscii = 0
End If
Case 45 'minus
If TextBox1.SelStart = 0 And InStr
(TextBox1.Text, "-") = 0 Then
Else
KeyAscii = 0
End If
Case 48 To 57 'numbers
Case Else 'Discard anything else
KeyAscii = 0
End Select
End Sub

Private Sub TextBox1_Exit(ByVal Cancel As
MSForms.ReturnBoolean)
Dim D As Double
D = CDbl(TextBox1.Text)
TextBox1.Text = Format(D, "Currency")
End Sub

--
HTH. Best wishes Harald
Excel MVP"


However, when I tab out of my text box the next tab stop
is a command button. The text in the text box does not
convert to currency when tabbing to a command button. To
convert to currency, I have to select another text box???

SDC

SDC,

I have just copied that code into a simple 1 textbox 1 commandbutton
userform, I put 123.12 in the textbox and it works fine for me with a
leading £ sign. What do you get?

--

HTH

Bob Phillips

"scrabtree23" wrote in message
...
A few days ago I posed the following questions:

"I have a text box on a user form and I want to format it
so the user has to enter numbers (with a decimal) and the
result needs to be displayed in the text box as currency.
I don't want it to allow text. ??? SDC"

Here was an excellent answer:

"Hi SDC

Userform code:

Private Sub TextBox1_Enter()
TextBox1.SelStart = 0
TextBox1.SelLength = Len(TextBox1.Text)
End Sub

Private Sub TextBox1_KeyPress(ByVal KeyAscii As
MSForms.ReturnInteger)
Select Case KeyAscii
Case 8 To 10, 13, 27, 32 'Control characters
Case 44, 46 'comma, dot
If InStr(TextBox1.Text, ".") = 0 Then
KeyAscii = 46
Else
KeyAscii = 0
End If
Case 45 'minus
If TextBox1.SelStart = 0 And InStr
(TextBox1.Text, "-") = 0 Then
Else
KeyAscii = 0
End If
Case 48 To 57 'numbers
Case Else 'Discard anything else
KeyAscii = 0
End Select
End Sub

Private Sub TextBox1_Exit(ByVal Cancel As
MSForms.ReturnBoolean)
Dim D As Double
D = CDbl(TextBox1.Text)
TextBox1.Text = Format(D, "Currency")
End Sub

--
HTH. Best wishes Harald
Excel MVP"


However, when I tab out of my text box the next tab stop
is a command button. The text in the text box does not
convert to currency when tabbing to a command button. To
convert to currency, I have to select another text box???

SDC

"scrabtree23" wrote in message
...

However, when I tab out of my text box the next tab stop
is a command button. The text in the text box does not
convert to currency when tabbing to a command button. To
convert to currency, I have to select another text box???

That is certainly very weird. Quoted code works like a charm here both in Excel97 and
ExcelXP. Is there possibly any other conflicting code in the form ?

Best wishes Harald
Excel MVP

Followup to newsgroup only please.

Bob/Harald,

Thanks for your replies. I have six text boxes inside one
frame. All share a similar code as detailed below. Once
I select a text box and enter the numbers (i.e. 23), the
number appears in the box as "23", until I select another
text box. Then the "23" converts to "$23.00". If I tab
from box to box the same thing happens as above, execpt on
the last box becasue from there the tab goes to a command
button. When the command button becomes the focus
(instead of another text box), the "23" stays "23"???

Also, I had a problem with the code earlier. If I tabed
from box to box without entering data I would get an error
message. This line of the code would highlight: D = CDbl
(usfmilage.txtnewmealregualrbreakfast.Text) .

To fix this I added the following code:

If usfmilage.txtnewmealregualrbreakfast.value = "" Then
Else
usfmilage.txtnewmealregualrbreakfast.Text
End If

SDC




-----Original Message-----
A few days ago I posed the following questions:

"I have a text box on a user form and I want to format it
so the user has to enter numbers (with a decimal) and
the
result needs to be displayed in the text box as
currency.
I don't want it to allow text. ??? SDC"

Here was an excellent answer:

"Hi SDC

Userform code:

Private Sub TextBox1_Enter()
TextBox1.SelStart = 0
TextBox1.SelLength = Len(TextBox1.Text)
End Sub

Private Sub TextBox1_KeyPress(ByVal KeyAscii As
MSForms.ReturnInteger)
Select Case KeyAscii
Case 8 To 10, 13, 27, 32 'Control characters
Case 44, 46 'comma, dot
If InStr(TextBox1.Text, ".") = 0 Then
KeyAscii = 46
Else
KeyAscii = 0
End If
Case 45 'minus
If TextBox1.SelStart = 0 And InStr
(TextBox1.Text, "-") = 0 Then
Else
KeyAscii = 0
End If
Case 48 To 57 'numbers
Case Else 'Discard anything else
KeyAscii = 0
End Select
End Sub

Private Sub TextBox1_Exit(ByVal Cancel As
MSForms.ReturnBoolean)
Dim D As Double
D = CDbl(TextBox1.Text)
TextBox1.Text = Format(D, "Currency")
End Sub

--
HTH. Best wishes Harald
Excel MVP"


However, when I tab out of my text box the next tab stop
is a command button. The text in the text box does not
convert to currency when tabbing to a command button. To
convert to currency, I have to select another text box???

SDC


.

Mine would just stay "123.12"??? I also expaned on my
question as I replied to myself. Please check it out and
advise.
-----Original Message-----
SDC,

I have just copied that code into a simple 1 textbox 1
commandbutton
userform, I put 123.12 in the textbox and it works fine
for me with a
leading £ sign. What do you get?

--

HTH

Bob Phillips

"scrabtree23" wrote in message
...
A few days ago I posed the following questions:

"I have a text box on a user form and I want to format
it
so the user has to enter numbers (with a decimal) and
the
result needs to be displayed in the text box as
currency.
I don't want it to allow text. ??? SDC"

Here was an excellent answer:

"Hi SDC

Userform code:

Private Sub TextBox1_Enter()
TextBox1.SelStart = 0
TextBox1.SelLength = Len(TextBox1.Text)
End Sub

Private Sub TextBox1_KeyPress(ByVal KeyAscii As
MSForms.ReturnInteger)
Select Case KeyAscii
Case 8 To 10, 13, 27, 32 'Control characters
Case 44, 46 'comma, dot
If InStr(TextBox1.Text, ".") = 0 Then
KeyAscii = 46
Else
KeyAscii = 0
End If
Case 45 'minus
If TextBox1.SelStart = 0 And InStr
(TextBox1.Text, "-") = 0 Then
Else
KeyAscii = 0
End If
Case 48 To 57 'numbers
Case Else 'Discard anything else
KeyAscii = 0
End Select
End Sub

Private Sub TextBox1_Exit(ByVal Cancel As
MSForms.ReturnBoolean)
Dim D As Double
D = CDbl(TextBox1.Text)
TextBox1.Text = Format(D, "Currency")
End Sub

--
HTH. Best wishes Harald
Excel MVP"


However, when I tab out of my text box the next tab stop
is a command button. The text in the text box does not
convert to currency when tabbing to a command button.
To
convert to currency, I have to select another text
box???

SDC




.

I suggest that you post all the code so that we can see, it's a little
difficult trying to work it all out.

--

HTH

Bob Phillips

"scrabtree23" wrote in message
...
Bob/Harald,

Thanks for your replies. I have six text boxes inside one
frame. All share a similar code as detailed below. Once
I select a text box and enter the numbers (i.e. 23), the
number appears in the box as "23", until I select another
text box. Then the "23" converts to "$23.00". If I tab
from box to box the same thing happens as above, execpt on
the last box becasue from there the tab goes to a command
button. When the command button becomes the focus
(instead of another text box), the "23" stays "23"???

Also, I had a problem with the code earlier. If I tabed
from box to box without entering data I would get an error
message. This line of the code would highlight: D = CDbl
(usfmilage.txtnewmealregualrbreakfast.Text) .

To fix this I added the following code:

If usfmilage.txtnewmealregualrbreakfast.value = "" Then
Else
usfmilage.txtnewmealregualrbreakfast.Text
End If

SDC




-----Original Message-----
A few days ago I posed the following questions:

"I have a text box on a user form and I want to format it
so the user has to enter numbers (with a decimal) and
the
result needs to be displayed in the text box as
currency.
I don't want it to allow text. ??? SDC"

Here was an excellent answer:

"Hi SDC

Userform code:

Private Sub TextBox1_Enter()
TextBox1.SelStart = 0
TextBox1.SelLength = Len(TextBox1.Text)
End Sub

Private Sub TextBox1_KeyPress(ByVal KeyAscii As
MSForms.ReturnInteger)
Select Case KeyAscii
Case 8 To 10, 13, 27, 32 'Control characters
Case 44, 46 'comma, dot
If InStr(TextBox1.Text, ".") = 0 Then
KeyAscii = 46
Else
KeyAscii = 0
End If
Case 45 'minus
If TextBox1.SelStart = 0 And InStr
(TextBox1.Text, "-") = 0 Then
Else
KeyAscii = 0
End If
Case 48 To 57 'numbers
Case Else 'Discard anything else
KeyAscii = 0
End Select
End Sub

Private Sub TextBox1_Exit(ByVal Cancel As
MSForms.ReturnBoolean)
Dim D As Double
D = CDbl(TextBox1.Text)
TextBox1.Text = Format(D, "Currency")
End Sub

--
HTH. Best wishes Harald
Excel MVP"


However, when I tab out of my text box the next tab stop
is a command button. The text in the text box does not
convert to currency when tabbing to a command button. To
convert to currency, I have to select another text box???

SDC


.

I replied to my own question and expanded the question
there. Please review and advise, and thanks.

SDC
-----Original Message-----
"scrabtree23" wrote in message
...

However, when I tab out of my text box the next tab stop
is a command button. The text in the text box does not
convert to currency when tabbing to a command button.
To
convert to currency, I have to select another text
box???

That is certainly very weird. Quoted code works like a
charm here both in Excel97 and
ExcelXP. Is there possibly any other conflicting code in
the form ?

Best wishes Harald
Excel MVP

Followup to newsgroup only please.


.

How much of the code do you want? Just where the error is
popping up? Or the code for the whole userform?
-----Original Message-----
I suggest that you post all the code so that we can see,
it's a little
difficult trying to work it all out.

--

HTH

Bob Phillips

"scrabtree23" wrote in message
...
Bob/Harald,

Thanks for your replies. I have six text boxes inside
one
frame. All share a similar code as detailed below.
Once
I select a text box and enter the numbers (i.e. 23), the
number appears in the box as "23", until I select
another
text box. Then the "23" converts to "$23.00". If I tab
from box to box the same thing happens as above, execpt
on
the last box becasue from there the tab goes to a
command
button. When the command button becomes the focus
(instead of another text box), the "23" stays "23"???

Also, I had a problem with the code earlier. If I tabed
from box to box without entering data I would get an
error
message. This line of the code would highlight: D =
CDbl
(usfmilage.txtnewmealregualrbreakfast.Text) .

To fix this I added the following code:

If usfmilage.txtnewmealregualrbreakfast.value = "" Then
Else
usfmilage.txtnewmealregualrbreakfast.Text
End If

SDC




-----Original Message-----
A few days ago I posed the following questions:

"I have a text box on a user form and I want to format
it
so the user has to enter numbers (with a decimal) and
the
result needs to be displayed in the text box as
currency.
I don't want it to allow text. ??? SDC"

Here was an excellent answer:

"Hi SDC

Userform code:

Private Sub TextBox1_Enter()
TextBox1.SelStart = 0
TextBox1.SelLength = Len(TextBox1.Text)
End Sub

Private Sub TextBox1_KeyPress(ByVal KeyAscii As
MSForms.ReturnInteger)
Select Case KeyAscii
Case 8 To 10, 13, 27, 32 'Control characters
Case 44, 46 'comma, dot
If InStr(TextBox1.Text, ".") = 0 Then
KeyAscii = 46
Else
KeyAscii = 0
End If
Case 45 'minus
If TextBox1.SelStart = 0 And InStr
(TextBox1.Text, "-") = 0 Then
Else
KeyAscii = 0
End If
Case 48 To 57 'numbers
Case Else 'Discard anything else
KeyAscii = 0
End Select
End Sub

Private Sub TextBox1_Exit(ByVal Cancel As
MSForms.ReturnBoolean)
Dim D As Double
D = CDbl(TextBox1.Text)
TextBox1.Text = Format(D, "Currency")
End Sub

--
HTH. Best wishes Harald
Excel MVP"


However, when I tab out of my text box the next tab
stop
is a command button. The text in the text box does not
convert to currency when tabbing to a command button.
To
convert to currency, I have to select another text
box???

SDC


.



.

Worked like a charm.

Thanks to all.
-----Original Message-----
When you tab out of the frame, the exit event for the
textbox does not fire.
the exit event for the frame fires I believe.

I believe this swould be true if you use the mouse to
select outside the
frame from any of the textboxes.


You might have the frame exit event format all the
textboxes which don't
contain a $. As an example

if Instr(Textbox1.Text,"$") = 0 then
' format textbox1
End if
if Instr(Textbox2.Text,"$") = 0 then
' format textbox2
End if

--
Regards,
Tom Ogilvy

scrabtree23 wrote in message
...
Bob/Harald,

Thanks for your replies. I have six text boxes inside
one
frame. All share a similar code as detailed below.
Once
I select a text box and enter the numbers (i.e. 23), the
number appears in the box as "23", until I select
another
text box. Then the "23" converts to "$23.00". If I tab
from box to box the same thing happens as above, execpt
on
the last box becasue from there the tab goes to a
command
button. When the command button becomes the focus
(instead of another text box), the "23" stays "23"???

Also, I had a problem with the code earlier. If I tabed
from box to box without entering data I would get an
error
message. This line of the code would highlight: D =
CDbl
(usfmilage.txtnewmealregualrbreakfast.Text) .

To fix this I added the following code:

If usfmilage.txtnewmealregualrbreakfast.value = "" Then
Else
usfmilage.txtnewmealregualrbreakfast.Text
End If

SDC




-----Original Message-----
A few days ago I posed the following questions:

"I have a text box on a user form and I want to format
it
so the user has to enter numbers (with a decimal) and
the
result needs to be displayed in the text box as
currency.
I don't want it to allow text. ??? SDC"

Here was an excellent answer:

"Hi SDC

Userform code:

Private Sub TextBox1_Enter()
TextBox1.SelStart = 0
TextBox1.SelLength = Len(TextBox1.Text)
End Sub

Private Sub TextBox1_KeyPress(ByVal KeyAscii As
MSForms.ReturnInteger)
Select Case KeyAscii
Case 8 To 10, 13, 27, 32 'Control characters
Case 44, 46 'comma, dot
If InStr(TextBox1.Text, ".") = 0 Then
KeyAscii = 46
Else
KeyAscii = 0
End If
Case 45 'minus
If TextBox1.SelStart = 0 And InStr
(TextBox1.Text, "-") = 0 Then
Else
KeyAscii = 0
End If
Case 48 To 57 'numbers
Case Else 'Discard anything else
KeyAscii = 0
End Select
End Sub

Private Sub TextBox1_Exit(ByVal Cancel As
MSForms.ReturnBoolean)
Dim D As Double
D = CDbl(TextBox1.Text)
TextBox1.Text = Format(D, "Currency")
End Sub

--
HTH. Best wishes Harald
Excel MVP"


However, when I tab out of my text box the next tab
stop
is a command button. The text in the text box does not
convert to currency when tabbing to a command button.
To
convert to currency, I have to select another text
box???

SDC


.



.