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

Won't the PrintObject property of the button do? Set it to
False. An educated guess.
Geof.
-----Original Message-----
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


.

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.

"Geof Wyght" wrote in message
...
Won't the PrintObject property of the button do? Set it to
False. An educated guess.
Geof.
-----Original Message-----
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


.

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.



--
kkkni
-----------------------------------------------------------------------
kkknie's Profile: http://www.excelforum.com/member.php...nfo&userid=754
View this thread: http://www.excelforum.com/showthread.php?threadid=26695

Thanks. That works great.

Ken Loomis


"kkknie" wrote in message
...

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.

K


--
kkknie
------------------------------------------------------------------------
kkknie's Profile:
http://www.excelforum.com/member.php...fo&userid=7543
View this thread: http://www.excelforum.com/showthread...hreadid=266952

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


--
Regards,
Tom Ogilvy

"Ken Loomis" wrote in message
...
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

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

"Tom Ogilvy" wrote in message
...
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


--
Regards,
Tom Ogilvy

"Ken Loomis" wrote in message
...
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