Excel 2007

[tommyreebok]

Having upgraded to excel 2007 from 2003, my macro (below) no longer performs the function it was intended to do.

It is a simple macro that unlocks a spreadsheet then allows the user to insert an image, then it adjusts the cell height and width to match before protecting the sheet again.

any ideas?

Private Sub CommandButton10_Click()
Range("H1").Select
Sheets("Expense Claim").Unprotect Password:="control1"
Range("H1").Select

'Dim strFile As String
Application.ScreenUpdating = False
On Error Resume Next
'varible Picture1 is inserted down below - ***change both***
Picture1 = Application.GetOpenFilename("Picture,*.JPG,Picture,*.JPEG,Picture,*.GIF,Picture,*.BMP")
'edit "("Picture,*.*")" section to add or chanve visible file types
On Error Resume Next
ActiveSheet.Pictures.Insert(Picture1).Select
Selection.ShapeRange.LockAspectRatio = msoTrue
Selection.ShapeRange.Height = 175
Selection.ShapeRange.Width = 234
Application.ScreenUpdating = True

Set Picture = Selection
'set cell height to picture size
Range("H1").Select
Picture.Top = Picture.TopLeftCell.Top
Picture.Left = Picture.TopLeftCell.Left
Picture.TopLeftCell.EntireRow.RowHeight = Picture.Height

Row = Row + 1


Range("H1").Select
Sheets("Expense Claim").Protect Password:="control1"
End Sub

 

[Jerry Sullivan]

Hello and welcome,

...my macro (below) no longer performs the function it was intended to do.

Please explain what happened when you tried this in xl2007 and how that differed from what used to happen in xl2003. I tried this in xl2007 and it inserted an image and resized the cell.

 

[tommyreebok]

the image was meant to be inserted into cell H1 but instead places the image into A1

 

[Jerry Sullivan]

Tommy,

I don't know why this worked for you in xl2003 but not xl2007, however if you don't mind my having re-written this code, you can try this and see if it works for you.

Generally it is better to avoid referencing Cells or Ranges with Selection and instead use a fully qualified reference to the Cell or Range as in the example below. I suspect that is why your existing code gave unpredictable results.

Private Sub CommandButton10_Click()
    Dim strPictureFilename As String
    Dim objPicture As Object
    Dim dblTop As Double, dblLeft As Double, dblWidthScale As Double
    Dim i As Long
 
    Application.ScreenUpdating = False
    strPictureFilename = Application.GetOpenFilename _
        ("Picture,*.JPG,Picture,*.JPEG,Picture,*.GIF,Picture,*.BMP")
    With Sheets("Expense Claim")
        .Unprotect Password:="control1"
        With .Range("H1")
            dblTop = .Top
            dblLeft = .Left
        End With
        Set objPicture = .Pictures.Insert(strPictureFilename)
        With objPicture
            .Top = dblTop
            .Left = dblLeft
            .Height = 175
            .Width = 234
        End With
        With .Range("H1")
            .EntireRow.RowHeight = objPicture.Height
            For i = 1 To 5
                dblWidthScale = .ColumnWidth / .Width
                .EntireColumn.ColumnWidth = objPicture.Width * dblWidthScale
            Next i
        End With
        .Protect Password:="control1"
    End With
    Set objPicture = Nothing
End Sub

I removed the statement that tried to lock the aspectratio because it was followed by statements that set the width and height. You'll probably want to chose one constraint of the other.

Hope this helps.

 

[tommyreebok]

works perfectly

thanks!