Results 1 to 10 of 10

Thread: Programmatically format VBA buttons

  1. #1
    Junior Member
    Join Date
    Sep 2012
    Posts
    5
    Rep Power
    0

    Question 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?

  2. #2
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,122
    Rep Power
    10
    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
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  3. #3
    Junior Member
    Join Date
    Sep 2012
    Posts
    5
    Rep Power
    0
    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.

  4. #4
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,122
    Rep Power
    10
    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
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  5. #5
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    659
    Rep Power
    13
    Quote Originally Posted by Belleye View Post
    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.
    Last edited by Rick Rothstein; 09-21-2012 at 02:09 PM.

  6. #6
    Junior Member
    Join Date
    Sep 2012
    Posts
    5
    Rep Power
    0
    Sorted thanks very much +1

  7. #7
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    659
    Rep Power
    13
    Quote Originally Posted by Belleye View Post
    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.
    Last edited by Rick Rothstein; 09-21-2012 at 02:32 PM.

  8. #8
    Junior Member
    Join Date
    Sep 2012
    Posts
    5
    Rep Power
    0
    LOL yes they did... +1s all round today and that is what I was looking for thanks very much.

  9. #9
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    12
    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

  10. #10
    Junior Member
    Join Date
    Sep 2012
    Posts
    5
    Rep Power
    0

    Solved

    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!
    Attached Files Attached Files

Similar Threads

  1. Replies: 5
    Last Post: 04-18-2013, 02:30 AM
  2. Clearing UsedRange Of WorkSheet Programmatically
    By tfurnivall in forum Excel Help
    Replies: 1
    Last Post: 12-04-2012, 09:05 AM
  3. HP Calculator Buttons
    By Howardc in forum Word Help
    Replies: 0
    Last Post: 12-01-2012, 11:12 AM
  4. Add ribbon programmatically to Excel 2010 using VBA
    By heapifyman in forum Excel Ribbon and Add-Ins
    Replies: 6
    Last Post: 07-18-2011, 09:16 PM
  5. Social Network Buttons
    By Excel Fox in forum Den Of The Fox
    Replies: 1
    Last Post: 07-10-2011, 10:15 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •