Formatting a Shape (Command button) using VBA

Question

I am simply trying to create a rectangle shape in a specific worksheet, formatting it & assign a macro to it using VBA.

Here goes my attempt :

Set t = wsJTO.Range("H" & 5 & ":G" & 6)    
Set btn = wsJTO.Shapes.AddShape(msoShapeRectangle, t.Left, t.Top, t.Width, t.Height)
With btn.ShapeRange.Fill
    .Visible = msoTrue
    .ForeColor.RGB = RGB(146, 208, 80)
    .Transparency = 0
    .Solid
End With
With btn.ShapeRange.ThreeD
    .BevelTopType = msoBevelArtDeco
    .BevelTopInset = 9
    .BevelTopDepth = 6
End With
btn.OnAction = "Module2.Selection_JTO"

According to the debugger, there is an error with the third line & I don't seem to understand what's wrong with it. Help would be appreciated

Keyser Soze · Accepted Answer · 2023-02-09 21:53:48Z

2

Silly me ... All I had to do was get rid of "ShapeRange" since it doesn't have a fill property. The following code does the job if it might serve someone in the future :

Set t = wsJTO.Range("H" & 5 & ":G" & 6)

Set btn = wsJTO.Shapes.AddShape(msoShapeRectangle, t.Left, t.Top, t.Width, t.Height)
With btn.Fill
    .Visible = msoTrue
    .ForeColor.RGB = RGB(146, 208, 80)
    .Transparency = 0
    .Solid
End With
With btn.ThreeD
    .BevelTopType = msoBevelArtDeco
    .BevelTopInset = 9
    .BevelTopDepth = 6
End With
btn.OnAction = "Module2.Selection_JTO"

answered Feb 9, 2023 at 21:53

Keyser Soze

2723 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Formatting a Shape (Command button) using VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related