text box tab problem

[scrabtree23]

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

 

[Bob Phillips]

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?

 

[Harald Staff]

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.

 

[scrabtree23]

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

 

[scrabtree23]

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

 

[Bob Phillips]

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

 

[scrabtree23]

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

SDC

 

[scrabtree23]

How much of the code do you want? Just where the error is
popping up? Or the code for the whole userform?

 

[scrabtree23]

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




.