2

I am replacing all my ActiveX controls with Excel shapes, because of this well know problem. Therefore I replaced each ActiveX Button with a rectangular shape assigning a macro to each shape:

enter image description here

My question is if I can address those 'shape buttons' with my vba code. Something simple like change the backgroung color of the "Review Start" button should be possible, right?

I'm thinking of something like:

Activesheet.shapes("Review Start").background.colorindex = 1

(This code is obviously not working)

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
J.schmidt
  • 721
  • 5
  • 27
  • 2
    There is no `Background` property for [Shape](https://learn.microsoft.com/en-us/office/vba/api/excel.shape), but if you name your shape "Review Start" (and not only text) you should be able to access it with `Shapes("Review Start")` – Vincent G Feb 20 '19 at 14:53

1 Answers1

4

One way is this. Assign a variable to the shape and then you can access its properties and methods easily. I'm not sure there's a way without using RGB.

By declaring the variable as Shape type, Intellisense will show you the properties and methods. Also you can use the Object Browser (F2).

Sub x()

Dim s As Shape

Set s = ActiveSheet.Shapes("Review Start") 'better to specify a sheet name

With s
    .Fill.ForeColor.RGB = RGB(255, 255, 255)
    .TextFrame.Characters.Font.Color = vbBlack
    .TextFrame.Characters.Text = "Fred"
End With

End Sub
SJR
  • 22,986
  • 6
  • 18
  • 26
  • 3
    In addition, it's always good to do this way, then you can look at the locals window also whilst debugging. – Nathan_Sav Feb 20 '19 at 14:57
  • Thank you so so much! In addition I'd like to know, how I can change the "Value" / "Caption" of the Button. I don't see any related properties in the list. – J.schmidt Feb 20 '19 at 15:03
  • 1
    I've added to the code above. And remember to use more meaningful procedure and variable names than I have! – SJR Feb 20 '19 at 15:10