Formatting For UserForm Label Not Working

[marsow]

Hi everyone,


The code here works. But, I can never get the value (displayed in a userform label) to be formatted numerically with commas and 2 decimals (e.g. 1,500.00). I tried every code and placed it everywhere but fails everytime. A simple addition of 1000 + 1500 equals 2001.00. I don't know what else to do and it's been so long now. I would appreciate some help, please.

NewRetailAmt = Me.tbx_rtlamt.Value * Me.tbx_Qty.Value
lbl_sbtot.Caption = Val(lbl_sbtot.Caption) + NewRetailAmt

Legend:
NewRetailAmt - is a variable
tbx_rtlamt - textbox with numeric input
tbx_Qty - textbox with numeric input as multiplier
lbl_sbtot - label to display sum

I tried these lines of code with no luck:
'Me.lbl_sbtot.Value = Format(Me.lbl_sbtot, "#,##0.00")
Or
'lbl_sbtot = Format(lbl_sbtot, "Standard")

Best regards, -m.

 

[Logit]

.
This macro works here :

Option Explicit


Private Sub CommandButton1_Click()
Dim ans As String
    ans = (TextBox1.Text * TextBox2.Text)
    Label2.Caption = Format(ans, "Currency")
    
End Sub

 

[marsow]

Thanks for the reply @Logit. I'll try this.

 

[marsow]

I can't get this to work. How do we modify your code for a single textbox and one label?

 

[Logit]

.
Your posted code shows two textboxes used to multiply two numbers. Why are you asking about one textbox ?

The code I posted uses only one label. The name of it is Label2.

???

 

[mikerickson]

Textboxes and Labels do not contain formatted numbers like cells do, they contain strings.
So, if one uses code like Logit's to put the string "$10.00" in a textbox, Excel sees that as a string and

aNumber = TextBox1.Value * TextBox2.Value

will fail. Excel's automatic type conversion doesn't handle the "$".

One has to either use code like

aNumber = Val(Replace(TextBox1.Value, "$", "")) * Val(Replace(TextBox2.Value, "$", ""))

or one could put a "$" label before the TextBox's and force the contents of the textboxes to be two decimal place numerals.

 

[marsow]

Hi @Logit, I apologize for the confusion.

What I meant to say was, how do we modify your code to make label2.Caption add itself? And, make that answer show in the same label2.Caption?
Similar to what goes on in my code here:

NewRetailAmt = Me.tbx_rtlamt.Value * Me.tbx_Qty.Value
lbl_sbtot.Caption = Val(lbl_sbtot.Caption) + NewRetailAmt

your code:
ans = (TextBox1.Text * TextBox2.Text)
Label2.Caption = Format(ans, "Currency")

 

[marsow]

Hi @mikerickson,

I tried to adopt your code but no luck.
multamt = Val(Replace(tbx_rtlamt.Value, "$", "")) * tbx_Qty.Value
ans = Val(Replace(lbl_sbtot.Caption, "$", "")) + Val(Replace(multamt, "$", ""))
lbl_sbtot.Caption = VBA.Format(CStr(ans))

 

[Logit]

.
Ok ... interesting question and one that I had to give some thought to. That made it worthwhile to consider the answer.
Keep in mind the following is most likely only ONE WAY to get your answer and it may not be exactly what you are seeking.
Hope I got it right and perhaps someone else has a more direct approach ...

Paste the following into your UserForm code module.

The form has two textboxes, one label and one command button :

Option Explicit


Private Sub CommandButton1_Click()
Dim ans As String
Dim totl As String
    ans = (TextBox1.Text * TextBox2.Text)
    totl = Format(ans, "Currency") + Sheets("Sheet1").Range("A1").Value
    Label1.Caption = Format(totl, "Currency")
    
End Sub


Private Sub UserForm_Initialize()
Sheets("Sheet1").Range("A1").Value = "$50.00"
Label1.Caption = Sheets("Sheet1").Range("A1").Value
End Sub

When the UserForm initializes, it populates A1 with $50.00, from which Label1 draws from to display the caption of $50.00
Then when you enter the values in both textboxes, they are multiplied together and added to the value from A1.

This method allows you to change the value for A1 in the code .... or you could add another textbox (instead of using a label) and enter what would have been the
labelcaption (amount) in that additional textbox.

If you are trying to get away from the overall look of the textbox, you can always format the looks of the textbox to be FLAT, NO BORDERS and perhaps with a very light grey background color. It
would appear as a 'place holder' on the userform instead of just another textbox.

Does any of this help ?

 

[marsow]

Hi @Logit,

Thanks for taking the time. I will certainly give your suggestion a try. I'm also thinking about using another textbox instead of a label.
I just never thought this concern would require this much change.

Appreciate your help.