Macro for Format Labels

[jdcar]

Hello everyone,

I have a userform that have a lot of labels to show the currency values.
For that I used the code below:

Private Sub UserForm_Initialize()
Dim xp, As Double
xp = Val(xp)
xp = CDbl(xp)
lbl1.Caption = xp
lbl2.Caption = xp
lbl3.Caption = xp
xp = Sheets("sheet1").Range("A21")
xp = Sheets("sheet1").Range("B21")
xp = Sheets("sheet1").Range("C21")
lbl1.Caption = Format(xp, "#,##0.00 €")
lbl2.Caption = Format(xp, "#,##0.00 €")
lbl3.Caption = Format(xp, "#,##0.00 €")

I wound like to have a macro to simplify the code. I use macro below but didn't worked. Is it possible to agregate all, with the same formats on the labels' values?

Sub FormatValue()
With UserForm3
ary = Array(.lbl1, .lbl2, .lbl3)
For i = 0 To UBound(ary)
ary(i).Caption = Format(i, "#,##0.00 €")
Next i
End With
End Sub

I would be very grateful for the help you can give to solve this problem. Many thanks

 

[DanteAmor]

Hi @jdcar .
I hope yor are well.

Change your Initialize event code to the following:

Private Sub UserForm_Initialize()
  Dim i As Long
 
  For i = 1 To 3   'Change the 3 to the total number of labels.
    Controls("lbl" & i).Caption = Format(Sheets("sheet1").Cells(21, i).Value, "#,##0.00 €")
  Next
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

 

[jdcar]

Hi @jdcar .
I hope yor are well.

Change your Initialize event code to the following:

Private Sub UserForm_Initialize()
  Dim i As Long
 
  For i = 1 To 3   'Change the 3 to the total number of labels.
    Controls("lbl" & i).Caption = Format(Sheets("sheet1").Cells(21, i).Value, "#,##0.00 €")
  Next
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

 

[jdcar]

Greetings Mr Dante
Possibly I did not explain myself well, because in the example of 3 labels that I exposed, the each one values are in diferent rows and columns in excell, (and they are 48 labels), and the objective was when the userform show the values in labels be currency formatting.
With your code I tried with the respective adaptation and it did not work.
in example of label's value: value "A" is in column 3 and row 7; value "B" is in column 5 and row 10.......and so on
thank you

 

[DanteAmor]

Possibly I did not explain myself well, because in the example of 3 labels that I exposed, the each one values are in diferent rows and columns

Exactly, you didn't put an explanation, but I deduced that it was the same row, since in your macro you have the same row:

So if you change the structure, neither my macro nor any other would have worked.

------------------------​

in example of label's value: value "A" is in column 3 and row 7; value "B" is in column 5 and row 10.......and so on

It is difficult to obtain a pattern with only 2 data.
I'm going to assume again that it's every every 3 rows and every 2 columns. Starting at row 7 and column 3.

So your data is something like this:

Dante Amor
	A	B	C	D	E	F	G
1
2
3
4
5
6			value "A" is in column 3 and row 7
7			2674097
8
9					value "B" is in column 5 and row 10
10					5979097
11
12							and so on
13							1425714
sheet1

------------------------​

Assuming labels from 1 to 48 named lbl1, lbl2, lbl3 and so on, you would have something like this:

------------------------​

Assuming the above, try the following:

Private Sub UserForm_Initialize()
  Dim n As Long, i As Long, j As Long
  
  i = 7             'start row
  j = 3             'start column
  For n = 1 To 3    'number of labels
    Controls("lbl" & n).Caption = Format(Sheets("sheet1").Cells(i, j).Value, "#,##0.00 €")
    i = i + 3       'every 3 rows
    j = j + 2       'every 2 columns
  Next
End Sub

------------------------​

Result:

------------------------​

If not the above. So, it would be great if you provide a picture of how your data is on the sheet and how you want the result in the labels.
What is the name of the 48 labels?

--------------
I hope to hear from you soon.
Respectfully
Dante Amor
--------------​

 

[jdcar]

Hi Mr Dante
The second post you give was must better, it works perfectly for what I want, but when I pretend use the values that are in a matrix type table, how do the code? Because the Initialize turns very long if I use individualy for each cell.
Is it possible help me?
Thank you

 

[DanteAmor]

but when I pretend use the values that are in a matrix type table, how do the code? Because the Initialize turns very long if I use individualy for each cell.

I definitely don't understand what you mean. The code has a loop to fill the labels.

But you still do not give examples as I told you in my previous post.

Although I would like to help you further, it is not possible, because you do not provide complete examples and the information you provide is incomplete.

 

[jdcar]

Product	MountV	Value	Value2	Value3
A	123,45​	156,01​	139,73​	26%
B	132,56​	258,32​	205,44​	95%
C	345,76​	430,74​	388,25​	25%
D	184,98​	274,73​	179,86​	49%
E	189,75​	250,20​	219,98​	32%
F	90,45​	145,43​	60,45​	61%

 

[jdcar]

Hi, Mr. Dante,
The values are included in the table above, in the range D4:H10 according to categories E5:E10 - MountV; F5:F10 -Value; .......
Whose values are represented in labels in the userform1
I hope I was clearer in the sense of solving the problem exposed.
I am very grateful for your patience, thank you.
Carlos

 

[jdcar]

Private Sub UserForm_Initialize()
'As you propose works well
    Dim n As Long, i As Long, j As Long
   
        i = 5             'start row
        j = 5             'start column

    For n = 1 To 6   'number of labels
        controls("lbl" & n).Caption = Format(Sheets("sheet1").Cells(i, j).Value, "#,##0.00 €")
            i = i + 1       'every 3 rows 'j = j + 1     'every 2 columns
    Next

        i = 5             'start row
        j = 6             'start column
        For n = 7 To 12   'number of labels
            controls("lbl" & n).Caption = Format(Sheets("sheet1").Cells(i, j).Value, "#,##0.00 €")
                i = i + 1       'every 3 rows 'j = j + 1     'every 2 columns
        Next

        i = 5             'start row
        j = 7             'start column
        For n = 13 To 18   'number of labels
            controls("lbl" & n).Caption = Format(Sheets("sheet1").Cells(i, j).Value, "#,##0.00 €")
                i = i + 1 'every 3 rows     'j = j + 1        every 2 columns
        Next

        i = 5             'start row
        j = 8            'start column
        For n = 19 To 24   'number of labels
            controls("lbl" & n).Caption = Format(Sheets("sheet1").Cells(i, j).Value, "#,##0.00 %")
                i = i + 1       'every 3 rows   'j = j + 1     'every 2 columns
              
        Next
        

End Sub