macro for print a shape or an image in a workbook [SOLVED]

[hoomantt]

i have an image in my workbook and want a macro to print it.just print it on A4 paper.and how much it can, print it full screen.(with maximum height and maximum width)

 

[Hui]

Hoomantt


Select a range of cells just bigger than the image/shape

Goto Page Layout

set the Page size, Orientation and Margins to Narrow

Adjust the Scale until the picture fits the page

Use the Print preview to check

 

[hoomantt]

hi dear Hui;

my image is a map of a country and it is not so big that there should be some data around it,and when we want to print it,just want to print that map with best and biggest size on A4.therefore please help me if you want,please....

 

[SirJB7]

Hi, hoomant!

I'm working on the adaptation of a part of another project that might help you. If not today, almost surely tomorrow I'll be posting it here. If I succeed, which I hope.

Regards!

 

[hoomantt]

Thanks my dear friend,

my friend Sir SirJB7,thanks...

 

[SirJB7]

Hi, hoomantt!


It isn't yet finished and I won't be able to update it until Monday, but here is what I've done yet.

https://dl.dropboxusercontent.com/u/60558749/macro%20for%20print%20a%20shape%20or%20an%20image%20in%20a%20workbook%20%28for%20hoomantt%20at%20chandoo%29.xlsm


A matter of scaling and zoom it's still pending.


Regards!

 

[Hui]

And what happened when you followed my instructions above ?

 

[hoomantt]

oh thanks , that is very good and usefull dear professor SirJB7.

you are the one.the best.the biggest man in the world.

thanks aot.and if you want please go on and help me.

thanks thanks and thanks....

 

[hoomantt]

mr Hui,'i want to print just the shape or image , not around it.aroun this shape are text and numbers and formulas.like a page of newspaper.you said true but that isnot usefull for me.

thanks alot about you help and read my question .

thanks....

 

[SirJB7]

Hi, hoomantt!


Download again the updated file from same previous link.


This is the code:

-----

[pre]

Option Explicit

Sub PrintMaximizedImages()
'
' constants
Const kiZoomMax = 400
Const kiZoomMin = 10
'
' declarations
Dim iOrientation As Integer, sPrintArea As String, vZoom As Variant
Dim iFitToPagesWide As Integer, iFitToPagesTall As Integer
Dim shp As Shape
Dim I As Integer
'
' start
With ActiveSheet.PageSetup
iOrientation = .Orientation
sPrintArea = .PrintArea
vZoom = .Zoom
iFitToPagesWide = .FitToPagesWide
iFitToPagesTall = .FitToPagesTall
End With
'
' process
With ActiveSheet.PageSetup
For I = 1 To ActiveSheet.Shapes.Count
Set shp = ActiveSheet.Shapes(I)
If shp.Width <= shp.Height Then .Orientation = xlPortrait Else .Orientation = xlLandscape
.PrintArea = Range(shp.TopLeftCell.Address, shp.BottomRightCell.Address).Address
.Zoom = kiZoomMax
Do Until .Pages.Count = 1 Or .Zoom = kiZoomMin
.PrintArea = ""
.PrintArea = Range(shp.TopLeftCell.Address, shp.BottomRightCell.Address).Address
.Zoom = .Zoom - 1
Loop
If .Pages.Count > 1 Then
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
End If
ActiveSheet.PrintPreview True
Next I
End With
'
' end
With ActiveSheet.PageSetup
.Orientation = iOrientation
.PrintArea = sPrintArea
.Zoom = vZoom
.FitToPagesWide = iFitToPagesWide
.FitToPagesTall = iFitToPagesTall
End With
Set shp = Nothing
Beep
'
End Sub

[/pre]
-----


Remember that little images won't get printed at full page size since Excel handles zoom values up to 400%. In the same way very big images that zoomed to 10% of it's original size which still don't fit in a single page will be printed at the minimum number of pages.


Just advise if any issue.


Regards!

 

[hoomantt]

thanks alot my dear friens SirJB7.

you are the best,the one and the kindest man in the world.

you are a hero .

my hero and thanks a lot.......

 

[SirJB7]

Hi, hoomantt!

Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.

Regards!

 

[hoomantt]

dear Sirjb7

hi and have a nice day

when i want print a special image or shape in my workbook that there is in (for example in worksheet1) and that name is (for example shape1 or chart1 or ... )what should i do?

would you please give me that macro?

there might be several shapes in a page , but i wanna print a specail of them!

can you please help me??!!

 

[SirJB7]

Hi, hoomantt!


For printing once or a just a few times one or just a few images you should use Hui's method which BTW is the same and I think the only one available that I embedded in the macro. For printing all the images in a worksheet you should opt for the macro.


Now if you want an intermediate option I'll describe you the required steps and leave the implementation as your homework:

- Put a command button on any worksheet (always ActiveX and not form controls)

- In the click event of the command button initialize and show the user form of next step

- Add an user form to the project, with two drop down list box: 1st for worksheets and 2nd for shapes in selected worksheet

- In the initialize event of the user form load the 1st list box control with the names of the worksheets that you want to include (all with a For...Next loop, maybe excluding some of them, or just manually for the desired ones)

- In the change event of the 1st control load the 2nd list box control with the names fo the images/shapes in the form (same previous method, and can "borrow" parts of the code from my original macro

- Put a command button on the user form to print the selected image of the selected worksheet

- In the click event of that command button add my macro code, replacing the loop and constants by selecting the worksheet and image from the list controls.


Do you accept the challenge? If not, well, give me a few days and maybe I'll be coming back with that, but for the time being I'm unable to do it.


Regards!