CODEWITHSUNDEEP
excelvba
J.schmidt
J.schmidt

Reputation: 719

Is it possible to address a shape with VBA code?

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)

Upvotes: 2

Views: 2568

Answers (1)

SJR
SJR

Reputation: 23081

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

Upvotes: 4

Related Questions