How to set ActiveX caption & color via VBA ???

[Aid2Kids]

Hello everyone,

I've been using Excel/VBA for years now, but I've only just now starting to use an ActiveX control. Here's what I'm trying to do...

1) Create an ActiveX command button (on a sheet) via VBA,
2) Set the button's name,
3) Set the button's caption,
4) Set the button's background color.

Here's as far as I gotten...

Dim objButton As Object
Set objButton = Worksheets("TS_Dir").OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False, DisplayAsIcon:=False, Left:=350, Top:=120, Width:=50, Height:=20)

The above works, but I've been able to set the caption and color. I was expecting to use something along the lines of ...

object.Property = ...

...but this isn't working. Would someone please be so kind as to point me in a better direction?


Thanks!

 

[V_Malkoti]

Try:

Set objButton = Worksheets("TS_Dir").OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False, DisplayAsIcon:=False, Left:=350, Top:=120, Width:=50, Height:=20)
objButton.Object.Caption = "My Button"
objButton.Object.Font.Bold = True
objButton.Object.BackColor = &HFFFF&
objButton.Object.ForeColor = &HFF0000

 

[Aid2Kids]

V_Malkoti

THANK YOU for your prompt and most helpful response! Your code statements work beautifully.


Take care