Word 2007 UserForm Textbox = Currency & Subtract in Real Time 1

[RP1America]

Greetings!

2 part question:

1) Within a Userform, how do I make data entered into a text show as currency ($)? I currently have the textboxes set to allow only numeric and only one decimal point, yet I cannot figure out how to make it show as currency. I would also like to permit only two decimal places, which I have not figured out. Here is what I have thus far ("gross" is a textbox):

Private Sub gross_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii
Case Asc("0") To Asc("9")
Case Asc(".")
If InStr(1, Me.gross.Text, ".") > 0 Then
KeyAscii = 0
End If
Case Else
KeyAscii = 0
End Select
End Sub


2) I have 4 textboxes in which dollar amounts will be entered by the user; gross, federal withholding, state withholding, and medical withhodling. I have another textbox that I would like to display the difference from gross in real time - as in gross minus everything else. Can this be done?

Thanks for your time and help!!

Ryan

 

[RP1America]

Okay, so I figured out part of my first question. I was able to get currency by using the following:

Private Sub gross_Exit(ByVal Cancel As MSForms.ReturnBoolean)

gross.Value = Format(CDbl(gross.Text), "Currency")

End Sub


Yet, I still need to know if it is possible to restrict user entry to 2 decimal places. And, if it is possible to have a running total show in real time in a userform.

Thanks!

Ryan

 

[SkipVought]

Hi,

The answer is yes and yes.

First you evaluate the data that the user enters and respond accordingly. You can use the textbox key events or wait until the user leaves the control to process your edits.

Second, keep in mind that the values in the textbox are, er, uh, TEXT. They are not numbers. So, maybe you store a 'shadow' numeric array of your text values and sum those values.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[RP1America]

Thank you for the response Skip!

However, I am quite new to VBA. Although I understand your point, I do not know how to achieve (code-wise) what you are describing.

I have decided to forgo restricting user entry to 2 decimal places. The "currency" code takes care of this when they exit the textbox, I was originally hoping to restrict to lessen entry errors...yet it is not necessary.

So on to adding (or subtracting in this case) values of 4 textboxes and having a running total show in another textbox after each value entered by the user. I understand that even though I have my text boxes set to only accept numeric values that VB recognizes those as text. I am a bit lost, however, on how to store a 'shadow' numeric array of the text values and then sum those values.

(I don't believe I mentioned this before, nor its relevance, but I am working in Word 2007)

Thanks!
Ryan

 

[SkipVought]

box1
box2
gross

As either box2 or box2 is exited, it sums to the gross. No shadow array. Just do the conversions and format.

Sub Initialize()
    box1.Text = Format(0, "currency")
    box2.Text = Format(0, "currency")
End Sub
Sub UpdateGross()
    gross.Text = Format(CCur(box1.Text) + CCur(box2.Text), "currency")
End Sub
Private Sub box1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    box1.Text = Format(box1, "currency")
    UpdateGross
End Sub
Private Sub box2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    box2.Text = Format(box2, "currency")
    UpdateGross
End Sub
Private Sub UserForm_Activate()
    Initialize
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[RP1America]

Absolutely wonderful Skip!

That worked perfectly! I really appreciate your help!!

Ryan