VBA Excel: Progamatically create and place 100 buttons and set backgound color

Question

I need to generate buttons, labeled by data in the spreadsheet, and place them in specific cells. Each needs to have background color set programaticall based on code logic.
I can create them, size them, place them, and set font info:

              Set rt = WSOut.Range(sButtonCol & k).Cells
              Set btn = WSOut.Buttons.Add(rt.Left + 1, rt.Top + 1, 60, 8)
               btn.Select
               With btn
                  .OnAction = "ButtonAction"
                  .Caption = sTicker  
                  .Name = sTicker
                  .Font.ColorIndex = 1
                  .Font.Bold = True
                  .Font.Size = 7
               End With

But what I CAN NOT seem to find is how to set the background color of the button. This is essential for quickly picking out the button set needed from the 100 or so in the sheet. I've tried

btn.Interior.Color = RGB(0, 255, 0)

but that seems to apply to some sort of manually created buttons

This sheet needs to be regenerated with fresh data frequently, and the buttons recreated from the data. Works perfectly, except for the 100 buttons all being the same color.

Appreciate any help. I've spent 2 hours hunting with Dr. Goog but no luck.

Pete A

If I remember correctly, `Buttons` are form control buttons, and you can't change the background color, so you have to use ActiveX buttons, or shapes. — BigBen, May 20 '20 at 19:02

FunThomas · Answer 1 · 2020-05-22T08:41:45.833

As BigBen writes in his comment, you are creating a Form control button. For that, you can't change the background color, neither via VBA nor from within Excel.

First alternative is to create ActiveX buttons instead. This answer shows how to do so. Drawback: Every button needs an indiviual event routine. The answer includes the code that's needed to create such a routine on the fly. It uses the VBE library to inject the code. However, the usage of VBE is often forbidden on a computer.

Second alternative is to create simple shapes rather than buttons. You can assign a Macro to them like to a Form button. Code to create such shape:

Dim btn as Shape
Set btn = ws.Shapes.AddShape(msoShapeRoundedRectangle, 200, 200, 100, 50)

With btn
   .Name = sTicker
   .OnAction = "ButtonAction"
    With .TextFrame.Characters
        .Text = sTicker
        .Font.Bold = True
        .Font.Size = 7
        .Font.Color = vbBlue
    End With
   .Fill.ForeColor.RGB = vbRed
End With

Update: To place a shape into a cell, you can use left/top/width/height properties of the cell. To "glue" the shape to the cell, use the Placement property. Have a look to

Sub CreateShape(r As Range, caption As String, color As Long)
    Dim btn As Shape
    Set btn = r.Parent.Shapes.AddShape(msoShapeRoundedRectangle, _
                                       r.Left, r.Top, r.Width, r.Height)
    With btn
        .Name = "Btn" & caption
        .TextFrame.Characters.Text = caption
        .Placement = xlMoveAndSize
        .Fill.ForeColor.RGB = color
    End With
End Sub

Thanks for both solutions! Guess I understood that they are different animals, and did not want to deal with the active x stuff. Not sure how the shapes will work, as they are not tied to a cell (move with it when sorted, sized, etc.). Appreciate the response. --Pete A — user3099993, May 21 '20 at 21:18
Any idea how to anchor a shape to a Cell? Appreciate the response. --Pete A — user3099993, May 21 '20 at 21:35
Thank you! I think this solves my problem. I NEVER would have guessed this would be possible. — user3099993, May 24 '20 at 00:04

VBA Excel: Progamatically create and place 100 buttons and set backgound color

1 Answers1