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

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




.

"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.

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.


.

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 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


.

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


.



.