Altering Form Control Buttons

[HWL]

I am using the old style form control buttons and I want to be able to do things like change the text and color of the button depending on factors. It is easy enough to reference a controlbutton but how do I reference a form button? formbutton#?

 

[VoG]

Is this a button added from the Forms toolbar? If so I don't think that you can change the backcolor. Is it on a worksheet? If so look at the .Caption property.

 

[HWL]

Is this a button added from the Forms toolbar? If so I don't think that you can change the backcolor. Is it on a worksheet? If so look at the .Caption property.

There are 2 types of controls on Excel 2007, the form controls used in the earlier versions and the newer activex controls. I know how to refer to the activex controls but not the older versions. I inserted a form button into my ducument; default name Button 4. How do I refer to it in a macro?

 

[Rick Rothstein]

I am using the old style form control buttons and I want to be able to do things like change the text and color of the button depending on factors. It is easy enough to reference a controlbutton but how do I reference a form button? formbutton#?

I don't think you can change the color, but one way to change the displayed text would be like this (change the sheet and button names as appropriate)...

Worksheets("Sheet3").Shapes("Button 1").OLEFormat.Object.Caption = "Hello"

 

[HWL]

I don't think you can change the color, but one way to change the displayed text would be like this (change the sheet and button names as appropriate)...

Worksheets("Sheet3").Shapes("Button 1").OLEFormat.Object.Caption = "Hello"

Thanks Rick, works perfect. I still think there should be a way to change the color too, I mean if you can change the Caption propert.

 

[VoG]

Nope, you would would need an ActiveX control in order to do that.

 

[Rick Rothstein]

Thanks Rick, works perfect. I still think there should be a way to change the color too, I mean if you can change the Caption propert.

You can change the Font color, but I don't believe there is anyway to get to the button's color itself (has something to do with how its drawn onto the sheet I think). Anyway, if changing the color, and making it bold to standout, is sufficient, then...

With Worksheets("Sheet3").Shapes("Button 1").OLEFormat.Object
  .Caption = "Hello There"
  .Font.ColorIndex = 3
  .Font.Bold = True
End With

 

[HWL]

You can change the Font color, but I don't believe there is anyway to get to the button's color itself (has something to do with how its drawn onto the sheet I think). Anyway, if changing the color, and making it bold to standout, is sufficient, then...

With Worksheets("Sheet3").Shapes("Button 1").OLEFormat.Object
  .Caption = "Hello There"
  .Font.ColorIndex = 3
  .Font.Bold = True
End With

Weird, I used this code but then I removed it and yet the font.colorindex keeps changing back to the color I had with the code. Hmmm

 

[Rick Rothstein]

Weird, I used this code but then I removed it and yet the font.colorindex keeps changing back to the color I had with the code. Hmmm

Once the color is changed, it is change... the color (and other properties) are not kept "alive" by the code, only changed by it. To put things back the way they were originally, run this code...

With Worksheets("Sheet3").Shapes("Button 1").OLEFormat.Object
  .Caption = "Button 1"
  .Font.ColorIndex = xlColorIndexAutomatic
  .Font.Bold = False
End With