Excel Macro Compatibility

  • Thread starter Thread starter marc747
  • Start date Start date
M

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

**********************************************************************************
 
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?
 
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,
 
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
 
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.
Click to expand...
How about, anyone can check it with Mac.
Click to expand...
Click to expand...
 
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...).
 
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...).
Click to expand...
 
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
 
Back
Top