start Excel command via VBA

Question

Hi,

I am having difficulties to get my Excle 2010 workbook to run on Excel 2011 for the MAC.

The Workbook contains a lot of VBA routines and I am finding quite a few incompatibilties.

The worst part is that there is no documantation. Does anybody know a VBA book for Excel 2011?

For example how to use applescript in conjunction with VBA?

This is my current problem:

I would like to invoke the Picture/ Crop command via VBA.

No problem in Excel 2010, but I cannot find a solution for Excel 2011. This is the code I am using:

Sub StartCroptMode()

#If Win32 Or Win64 Then           ‘für Excel 2010

          ActiveSheet.Shapes("MyPicture").Select         'selects picture with name "MyPicture"

Application.CommandBars.ExecuteMso ("PictureCrop")      'starts crop mode

#Else                ‘für Excel 2011 on the MAC

ActiveSheet.Shapes("MyPicture").Select

Application.CommandBars.FindControl(, 732).Execute        'creates error

#End If

End Sub

Thanks for the help

Michael

Answer 1

Thanks Jim,

I am aware that you can crop the picture within VBA. In this case the user should crop the picture himself. I just wanted to offer the functionality without finding the command within the ribbon after activating the picture.

I find the combination of commands, ribbon and context menues in Excel 2011 quite disturbing. Just got used to the ribbon....... 

Michael

PS: Your link helped me with another issue. Thanks!

Answer 2

Ah, then you are using the control exactly the way it ought to be used, which is a great idea. Here are the IDs for crop controls for Excel 2010:

MacroPlay	button	186
MacroPlay	button	186
PictureCropTools	splitButton	18131
PictureCrop	toggleButton	732
PictureCrop	toggleButton	732
PictureCropAspectRatioMenu	menu	18612
PictureCropAspectRatio1To1	button	18613
PictureCropAspectRatio2To3	button	18614
PictureCropAspectRatio3To4	button	22569
PictureCropAspectRatio3To5	button	18615
PictureCropAspectRatio4To5	button	18616
PictureCropAspectRatio3To2	button	22194
PictureCropAspectRatio4To3	button	22570
PictureCropAspectRatio5To3	button	22195
PictureCropAspectRatio5To4	button	22196
PictureCropAspectRatio16To9	button	22571
PictureCropAspectRatio16To10	button	22572
PictureFillCrop	button	18654
PictureFitCrop	button	18653

In general, if a control works in 2010 and it exists in 2011, VBA should be able to display it and it should work the same in VBA in both versions. When that is not the case, you should report it as a bug using the Send Feedback option from Excel's Help menu.

Crop controls are all new in 2011 (there are 4 kinds of crop instead of just one, so I was a bit surprised to see only one ID for PictureCrop for 2010), plus the VBA editor and compiler are brand new. Microsoft may not be aware this particular ID is not working in 2011, so please take the time to report the problem.

I didn't try all the controls in 2010 or 2011, so first try in 2010 to see if it works. I don't know if 2010 got all 4 crop types that are new in 2011, so if they don't exist yet in 2010, I would not expect them to work in 2011 VBA.

Answer 3

I am getting the message.

       Runtime Error 91

       “Object Variable or with block variable not set”

I got the control number from a complete listing of controls:

Picture
&From File...	<br>2619
&Color	<br>1403
&Automatic	<br>1365
&Grayscale	<br>1366
&Black and White	<br>1367
&Washout	<br>1368
&More Contrast	<br>1064
&Less Contrast	<br>1065
&More Brightness	<br>1066
&Less Brightness	<br>1067
&Crop	<br>732
Rotate &Left 90°	<br>199

Some work just fine (2619, 199) even they are also from the ribbon.

Others have the same error message. Do they require a different syntax or is this just a bug? Why does Microsoft make it so difficult?

Thanks

Michael

Answer 4

Will this help?

Sub CropThem()

Dim shp As Shape

Dim sngMemoLeft As Single

Dim sngMemoTop As Single

For Each shp In ActiveSheet.Shapes

With shp

sngMemoLeft = .Left

sngMemoTop = .Top

With .PictureFormat

.CropLeft = 10

.CropTop = 10

.CropBottom = 10

.CropRight = 10

End With

.Left = sngMemoLeft

.Top = sngMemoTop

End With

Next shp

End Sub

Giving credit where it is due. This is from:

http://visualbasic.ittoolbox.com/groups/technical-functional/vb-vba-l/crop-picture-with-vba-excel-3556360

Answer 5

That control appears to have been removed from XL2011, when the function was moved to the ribbon.

I don't know of any way to execute a similar control, though you can still programmatically crop a picture a fixed amount.