Spacing of amounts 1

[Mollethewizard]

I’ve got a user form with textboxes where the users should put in amounts. In Sweden we divide the amounts in groups of 3. For example 1000 we write 1 000 and 78500 should be written 78 500. I’ve managed to write code that takes care of the spaces between the groups when the user exits the textboxes. Just to prevent the user from writing the amounts in the wrong order (with dots, commas etc.) the textboxes only accepts entered numbers.

A problem occurs when a user discovers that he has entered the wrong amount in a textbox further up and back-tabs to the textbox he wants to correct. The textboxes he back-tabs trough who has amounts, lets say, 1 234 or 12 345 or 123 456 etc. will have extra spaces.

Is there a way to increase "the spacing code" so the extra spaces don’t occur? I’ve tried with comparison of the string with “Like” but I haven’t managed to get it to work.

Preventing code:

Private Sub txtBel12_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If KeyAscii < 48 Or KeyAscii > 57 Then KeyAscii = 0
End Sub

Exit code:

Private Sub txtBel12_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Kronkoll = Me.txtBel12
Call Krondel
Me.txtBel12 = Kronor

“The spacing code” I suppose there is a smarter way to write this code too?

Sub Krondel()
If Len(Kronkoll) = 4 Then
Kr1 = Left(Kronkoll, 1)
Kr2 = Right(Kronkoll, 3)
Kronor = Kr1 & " " & Kr2
ElseIf Len(Kronkoll) = 5 Then
Kr1 = Left(Kronkoll, 2)
Kr2 = Right(Kronkoll, 3)
Kronor = Kr1 & " " & Kr2
ElseIf Len(Kronkoll) = 6 Then
Kr1 = Left(Kronkoll, 3)
Kr2 = Right(Kronkoll, 3)
Kronor = Kr1 & " " & Kr2
ElseIf Len(Kronkoll) = 7 Then
Kr1 = Left(Kronkoll, 1)
KrMitt = Mid(Kronkoll, 2, 3)
Kr2 = Right(Kronkoll, 3)
Kronor = Kr1 & " " & KrMitt & " " & Kr2
ElseIf Len(Kronkoll) = 8 Then
Kr1 = Left(Kronkoll, 2)
KrMitt = Mid(Kronkoll, 3, 3)
Kr2 = Right(Kronkoll, 3)
Kronor = Kr1 & " " & KrMitt & " " & Kr2
ElseIf Len(Kronkoll) = 9 Then
Kr1 = Left(Kronkoll, 3)
KrMitt = Mid(Kronkoll, 4, 3)
Kr2 = Right(Kronkoll, 3)
Kronor = Kr1 & " " & KrMitt & " " & Kr2
Else: Kronor = Kronkoll
End If
End Sub

Christer

 

[PHV]

Try something like this:
Private Sub txtBel12_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Me.txtBel12 = Format(Me.txtBel12, "### ### ##0")
End Sub
Private Sub txtBel12_Enter()
Me.txtBel12 = Replace(Me.txtBel12, " ", "")
End Sub

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Enkay62]

Hi.
I'm not pretty sure I've understood the matter but, anyway, as far as I've understood, if I were you I would proceed as following :

1)In "Enter" event put code to "Compact" spaces.
2) Use "KeyDown" event to prevent users from creating spaces

If keycode = vbKeySpace then
keycode = 0
End If

3)In "Exit" event re-create spaces

Hope this helps

Bye

Nick

 

[Mollethewizard]

PHV
Your code works just beautifully!

Enjoy your star!

Thanks

Christer