Programmatically format VBA buttons

**Belleye** · 09-21-2012, 08:22 AM

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?

**Admin** · 09-21-2012, 11:10 AM

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

**Belleye** · 09-21-2012, 01:23 PM

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.

**Admin** · 09-21-2012, 01:40 PM

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

**Rick Rothstein** · 09-21-2012, 02:00 PM

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.

**Belleye** · 09-21-2012, 02:03 PM

Sorted thanks very much +1

**Rick Rothstein** · 09-21-2012, 02:22 PM

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.

**Belleye** · 09-21-2012, 02:40 PM

LOL yes they did... +1s all round today

and that is what I was looking for thanks very much.

**snb** · 09-23-2012, 02:23 AM

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

**Belleye** · 09-25-2012, 11:18 AM

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!