Userforms - working with decimal places

[Angie]

I would like to be able to enter an amount into a textbox within a userform
and have the amount fill from the right hand side. (This much I can do using
the Align Property). However, as the numbers are typed I would like to see
the decimal point to be placed 2 decimal places within the number, ie if I
enter 123456 I would like to see:
As soon as the 12 is typed – see .12
As soon as the next digit is typed, the 3 – see 1.23
As soon as the next digit is typed, the 4 – see 12.34
If I then deletes the last number, the 4 – see 1.23

So if I type 123456 – see 1234.56

So a value of $2000.00 would be typed as 200000 and the decimal point is not
typed but appears correctly.

Is this possible?

Many Thanks, Angie.

 

[Doug Robbins - Word MVP]

It's going to take some pretty sophisticated code to do exactly what you
want.

The following will insert the decimal point when the user exits from the
textbox. Is that enough for you:

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim num As Long
num = Val(TextBox1.Text)
TextBox1.Text = Format(num / 100, "0.00")


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[Helmut Weber]

Hi Angie,

just for fun, how about this one:

Private Sub TextBox1_Change()
TextBox1.Text = DigitPeriod(TextBox1.Text)
End Sub

Public Function DigitPeriod(sTmp As String) As String
Dim lCnt As Long ' a counter
Dim lPrt As String ' left part of a string
Dim rPrt As String ' right part of a string
' replace any character that isn't a digit by "x"
For lCnt = 1 To Len(sTmp)
If Asc(Mid(sTmp, lCnt, 1)) < 48 _
Or Asc(Mid(sTmp, lCnt, 1)) > 57 Then
Mid(sTmp, lCnt, 1) = "x"
End If
Next
' delete all "x"s
sTmp = Replace(sTmp, "x", "")
' insert a period before the second last digit
' if there are more than 3 digits
If Len(sTmp) > 2 Then
lPrt = Left(sTmp, Len(sTmp) - 2)
rPrt = Right(sTmp, 2)
sTmp = lPrt & "." & rPrt
End If
DigitPeriod = sTmp
End Function

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

 

[Doug Robbins - Word MVP]

Hi Helmut

A minor modification handles the "formatting" of the first and second digit
that are added:

Private Sub TextBox1_Change()
TextBox1.Text = DigitPeriod(TextBox1.Text)
End Sub

Public Function DigitPeriod(sTmp As String) As String
Dim lCnt As Long ' a counter
Dim lPrt As String ' left part of a string
Dim rPrt As String ' right part of a string
' replace any character that isn't a digit by "x"
For lCnt = 1 To Len(sTmp)
If Asc(Mid(sTmp, lCnt, 1)) < 48 _
Or Asc(Mid(sTmp, lCnt, 1)) > 57 Then
Mid(sTmp, lCnt, 1) = "x"
End If
Next
' delete all "x"s
sTmp = Replace(sTmp, "x", "")
' insert a period before the second last digit
' if there are more than 3 digits
If Len(sTmp) > 2 Then
lPrt = Int(Left(sTmp, Len(sTmp) - 2))
rPrt = Right(sTmp, 2)
sTmp = lPrt & "." & rPrt
Else
sTmp = "0.0" & sTmp
End If
If Val(sTmp) = 0 Then
sTmp = ""
End If
DigitPeriod = sTmp
End Function



--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[Helmut Weber]

Hi Doug,

yes!

Feels good thinking together.

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

 

[Jay Freedman]

Hi Angie,

I recommend Doug's method as much the simplest way. If you really need
to see the decimal point in place as the number is being entered,
though, you can use this instead:

Private Sub TextBox1_Change()
Dim tmp As String
Dim num As Long

With TextBox1
tmp = .Text
If Not (Right(tmp, 1) Like "[!0-9.]") Then
' remove any decimal point
tmp = Replace(tmp, ".", "")

On Error GoTo Oflow
num = Val(tmp)
tmp = Format(num / 100, "0.00")

.Text = tmp
Else
' remove invalid character
.Text = Left(tmp, Len(tmp) - 1)
End If
End With

Exit Sub

Oflow:
MsgBox "The number is too large.", , "Error"
TextBox1.Text = Left(tmp, Len(tmp) - 1)
End Sub

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the
newsgroup so all may benefit.

 

[Greg Maxey]

Jay,

Perhaps I am just unable to create an error or failure, but why did you
use:

If Not (Right(tmp, 1) Like "[!0-9.]") Then

rather than just

If (Right(tmp, 1) Like "[0-9.]") Then

Hi Angie,

I recommend Doug's method as much the simplest way. If you really need
to see the decimal point in place as the number is being entered,
though, you can use this instead:

Private Sub TextBox1_Change()
Dim tmp As String
Dim num As Long

With TextBox1
tmp = .Text
If Not (Right(tmp, 1) Like "[!0-9.]") Then
' remove any decimal point
tmp = Replace(tmp, ".", "")

On Error GoTo Oflow
num = Val(tmp)
tmp = Format(num / 100, "0.00")

.Text = tmp
Else
' remove invalid character
.Text = Left(tmp, Len(tmp) - 1)
End If
End With

Exit Sub

Oflow:
MsgBox "The number is too large.", , "Error"
TextBox1.Text = Left(tmp, Len(tmp) - 1)
End Sub

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the
newsgroup so all may benefit.

It's going to take some pretty sophisticated code to do exactly what you
want.

The following will insert the decimal point when the user exits from the
textbox. Is that enough for you:

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim num As Long
num = Val(TextBox1.Text)
TextBox1.Text = Format(num / 100, "0.00")


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[Angie]

Hello

Thanks so much for all the suggestions, I will give these a go over the next
couple of days and then update you.

You help if very much appreciated.

 

[Helmut Weber]

Hmm...

I think, all of the offered solutions will work in practice.

However, just for the sake of enjoying riddles,
I thought, as soon as numbers come in,
there will be problems with numbers and their limitations,
like exponential represention.

So I restricted all to digits,
avoiding numbers altogether and allowing
only a string consisting of digits
and adding a period and leading zeros, if required.
Also avoiding "0.00", if a character
other than a digit was entered at first..

Yes, a string consisting of some 1000s of
digits ending in a period followd by two digits
is of no practical use.

Private Sub TextBox1_Change()
TextBox1.Text = DigitPeriod(TextBox1.Text)
End Sub

Public Function DigitPeriod(sTmp As String) As String
Dim lCnt As Long ' a counter
Dim lPrt As String ' left part of a string
Dim rPrt As String ' right part of a string
' replace any character that isn't a digit by "x"
For lCnt = 1 To Len(sTmp)
If Asc(Mid(sTmp, lCnt, 1)) < 48 _
Or Asc(Mid(sTmp, lCnt, 1)) > 57 Then
Mid(sTmp, lCnt, 1) = "x"
End If
Next
' delete all "x"s
sTmp = Replace(sTmp, "x", "")
' insert a period before the second last digit
' if there are more than 3 digits
If Len(sTmp) > 2 Then
lPrt = Left(sTmp, Len(sTmp) - 2)
rPrt = Right(sTmp, 2)
sTmp = lPrt & "." & rPrt
End If
If Len(sTmp) = 2 Then
lPrt = "0"
rPrt = sTmp
sTmp = lPrt & "." & rPrt
End If
If Len(sTmp) = 1 Then
lPrt = "0"
rPrt = "0" & sTmp
sTmp = lPrt & "." & rPrt
End If
While Left(sTmp, 1) = "0" And Len(sTmp) > 4
sTmp = Right(sTmp, Len(sTmp) - 1)
Wend

DigitPeriod = sTmp
End Function

' -----------------

Have a nice day.

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

 

[Jay Freedman]

Sorry, that's just a leftover from some other stuff I was trying out
before. You're right, simpler is better.

Just an aside: This is a perfect example of why all production code
should be inspected by someone other than the original author. It's
much too easy to let your eye slide right over something you've been
looking at for a while, no matter how ugly it really is.

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the
newsgroup so all may benefit.

 

[Angie]

Thanks for all of your help.

This function works a treat and everyone thinks its great.

 

[Greg Maxey]

Jay,

Sorry for the delay in reply. I have been down at Norfolk Naval Station this
week at transition training. Glad to have your back this time ;-).

--
Greg Maxey/Word MVP
See:
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

Sorry, that's just a leftover from some other stuff I was trying out
before. You're right, simpler is better.

Just an aside: This is a perfect example of why all production code
should be inspected by someone other than the original author. It's
much too easy to let your eye slide right over something you've been
looking at for a while, no matter how ugly it really is.

Jay,

Perhaps I am just unable to create an error or failure, but why did
you use:

If Not (Right(tmp, 1) Like "[!0-9.]") Then

rather than just

If (Right(tmp, 1) Like "[0-9.]") Then