Many labels on form.. want to change backcolor of labels thru loop 1

[Aamir Saeed]

hello,

using vba, i want to change back color of labels by using for-next loop

Trying and getting error.

for counter = 1 to 5
label & format(counter)
next counter

Please see attached file generating error, help/hint/guidance requested.

Regards
Aamir Saeed

 

[Andrzejek]

Risky opening macro-enabled files....

If you have on your Form:
[tt]label1
label2
label3
label4
label5[/tt]

You may try:

Dim i As Integer

For i = 1 To 5
    Me.Controls("label" & i).BackColor = vbRed
Next i

But if that is the case, I would rather name the labels with more meaningful names.
You can still loop thru them if you want to change all or just some of them in the code.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Aamir Saeed]

Dear Andy,

Thanks a lot, I understand macro-enabled file may be harmful.
I will surely try what you suggested and let you know.

Meantime, please see attached JPEG image. Please note code marked with red rectangle.

Kindly highlight mistake i am doing in writing that line as i am unable to correct it.
Also, what is a good/easy to understand source to learn this type of syntax?

Regards.
Aamir

 

[Andrzejek]

What are you trying to accomplish?
I see the message box with "Total Controls on Form" by Me.Controls.Count
and I see you want to count the labels on your Form?

try

Option Explicit

Private Sub CommandButton1_Click()
Dim intLabelsCount As Integer
Dim c As MSForms.Control

MsgBox "Total Controls on Form " & Me.Controls.Count

For Each c In Me.Controls
    If TypeOf c Is MSForms.Label Then
        intLabelsCount = intLabelsCount + 1
    End If
Next c

MsgBox "Total Lable Count is " & intLabelsCount

End Sub

By the way, this is what your line is really saying:
[tt]Dim intLabelsCount [red]As Variant[/red], TotalControls [red]As Variant[/red], Counter As Integer[/tt]


---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Andrzejek]

Your line:
[tt]For Counter = 1 To TotalControls[/tt]

gives you an error. Controls are 0 based.
Try:

[tt]For Counter = [highlight #FCE94F]0[/highlight] To TotalControls [highlight #FCE94F]-1[/highlight][/tt]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Aamir Saeed]

Dear Andy

Many thanks for helping me out, i tried and it worked smoothly.

Actually i am a dairy milk producer and trying to build a small database for recording
a) date, shift and animal wise milk produced
b) Milk internally consumed and delivered to distributor
c) feed consumed (item wise)

At present i am doing it myself by recording directly on excel sheets. What i am trying to develop will help me/workers/users to enter on form (minimizing mistakes through validation) Ideally all these should improvide quality of data, reduce processing time and so can make various reports timely for decision making.

I know for a newbie/amateur like me its a difficult task and time consuming too, but its fun too and refreshes.

Once again, thanks for your help.

Best regards
Aamir Saeed

 

[Andrzejek]

I am glad I could help. And welcome to Tek-Tips
Come back if you have any other questions. Here are a lot of people willing to help.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson