Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 15
Default text box tab problem

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


  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 620
Default text box tab problem

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




  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 15
Default text box tab problem

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




.

  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 70
Default text box tab problem

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


  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 15
Default text box tab problem

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.


.



  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 15
Default text box tab problem

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


.

  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 620
Default text box tab problem

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


.



  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 15
Default text box tab problem

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


.



.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Countif - problem counting text, other text gmoore3055 Excel Worksheet Functions 1 February 4th 10 10:30 PM
Text Display Problem. Markv Excel Discussion (Misc queries) 0 July 30th 08 08:37 AM
Text Box problem M&M[_2_] Excel Discussion (Misc queries) 1 August 9th 07 10:36 PM
Large amount of text in cells - Problem text.xls (0/1) Patrick Excel Discussion (Misc queries) 3 May 5th 05 04:10 PM
Large amount of text in cells - Problem text.xls (1/1) Patrick Excel Discussion (Misc queries) 0 May 5th 05 01:23 PM


All times are GMT +1. The time now is 10:53 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"