Excel VBA format numbers to specific value in UserForm

Question

I have UserForm that dispalys values out of cells on my sheet "Price calculations". First column A on sheet "Price calculation" has text in rows from 72 to 88. All the rest of the columns D, E, F, G, H... etc has numbers I want to format.

I want to format cells to specific format for values with 17 rows k=17 (columns D, E, F, G, H... etc) is my code. I want to use format Format(XXX.Value, "#,##0.00") so my numbers will look like 71 000,00 instead of 71000

Private Sub UserForm_Initialize()

Dim vDB As Variant, a As Variant, c As Variant
Dim Ws As Worksheet
Dim i As Integer, j As Integer, n As Integer
Dim k As Integer

Set Ws = ThisWorkbook.Sheets("Price calculation")

a = Array("a", "d", "e", "f", "g", "h", "i", "j", "k", "l", "m", "n") 'column characters
c = Array(409, 444, 480, 516, 552, 588, 624, 660, 696, 732, 768, 804) 'label numbers

For i = LBound(a) To UBound(a)
    If i = 0 Then
        k = 16
    Else
        k = 17
    End If
    vDB = Ws.Range(a(i) & 72).Resize(k)
    n = 0
    For j = c(i) To c(i) + k - 1
        n = n + 1
        Me.Controls("Label" & j).Caption = vDB(n, 1)
    Next j
Next i

Ha, good answer. I asked because you seem familiar with Format. But not sure what you are doing anyway. Are you trying to format elements of an array? — SJR
– SJR, Commented Nov 27, 2018 at 10:45
If you want to format on your labels you are probably looking for: Me.Controls("Label" & j).Caption = Format$(vDB(n, 1), "#,##0.00") — Pᴇʜ
– Pᴇʜ, Commented Nov 27, 2018 at 10:49
That seems to derive from a particular school of programming. Not sure any of us are entirely clear what is being asked here. — SJR
– SJR, Commented Nov 27, 2018 at 11:04

Pᴇʜ · Accepted Answer · 2018-11-28 08:23:48Z

1

Use

Worksheets("YourSheetName").Range("D72:N88").NumberFormat = "#,##0.00"

to format the cells D72:N88 in worksheet YourSheetName.

Or

Me.Controls("Label" & j).Caption = Format$(vDB(n, 1), "#,##0.00")

If you want to format on your labels.

edited Nov 28, 2018 at 8:23

answered Nov 27, 2018 at 12:14

Pᴇʜ

57.9k10 gold badges55 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

10101 Over a year ago

Format$(vDB(n, 1), "#,##0.00") - it is actually this one. I didn't know you have to insert $ after Format O_o

Pᴇʜ Over a year ago

@user7202022 The issue was that you asked to "format cells" but you actually wanted to format the "labels". You should be clear with the terms you use! • Actually the only difference is that Format$() returns a String while Format() returns a Variant. Both should work but Format$() is a little faster in this case.

10101 Over a year ago

Ok. Thank you! I have to improve my English language skills as well. A lot to improve tho

Collectives™ on Stack Overflow

Excel VBA format numbers to specific value in UserForm

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related