1

Hello Developers and other, :)

I'm developing a macro to create a lot of button automatically, and I wants to give them a specific background color.

  With Worksheets("Sheet 1")
    Set r_BtnRange = .Range("O3:O4")
    For Each r_Cell In r_BtnRange.Cells
        Set b_Btn = .Buttons.Add(r_Cell.Left, r_Cell.Top, _
                                        r_Cell.Width, r_Cell.Height)
        With b_Btn
            .Caption = "Email End-User"
            .OnAction = "Email_EndUser"
            .Font.Color = RGB(255, 255, 255)
            '.BackColor = 16711680
       End With
    Next
End With

The .Button.Add return a Button Object, witch has no BackColor Proprety (according to this: https://learn.microsoft.com/en-us/dotnet/api/microsoft.office.interop.excel.button?view=excel-pia and the fact I receive an error when tying to uncomment the line)

please see Here

I could use a Shape to do it, but I would know if there is really any method to do it (btw, had to disable it and modify multiple propriety of it.

Thanks a lot for your answers,

Antoine

Titou Titou
  • 23
  • 1
  • 8
  • 3
    See this post https://stackoverflow.com/questions/42350388/excel-vba-changing-color-for-commandbutton – mooseman Aug 09 '18 at 15:03
  • If you're using a Form Control button, you can't change the background color, either manually or with VBA. – BigBen Aug 09 '18 at 15:21
  • @mooseman I've see this post, please read mine carefully. Moreover I've added a picture to be more explicit. Thanks you for reply – Titou Titou Aug 09 '18 at 16:13
  • @BigBen Thos button are not created on a form, but directly on a sheet. It create a Button Object, please read my post carefully and check the Microsoft link. Whatever, you seem right, it look impossible to do. Thanks you for your reply too. – Titou Titou Aug 09 '18 at 16:15
  • 1
    1) I read your post carefully - btw that link is not VBA-specific, but Office Interop... In any case, the button you create is a Form Control type button, and as such you can't change the background color. Run your code as is and try to change the color manually - you won't be able to. – BigBen Aug 09 '18 at 16:18
  • I read your post and as BigBen has stated, the button you are using can't be changed. The link I put in was for an alternative button type that can have it's background color changed. – mooseman Aug 09 '18 at 16:22
  • Thanks for your reply! @BigBen wow, didn't even notice that! Well, thanks you both, I will try to create some button of an other type ^^ – Titou Titou Aug 10 '18 at 07:23

0 Answers0