0

I can select a shape normally with following VBA

ActiveSheet.Shapes(Application.Caller).Select

I want to select a chart and shape the inside the chart. I am not finding line of code.

I tried the following but no success.

ActiveSheet.ChartObjects(Application.Caller).Select

Additional information:

Actually my chart is locked and I want to keep it locked and my sheet it protected(edit object is unchecked). I inserted shape inside the chart. My target is to edit shape inside the chart in locked mode with application.caller. So I can select the shape then I ll be able to edit it

I 'll have more than 10 shapes in chart and shapes are added dynamically with add shape button with random names. so i can target shape with its name. that is why I want to user application.caller to auto select it

Thanks

braX
  • 11,506
  • 5
  • 20
  • 33
HeavyLoad Collection
  • 67
  • 10

1 Answers1

1
Sub ChartClick()

    Dim cht As Chart, clr

    Set cht = ActiveSheet.Shapes(Application.Caller).Chart

    cht.Shapes("myShape").Select

'    With cht.Shapes("myShape")
'        clr = .Fill.ForeColor.RGB
'        .Fill.ForeColor.RGB = IIf(clr = vbRed, vbBlue, vbRed)
'    End With

End Sub

...assuming your macro is linked to the chartobject and not directly to the shape contained in the chart.

You can (and should) work directly with cht and not use ActiveChart. There's almost never any need to select/activate anything in excel in order to work with it.

How to avoid using Select in Excel VBA

Tim Williams
  • 154,628
  • 8
  • 97
  • 125
  • Actually my chart is locked and I want to keep it locked and my sheet it protected(edit object is unchecked). I inserted shape inside the chart. My target is to edit shape inside the chart in locked mode with application.caller. So I can select the shape then I ll be able to edit it. – HeavyLoad Collection Apr 19 '20 at 22:43
  • I know we need to avoid select normally but here I am manually editing shape so select is necessary – HeavyLoad Collection Apr 19 '20 at 22:51
  • Thanks for the update. but I 'll have more than 10 shapes in chart and shapes are added dynamically with add shape button with random names. so i can target shape with its name cht.Shapes("myShape").Select that is why I want to user application.caller to auto select it. hope you get my words. – HeavyLoad Collection Apr 19 '20 at 22:57
  • Also, I am planning to link macro to every shape added inside the chart with add shape button. – HeavyLoad Collection Apr 19 '20 at 22:59
  • The difficulty with this is there's no way to use `ActiveSheet.Shapes(callerName)` if the clicked shape is located in a chartobject. You will need to check each chart on the sheet and its contained shapes for a name match. Even with a locked sheet though, you can still just click on the shape to edit it - no need for a macro? – Tim Williams Apr 19 '20 at 23:03
  • i feel very bad that there are a lot of limitations with VBA. and feel like giving up the project . – HeavyLoad Collection Apr 19 '20 at 23:05
  • You should not consider unfamiliarity with how it works as "limitations" - we all had to learn the same way how to do things. – Tim Williams Apr 19 '20 at 23:07
  • i know in locked sheet we can edit shape unless we have shape property is set to locked and edit objects are unchecked with protection. – HeavyLoad Collection Apr 19 '20 at 23:10
  • Actually my reason for inserting shapes in the chart is linked to this thread I started today https://stackoverflow.com/questions/61305081/can-we-change-shape-or-object-group-border-color-like-this but with this thread also there is no direct solution as of limitation with vba. – HeavyLoad Collection Apr 19 '20 at 23:15
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/212037/discussion-between-heavyload-collection-and-tim-williams). – HeavyLoad Collection Apr 19 '20 at 23:30