Making a text box non-printable with VBA - how?

[Bill Hamilton]

OK, I've written some simple code to create and insert a standard text box (not a 'control') onto a sheet using Shapes.addtextbox and I want to make it non-printable. I can't seem to find any construct to do it as none of the Shape or Shapes properties/methods seem to have anything to do with that.

I started the macro recorder and manually opened the box's Properties and unchecked Print Object but it recorded absolutely nothing. Very helpful.

Any ideas, O Mighty Coders?

(Using Excel 2007 under Win7)

Bill

 

[VoG]

Try like this

ActiveSheet.Shapes("TextBox 1").PrintObject = msoFalse

 

[Bill Hamilton]

Hi. Thanks for the response. Doesn't work unfortunately. Here's my code (reduced to the relevant essentials):

Sub TestTextBox()
Dim wSheet As Worksheet
Dim shp As Shape
Set wSheet = ActiveSheet
Set shp = wSheet.Shapes.addtextbox(msoTextOrientationHorizontal, _
                        200, 150, 300, 300)
       shp.Name = "Help Box"
       shp.TextFrame.Characters.Text = "Text for the box"
       ActiveSheet.Shapes("Help Box").PrintObject = msoFalse
'       shp.PrintObject = msoFalse
End Sub

Both the "ActiveSheet.Shapes("Help Box").PrintObject = msoFalse" as you suggested and my alternative "shp.PrintObject = msoFalse" instructions fail with "Object doesn't support his property or method" runtime error 438.

 

[VoG]

This worked for me

Sub TestTextBox()
Dim wSheet As Worksheet
Dim shp As Shape
Set wSheet = ActiveSheet
Set shp = wSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, _
                        200, 150, 300, 300)
       shp.Name = "Help Box"
       shp.TextFrame.Characters.Text = "Text for the box"
       shp.Select
       Selection.PrintObject = False
'       shp.PrintObject = msoFalse
End Sub

 

[Bill Hamilton]

Subtly different, but yes it does exactly what's required.

Thank you very much. Another satisfied customer.

Bill