Hide shapes (buttons) before printing

[Ken Loomis]

So I now have shapes that acts as buttons on my worksheet thanks to help I
got from this group.

Now I want to hide those shapes before I print out the report. So I plan to
add a macro, called from yet another button, to do a print preview.

I'd like to hide those buttons before I do the print preview and then unhide
them at the end of that macro.

From what I am begining to understand about the Exel object model, I suspect
there is a collection of shapes on the workseeht and that each shape in that
collection has a 'visible' property. Since all od the shaps on this
worksheet are button, I will want to hide all of them. And, I may even get
button happy and add more shapes.

So my question is:

How do I hide/unhide all the shapes on a worksheet?

Thanks,
Ken Loomis

 

[kkknie]

You pretty much have it already. Here's the code:

Sub test()
Dim s As Shape
For Each s In ActiveSheet.Shapes
s.Visible = msoFalse
Next
End Sub

Use msoTrue to set them back to visible.

 

[Geof Wyght]

Won't the PrintObject property of the button do? Set it to
False. An educated guess.
Geof.

 

[Tom Ogilvy]

To expand on Geof's suggestion, right click on the shape and select Format
AutoShape. Go the properties tab and unselect Print Object.

Or you can run this one time

Sub Tester2()
Dim shp As Shape
For Each shp In ActiveSheet.Shapes
On Error Resume Next
shp.ControlFormat.PrintObject = False
On Error GoTo 0
Next
End Sub

 

[Ken Loomis]

I discovered that after I posted. And yes, I can set that manually from the
worksheet using the Format Autoshape & Properties tab.

That is neat beacuse it always shows the shapes but never prints them.

Thanks for the help.

 

[Ken Loomis]

Thanks, Tom.

That sub will help since I am still adding buttons to this worksheet and
won't need to individually set that as I go. Just run it at the end when I
am done.

Ken Loomis

 

[Ken Loomis]

Thanks. That works great.

Ken Loomis

 

[amigo_ako]

Hi,

I would like to ask if how can i hide all the shapes/button in the workbook, wherein the workbook have 8 worksheets and some of the worksheets have buttons/shapes needed to be hidden.

Please help. Thanks.

Or please can someone developed this code I have made. Make it simple.
If cmdNo = "ON" Then
Sheets(1).Activate
Sheets(1).Buttons.Visible = True
Sheets(3).Buttons.Visible = True
Sheets(5).Buttons.Visible = True
Sheets(7).Buttons.Visible = True
Else
Sheets(1).Activate
Sheets(1).Buttons.Visible = False
Sheets(3).Buttons.Visible = False
Sheets(5).Buttons.Visible = False
Sheets(7).Buttons.Visible = False
End If