How to use CommandButton instead of TextBox on Chart?

[HerryMarkowitz]

Hi beautiful people,
1- Open enclosed file.
2- Run Macro1.

Please Login or Register  to view this content.

3- Click "Sent Chart To Desktop" in order to sent Chart to the desktop as picture.
4- See that you have a chart picture in your desktop.
Everything is okey until now...

Question: How to use CommandButton instead of TextBox above code?
Note: Command Button must be able to move with chart!

Thanks in advance...

[Andy Pope]

You can not embed forms controls in to a chart object.
So you can add the button to the worksheet but it will not move with the chart unless you group them.

You could add a shape that looks like a button and has macro assigned.

Please Login or Register  to view this content.

You can incorporate that into your existing code

[HerryMarkowitz]

Hi Andy,
Thanks for the code.
I need CommandButton instead of TextBox because CommandButton has PrintObject:False property.
Because I dont want to see TextBox on the Chart when printing.
So, There is no difference between using TextBox or soShapeBevel...
Do you have any other idea?
BR

[Nivvi]

Hi,

But shapes can be set not to print too.

Edit: Same with text box

Edit2:

Please Login or Register  to view this content.

[HerryMarkowitz]

Hi Nivvi,
Thanks for the code.
Following code works if there is only TextBox on the Sheet.

Please Login or Register  to view this content.

Following code doesnt work if TextBox located on the Chart.

Please Login or Register  to view this content.

So, this issue havent been solved yet...

[Andy Pope]

Does the macro assigned to the shape print the chart? If so make the shape invisible whilst printing

[HerryMarkowitz]

Does the macro assigned to the shape print the chart? If so make the shape invisible whilst printing

But user must see following text on the chart;
"Print Chart as Picture to the Desktop"
So, making shape invisible is not good idea.

[Andy Pope]

I mean make it invisible whilst exporting or printing. Once that is done you make the button visible again

[HerryMarkowitz]

I mean make it invisible whilst exporting or printing. Once that is done you make the button visible again

Sorry, I cant understand what you mean.
Can you post a vba code for this?

[Andy Pope]

Not without seeing your macro2.
I assume that this original code line assigned the print macro to the text box.

Please Login or Register  to view this content.

So Macro2 would go something like

Please Login or Register  to view this content.

[HerryMarkowitz]

Hi Andy,
Following code doesnt work if TextBox located on the Chart.

Please Login or Register  to view this content.

Any idea?

[Andy Pope]

I suggested an alternative, did that not work?

[HerryMarkowitz]

It is working...
But if there are more than one shape on the chart then all shapes will be invisible...
So, I need a code which must include TextBox(1)...

[Andy Pope]

So change the code to only make those shapes you don't want invisible.

If shape(1) is not your textbox then change the code

Please Login or Register  to view this content.

[HerryMarkowitz]

Thanks a lot! Solved