Programmatically format VBA buttons

Hi everyone,

First time I've joined and posted on an excel forum :D
As the title suggests I'm having issues programmatically formatting buttons, rather than;

Code:

---

userform1.CommandButton1.backcolor = 12648447
userform1.CommandButton2.backcolor = 12648447

---

I would like to do something like:

Code:

---

For x = 1 To 2
        userform1.CommandButton & x.backcolor = 12648447
Next

---

Any suggestions?

Hi Belleye,

Welcome to ExcelFox !!!

this goes in Userform module.

Code:

---

Dim i  As Long
   
    With Me
        For i = 0 To .Controls.Count - 1
            If TypeName(.Controls(i)) = "CommandButton" Then
                .Controls(i).BackColor = RGB(0, 64, 0)
            End If
        Next
    End With

---

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://eileenslounge.com/viewtopic.php?p=318868#p318868
https://eileenslounge.com/viewtopic.php?p=318311#p318311
https://eileenslounge.com/viewtopic.php?p=318302#p318302
https://eileenslounge.com/viewtopic.php?p=317704#p317704
https://eileenslounge.com/viewtopic.php?p=317704#p317704
https://eileenslounge.com/viewtopic.php?p=317857#p317857
https://eileenslounge.com/viewtopic.php?p=317541#p317541
https://eileenslounge.com/viewtopic.php?p=317520#p317520
https://eileenslounge.com/viewtopic.php?p=317510#p317510
https://eileenslounge.com/viewtopic.php?p=317547#p317547
https://eileenslounge.com/viewtopic.php?p=317573#p317573
https://eileenslounge.com/viewtopic.php?p=317574#p317574
https://eileenslounge.com/viewtopic.php?p=317582#p317582
https://eileenslounge.com/viewtopic.php?p=317583#p317583
https://eileenslounge.com/viewtopic.php?p=317605#p317605
https://eileenslounge.com/viewtopic.php?p=316935#p316935
https://eileenslounge.com/viewtopic.php?p=317030#p317030
https://eileenslounge.com/viewtopic.php?p=317030#p317030
https://eileenslounge.com/viewtopic.php?p=317014#p317014
https://eileenslounge.com/viewtopic.php?p=316940#p316940
https://eileenslounge.com/viewtopic.php?p=316927#p316927
https://eileenslounge.com/viewtopic.php?p=316875#p316875
https://eileenslounge.com/viewtopic.php?p=316704#p316704
https://eileenslounge.com/viewtopic.php?p=316412#p316412
https://eileenslounge.com/viewtopic.php?p=316412#p316412
https://eileenslounge.com/viewtopic.php?p=316254#p316254
https://eileenslounge.com/viewtopic.php?p=316046#p316046
https://eileenslounge.com/viewtopic.php?p=317050&sid=d7e077e50e904a138c794e1 f2115da95#p317050
https://www.youtube.com/@alanelston2330
https://www.youtube.com/watch?v=yXaYszT11CA&lc=UgxEjo0Di9-9cnl8UnZ4AaABAg.9XYLEH1OwDIA35HNIei0z-
https://eileenslounge.com/viewtopic.php?p=316154#p316154
https://www.youtube.com/watch?v=TW3l7PkSPD4&lc=UgwAL_Jrv7yg7WWC8x14AaABAg
https://teylyn.com/2017/03/21/dollarsigns/#comment-191
https://eileenslounge.com/viewtopic.php?p=317050#p317050
https://eileenslounge.com/viewtopic.php?f=27&t=40953&p=316854#p316854
https://www.eileenslounge.com/viewtopic.php?v=27&t=40953&p=316875#p316875
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

Hey Admin,

Thanks for your reply, correct me if I'm wrong but the above code references the buttons by which order they where created in? I was hoping to reference the buttons by name, is this possible?

Thanks.

Hi,

Yes your assumption is right. You could also try this way as well

Code:

---

If .Controls(i).Name Like "abc*" Then 'begins with abc,"*abc" > ends with abc, "*abc*" contains abc
'//or use like
'If .Controls(i).Name = "abc" Then
    .Controls(i).BackColor = RGB(0, 64, 0)
End If

---

Quote:

---

Originally Posted by Belleye

Thanks for your reply, correct me if I'm wrong but the above code references the buttons by which order they where created in? I was hoping to reference the buttons by name, is this possible?

---

You can use the Controls collection to address a control direcly by name as well...

Code:

---

X = 2
Me.Controls("CommandButton" & X).BackColor = vbRed

---

So X could be the variable used as a For..Next loop counter as you seem to want.

Sorted thanks very much +1

Quote:

---

Originally Posted by Belleye

Sorted thanks very much +1

---

Based on who the Threaded View of this forum shows you responded to, I think our messages may have crossed... I think you will want to read the message I posted (Message #5) as I believe it does what you might have been originally looking for.

LOL yes they did... +1s all round today :) and that is what I was looking for thanks very much.

Just to add:

If you put this in the userform's codemodule you can use:

Code:

---

sub snb()
  For j = 1 To 2
    Me("CommandButton" &j).backcolor = 12648447
  Next
end sub

---

it's the shorthand for

Code:

---

sub snb_longer()
  For j = 1 To 2
    controls("CommandButton" &j).backcolor = 12648447
  Next
end sub

---

or

Code:

---

sub snb_still_longer()
  For j = 1 To 2
    Me.controls("CommandButton" &j).backcolor = 12648447
  Next
end sub

---

Thanks all that helped.... I guess it would be wrong to take and not give back; So here is the non-activeX calendar I was working on for you to use/modify/laugh at.

Cheers!