VBA to change Print Object property of a picture

[nickthebizz]

Hi there

I need some help to write a code which I can assign to a button in excel and do the following

I have a workbook with a few sheets. On the top left corner of each sheet I have a pictures which acts as a header for my printed page. The thing i need is to
add a button where i can change the printobject to false in all pictures in the workbook(note that this pictures-Header is the same one in all sheets). So with this way I can choose to either print
all sheets without the pictures or if I would like to print all pages with the picture.

Does anyone have any idea how I could make this?

thank you

Nickthebizz

 

[Micron]

This might get you started. Note: it assumes your pictures are of type object. If not, I don't know if this would work. You'd need to change the sheet reference either by name or use ActiveSheet if the code is in a sheet module. This code would change this property in all shapes that are part of the loop. If you have some that you don't want to be affected, you'd need a list (an array is probably better) of shape names that you only want to affect. I'd put this code in a separate sub that will accept the shape name(s) and act on them. The line that sets the print property will cycle the property from true to false and back.

Dim obj As Object
For Each obj In Sheets("Sheet 3").DrawingObjects
     'Debug.Print obj.Name & "   " & obj.printObject
     obj.printObject = Not obj.printObject
Next

 

[nickthebizz]

This might get you started. Note: it assumes your pictures are of type object. If not, I don't know if this would work. You'd need to change the sheet reference either by name or use ActiveSheet if the code is in a sheet module. This code would change this property in all shapes that are part of the loop. If you have some that you don't want to be affected, you'd need a list (an array is probably better) of shape names that you only want to affect. I'd put this code in a separate sub that will accept the shape name(s) and act on them. The line that sets the print property will cycle the property from true to false and back.

Dim obj As Object
For Each obj In Sheets("Sheet 3").DrawingObjects
     'Debug.Print obj.Name & "   " & obj.printObject
     obj.printObject = Not obj.printObject
Next

Dear Micron, I have added the code and it works perfect!

 

[Micron]

Glad I could help & thanks for the recognition.