Excel Macro Compatibility

[marc747]

Hi, I have a macro in excel that by clicking on it opens a folder to
insert a picture everything works great in excel 2000 but I have
people that are using excel 2003 or 2007 and a few that use excel for
Mac, on some it give a problem and on some it does not work at all.
Is there anything that I can insert in the macro to make it more
compatible with all different versions of excel.

Following is my Macro.

******************************************************************************
Sub InsertPicture()

Dim myPicture As String, MyObj As Object

Range("A14").Select

myPicture = Application.GetOpenFilename _
("Pictures (*.gif; *.jpg; *.bmp; *.tif),*.gif; *.jpg; *.bmp;
*.tif", _
, "Select Picture to Import")

If myPicture = "False" Then Exit Sub

ActiveSheet.Unprotect Password:="test"
Application.ScreenUpdating = False

Set MyObj = ActiveSheet.Pictures.Insert(myPicture)
With MyObj
With .ShapeRange
..LockAspectRatio = False
.Height = 170
.Width = 200
.Left = .Left + 2
.Top = .Top + 12

End With
.Placement = xlMoveAndSize
End With

Set MyObj = Nothing
Application.ScreenUpdating = True
ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:=
_
True, Password:="test"

End Sub

**********************************************************************************

 

[Dave Peterson]

I don't see anything wrong with your code and it worked ok for me in xl2003.

I would use the pictures collection directly, though:

Option Explicit
Sub InsertPicture()

Dim myPicture As Variant
Dim MyPict As Picture

myPicture = Application.GetOpenFilename _
(filefilter:="Pictures,*.gif; *.jpg; *.bmp; *.tif", _
Title:="Select Picture to Import")

If myPicture = False Then
Exit Sub
End If

ActiveSheet.Unprotect Password:="test"
Application.ScreenUpdating = False

ActiveSheet.Range("a14").Select

Set MyPict = ActiveSheet.Pictures.Insert(myPicture)
With MyPict
.Placement = xlMoveAndSize
With .ShapeRange
.LockAspectRatio = False
.Height = 170
.Width = 200
.Left = .Left + 2
.Top = .Top + 12
End With
End With

Set MyPict = Nothing
Application.ScreenUpdating = True
ActiveSheet.Protect DrawingObjects:=False, _
Contents:=True, Scenarios:=True, Password:="test"

End Sub

Do you know what fails and in what version of excel that it fails?

 

[p45cal]

Works fine here in xl2007

 

[marc747]

There is a button in the work sheet that when clicked on it runs the
macro and in different excl versions when I click on the button it
gives a message that there is no Macro, then I checked the macro and
it seems that all the macro that are in the work book have been
deleted.

How about, anyone can check it with Mac.

Thansk,

 

[Dave Peterson]

First, I don't use a mac.

Did you use a commandbutton from the control toolbox toolbar or a button from
the Forms toolbar?

I don't think Mac's have the control toolbox toolbar.

If this doesn't help (and I would be very surprised if it did!), then you may
want to ask your question in a newsgroup dedicated to Mac's.

Be sure to include your version of excel for the mac, too.

news://msnews.microsoft.com/microsoft.public.mac.office.excel

 

[marc747]

I used the button from the form toolbar.
I did some more reading and I found that macro created in Excel 2000
does not work in Mac Excel 2008.

I have 2 questions about my macro,
I am thinking of changing to the macro that you suggested but I want
to understand the difference between your suggestion and the one that
I have.
And, is there a code that opens the (inset/picture/from file...)
instead of the (File/open) window.

Thanks,

First, I don't use a mac.

Did you use a commandbutton from the control toolbox toolbar or a button from
the Forms toolbar?

I don't think Mac's have the control toolbox toolbar.  

If this doesn't help (and I would be very surprised if it did!), then youmay
want to ask your question in a newsgroup dedicated to Mac's.

Be sure to include your version of excel for the mac, too.

news://msnews.microsoft.com/microsoft.public.mac.office.excel

There is a button in the work sheet that when clicked on it runs the
macro and in different excl versions when I click on the button it
gives a message that there is no Macro, then I checked the macro and
it seems that all the macro that are in the work book have been
deleted.

How about, anyone can check it with Mac.

 

[Dave Peterson]

Application.Dialogs(xlDialogInsertPicture).Show

And IIRC, xl2008 on the Mac doesn't support VBA at all. But VBA will be
supported in future versions on the Mac (from what I've read...).

 

[marc747]

I tried to replace "myPicture = Application.GetOpenFilename _" with
"Application.Dialogs(xlDialogInsertPicture).Show" and it gives me a
error what I am doing wrong.

I am using your the following Macro that you wrote.
Thanks,
************************************************************************************
Option Explicit
Sub InsertPicture()

Dim myPicture As Variant
Dim MyPict As Picture

myPicture = Application.GetOpenFilename _
(filefilter:="Pictures,*.gif; *.jpg; *.bmp;
*.tif", _
Title:="Select Picture to Import")

If myPicture = False Then
Exit Sub
End If

ActiveSheet.Unprotect Password:="test"
Application.ScreenUpdating = False

ActiveSheet.Range("a14").Select

Set MyPict = ActiveSheet.Pictures.Insert(myPicture)
With MyPict
.Placement = xlMoveAndSize
With .ShapeRange
.LockAspectRatio = False
.Height = 170
.Width = 200
.Left = .Left + 2
.Top = .Top + 12
End With
End With

Set MyPict = Nothing
Application.ScreenUpdating = True
ActiveSheet.Protect DrawingObjects:=False, _
Contents:=True, Scenarios:=True, Password:="test"

End Sub

********************************************************************************

Application.Dialogs(xlDialogInsertPicture).Show

And IIRC, xl2008 on the Mac doesn't support VBA at all.  But VBA will be
supported in future versions on the Mac (from what I've read...).

 

[Dave Peterson]

That line of code (applicaiton.dialogs()) just shows the dialog that you wanted
to see.

I didn't realize that you were going to use it as a replacement in your code. I
wouldn't use in the code to insert a picture.

Why not use the .getopenfilename?

But if you want, you could use something like:

Dim res As Boolean
Dim myPict As Picture

res = Application.Dialogs(xlDialogInsertPicture).Show

If res = False Then
'user hit cancel
Exit Sub
End If

With ActiveSheet
Set myPict = .Pictures(.Pictures.Count)
End With

MsgBox myPict.TopLeftCell.Address 'or whatever you wanted to do