Changing the Color of a Dynamic Form Button

[DrRonald]

I am new to VBA. I am trying to figure out a way to change the background color of a button that I am programmatically adding to a Userform. An example of the code I am using is a follows:

Dim rnge As Range
Dim ws As Worksheet
Dim cControl As Control
Dim c As Integer
Dim NName As String
c = 1
Set ws = Worksheets("Main")
For Each rnge In ws.Range("RngeName")
    Set cControl = Me.Controls.Add("Forms.CommandButton.1", True)
        With cControl
            .Width = 50
            .Height = 20
            .Top = 20 + c * 40
            .Left = 60
            .Caption = rnge
            .Name = "but" & rnge
            .ZOrder (0)
        End With

    NName = cControl.Name

        With     ActiveWorkbook.VBProject.VBComponents("UserForm").CodeModule
            X = .CountOfLines
            .InsertLines X + 1, "Sub " & NName & "_Click()"
            .InsertLines X + 2, "UserForm.Hide"
            .InsertLines X + 3, "End Sub"
        End With
    c = c + 1
Next rnge

End Sub

I have read that you can't change the background of these buttons, but I know for a fact that you can change the color. I can do this by adding a line after the first with such as ".BackColor = "... The problem I am having is changing the color outside the For statement. That is, I want to change the color at some point after initially creating the buttons.

Also, I can't get the code which I am adding to those buttons to work. The "UserForm.Hide" is just a test, but it doesn't work. The code is added correctly, but the button still doesn't do anything. Thanks very much.

 

[RoryA]

If you use a naming convention, then you can later refer to the button by name to change colour. (the reference you have seen to being unable to alter the colour relates to Forms controls on sheets - not the same thing)
Do all your buttons perform basically the same action? If so , you can use a class module to handle their clicks rather than adding event code on the fly.

 

[DrRonald]

I have tried to change the colors by adding ".Name = AName" to the With Statement, however I have not been able to figure out what to put later in the code to then change the color. Would you give me an example? Also, The buttons will perform basically the same function, however I do not know how many buttons will be on the form. There is a list in one of the worksheets which has a number of names corresponding to the number of buttons that will be added. The number of names must be able to change. What specifically could I add to the code to cause these buttons to do things? Thank you very much.

 

[Norie]

When exactly do you want to change the background colour?

Would it be when some event occurs like the button is clicked?

By the way, why multiple buttons that all seem to be doing exactly the same thing - hide the userform?

Is that just an example and the buttons are actually going to do something else?

One more thing I've got to ask, sort of out of curiousity, why multiple buttons?

 

[DrRonald]

The color of the buttons will change when some event occurs at a later point in the code. Perhaps when a button is clicked or when the a value reaches a certain limit.

The act of hiding the form is just an example. They will actually be bringing up a different userform. The values and text on the userform will be different depending on what button was pressed. I could just add the buttons in directly to the form, but the number of buttons needed will change depending on the number of names in the list.

 

[Norie]

Couldn't you just use something like a listbox to display the names?

It's pretty straightforward to populate one and work with the item(s) the user has selected.

You also wouldn't need to add controls dynamically.

 

[DrRonald]

Sorry my computer is wigging out on me.

 

[DrRonald]

I have a list box in my userform also, but in this situation a button is really what I need.

 

[Norie]

Why do you need the buttons?

Am I missing something?

Is the listbox populated with the same data as you are using to create the command buttons?

 

[DrRonald]

No the listbox is something completely different in the code. I was just using it as an example. A listbox would work but it is not what I am looking for. The buttons are what I really want and I just need to figure out how to change the color, or for that matter how to make them do anything.
​