"reset" pictures programatically with vba

[twilsonco]

Hey guys,

I've got a workbook that runs on mac and pc. One of the problems with this is that pictures added in one platform lose their aspect ratio when opened on the other.

This can be fixed by using the "reset" button in the "format pictures" tab on the ribbon, but I don't know how to do that with vba. I gave up trying to compress pictures with vba, and this is another ribbon object so I don't know if this is possible.

The other alternative is to tag each picture so I know which platform is was imported in, that way I can check and resize when the workbook is opened in the other platform (the aspect ratio of a picture imported in windows and then opened in mac increases by a factor of ~1.226, so I can resize them again).

Another alternative is one that I don't know about that you awesome people can teach me! I like that one, or being able to access the "reset" button in the "format pictures" tab on the ribbon.

Thanks,

Tim



******** type="cosymantecnisbfw" cotype="cs" id="SILOBFWOBJECTID" style="width: 0px; height: 0px; display: block;">******** type="cosymantecnisbfw" cotype="cs" id="SILOBFWOBJECTID" style="width: 0px; height: 0px; display: block;"></object>

 

[shg]

Duplicate post

 

[twilsonco]

Oops. Browser went all non-responsive. How can I delete?

 

[Jerry Sullivan]

Perhaps shg meant "cross post", which is rarely caused by the browser acting alone.
"reset" pictures programmatically with vba

 

[twilsonco]

guess I should elaborate. Browser went non-responsive and I went non-patient, but the browser started it!

Wait, the cross post is because I also posted on excelforum.com isn't it... I always suspected they were too similar... I feel smart now.




******** type="cosymantecnisbfw" cotype="cs" id="SILOBFWOBJECTID" style="width: 0px; height: 0px; display: block;"></object>

 

[Worf]

An answer to this question was posted at ExcelForum, but non members can’t see code there, so, Twilsonco, if you could paste the final code here…</SPAN>
Anyway, I wrote something on this subject (untested on Mac):
</SPAN>

' this code goes at ThisWorkbook module
Dim ws As Worksheet
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Set ws = ThisWorkbook.Worksheets("Sheet1")
If IsWin Then           ' save picture size at Windows
    With ws.Shapes("Picture 5")
        ws.Range("d4").Value = .Height
        ws.Range("d5").Value = .Width
    End With
End If
End Sub
Private Sub Workbook_Open()
Set ws = ThisWorkbook.Worksheets("Sheet1")
If Not IsWin Then       ' adjust picture size at Mac
    With ws.Shapes("Picture 5")
        .Height = ws.Range("d4").Value
        .Width = ws.Range("d5").Value
    End With
End If
End Sub
Function IsWin() As Boolean
If Application.OperatingSystem Like "*Win*" Then
    IsWin = True
Else
    IsWin = False
End If
End Function

 

[twilsonco]

For those trying to do the same thing as me, which is to have a semi-complex userform and pictures that work across Mac and Windows (and multiple versions of windows XL), here's some nice info:

i) Preprocessor commands (with a # symbol before them) are a handy way to make your code dynamic for a given platform, e.g.:

[COLOR=#323333][FONT=Courier]#If Mac Then[/FONT][/COLOR]
[COLOR=#323333][FONT=Courier]   winPC = False[/FONT][/COLOR]
[COLOR=#323333][FONT=Courier]#Else[/FONT][/COLOR]
[COLOR=#323333][FONT=Courier]   winPC = True[/FONT][/COLOR]
[COLOR=#323333][FONT=Courier]#End if[/FONT][/COLOR]

ii) Userforms themselves change size between Mac and Windows. This code by Ron de Bruin is very handy for that.

iii) Pictures are also resized (aspect ratio changes) between Mac and Windows. For example, a picture imported into XL in Windows will experience an aspect ratio (width/height) change of ~1.226 when the XL file is opened in Mac. Similarly, if imported into XL in Mac and opened in Windows, the change is ~/1.226, or ~0.8155. The following code demonstrates one way of dealing with this:

[COLOR=#323333][FONT=Courier]For Each ws In .Worksheets[/FONT][/COLOR]
[COLOR=#323333][FONT=Courier]    With ws[/FONT][/COLOR]
[COLOR=#323333][FONT=Courier]        For Each Sh In .Shapes[/FONT][/COLOR]
[COLOR=#323333][FONT=Courier]            With Sh[/FONT][/COLOR]
[COLOR=#323333][FONT=Courier]                If .Type = msoPicture Then[/FONT][/COLOR]
[COLOR=#323333][FONT=Courier]                    If winPC Then[/FONT][/COLOR]
[COLOR=#323333][FONT=Courier]                        .ScaleHeight 0.8155, msoFalse, msoScaleFromTopLeft[/FONT][/COLOR]
[COLOR=#323333][FONT=Courier]                    Else[/FONT][/COLOR]
[COLOR=#323333][FONT=Courier]                        .ScaleHeight 1.226, msoFalse, msoScaleFromTopLeft[/FONT][/COLOR]
[COLOR=#323333][FONT=Courier]                    End If[/FONT][/COLOR]
[COLOR=#323333][FONT=Courier]                End If[/FONT][/COLOR]
[COLOR=#323333][FONT=Courier]            End With[/FONT][/COLOR]
[COLOR=#323333][FONT=Courier]        Next Sh[/FONT][/COLOR]
[COLOR=#323333][FONT=Courier]    End With[/FONT][/COLOR]
[COLOR=#323333][FONT=Courier]Next ws[/FONT][/COLOR]

(I changed the height, but the same result can be achieved by scaling the width)


Thanks again!

Tim




******** type="cosymantecnisbfw" cotype="cs" id="SILOBFWOBJECTID" style="width: 0px; height: 0px; display: block;"></object>