Trying to Insert,resize cell and photo using VBA

Topic

New Reply

I’m using Excel 2007 and I’m trying to figure out if there is a way to insert a photo in a randomly selected cell and have both the photo and cell be re-sized the same using VBA
Example below
.Height = Application.InchesToPoints(1.5)
.Width = Application.InchesToPoints(1.75)
Currently I’m using code below to insert a photo into a pre sized cell, preselected cell.
Using my present method takes up to much space if photos are not used, so what I’m trying to do is save open space on a sheet if areas are not used for photos
Any possible suggestions

Code:

Dim sFile As Variant, r As Range
  Set r = Range("A1")
  
  sFile = Application.GetOpenFilename(FileFilter:="Pic Files (*.jpg;*.bmp), *.jpg;*.bmp", Title:="Browse to select a picture")
  If sFile = False Then Exit Sub
  ActiveSheet.Unprotect ""
  Dim i As Long
  For i = ActiveSheet.Pictures.Count To 1 Step -1
    If ActiveSheet.Pictures(i).Top = r.Top And _
        ActiveSheet.Pictures(i).Left = r.Left Then
      ActiveSheet.Pictures(i).Delete
    End If
  Next i
  With ActiveSheet.Pictures.Insert(sFile).ShapeRange
    .LockAspectRatio = True
    .Top = r.Top
    .Left = r.Left
    .Height = r.RowHeight * r.MergeArea.Rows.Count
  End With
  ActiveSheet.Protect ""

Reply | Quote

Replies

I’m using Excel 2007 and I’m trying to figure out if there is a way to insert a photo in a randomly selected cell and have both the photo and cell be re-sized the same using VBA
Example below
.Height = Application.InchesToPoints(1.5)
.Width = Application.InchesToPoints(1.75)
Currently I’m using code below to insert a photo into a pre sized cell, preselected cell.
Using my present method takes up to much space if photos are not used, so what I’m trying to do is save open space on a sheet if areas are not used for photos
Any possible suggestions

Code:

Dim sFile As Variant, r As Range
  Set r = Range("A1")
  
  sFile = Application.GetOpenFilename(FileFilter:="Pic Files (*.jpg;*.bmp), *.jpg;*.bmp", Title:="Browse to select a picture")
  If sFile = False Then Exit Sub
  ActiveSheet.Unprotect ""
  Dim i As Long
  For i = ActiveSheet.Pictures.Count To 1 Step -1
    If ActiveSheet.Pictures(i).Top = r.Top And _
        ActiveSheet.Pictures(i).Left = r.Left Then
      ActiveSheet.Pictures(i).Delete
    End If
  Next i
  With ActiveSheet.Pictures.Insert(sFile).ShapeRange
    .LockAspectRatio = True
    .Top = r.Top
    .Left = r.Left
    .Height = r.RowHeight * r.MergeArea.Rows.Count
  End With
  ActiveSheet.Protect ""

Reply | Quote

I’m using Excel 2007 and I’m trying to figure out if there is a way to insert a photo in a randomly selected cell and have both the photo and cell be re-sized the same using VBA
Example below
.Height = Application.InchesToPoints(1.5)
.Width = Application.InchesToPoints(1.75)
Currently I’m using code below to insert a photo into a pre sized cell, preselected cell.
Using my present method takes up to much space if photos are not used, so what I’m trying to do is save open space on a sheet if areas are not used for photos
Any possible suggestions

Code:

Dim sFile As Variant, r As Range
  Set r = Range("A1")
  
  sFile = Application.GetOpenFilename(FileFilter:="Pic Files (*.jpg;*.bmp), *.jpg;*.bmp", Title:="Browse to select a picture")
  If sFile = False Then Exit Sub
  ActiveSheet.Unprotect ""
  Dim i As Long
  For i = ActiveSheet.Pictures.Count To 1 Step -1
    If ActiveSheet.Pictures(i).Top = r.Top And _
        ActiveSheet.Pictures(i).Left = r.Left Then
      ActiveSheet.Pictures(i).Delete
    End If
  Next i
  With ActiveSheet.Pictures.Insert(sFile).ShapeRange
    .LockAspectRatio = True
    .Top = r.Top
    .Left = r.Left
    .Height = r.RowHeight * r.MergeArea.Rows.Count
  End With
  ActiveSheet.Protect ""

Reply | Quote

I’m using Excel 2007 and I’m trying to figure out if there is a way to insert a photo in a randomly selected cell and have both the photo and cell be re-sized the same using VBA
Example below
.Height = Application.InchesToPoints(1.5)
.Width = Application.InchesToPoints(1.75)
Currently I’m using code below to insert a photo into a pre sized cell, preselected cell.
Using my present method takes up to much space if photos are not used, so what I’m trying to do is save open space on a sheet if areas are not used for photos
Any possible suggestions

Code:

Dim sFile As Variant, r As Range
  Set r = Range("A1")
  
  sFile = Application.GetOpenFilename(FileFilter:="Pic Files (*.jpg;*.bmp), *.jpg;*.bmp", Title:="Browse to select a picture")
  If sFile = False Then Exit Sub
  ActiveSheet.Unprotect ""
  Dim i As Long
  For i = ActiveSheet.Pictures.Count To 1 Step -1
    If ActiveSheet.Pictures(i).Top = r.Top And _
        ActiveSheet.Pictures(i).Left = r.Left Then
      ActiveSheet.Pictures(i).Delete
    End If
  Next i
  With ActiveSheet.Pictures.Insert(sFile).ShapeRange
    .LockAspectRatio = True
    .Top = r.Top
    .Left = r.Left
    .Height = r.RowHeight * r.MergeArea.Rows.Count
  End With
  ActiveSheet.Protect ""

Reply | Quote

I’m using Excel 2007 and I’m trying to figure out if there is a way to insert a photo in a randomly selected cell and have both the photo and cell be re-sized the same using VBA
Example below
.Height = Application.InchesToPoints(1.5)
.Width = Application.InchesToPoints(1.75)
Currently I’m using code below to insert a photo into a pre sized cell, preselected cell.
Using my present method takes up to much space if photos are not used, so what I’m trying to do is save open space on a sheet if areas are not used for photos
Any possible suggestions

Code:

Dim sFile As Variant, r As Range
  Set r = Range("A1")
  
  sFile = Application.GetOpenFilename(FileFilter:="Pic Files (*.jpg;*.bmp), *.jpg;*.bmp", Title:="Browse to select a picture")
  If sFile = False Then Exit Sub
  ActiveSheet.Unprotect ""
  Dim i As Long
  For i = ActiveSheet.Pictures.Count To 1 Step -1
    If ActiveSheet.Pictures(i).Top = r.Top And _
        ActiveSheet.Pictures(i).Left = r.Left Then
      ActiveSheet.Pictures(i).Delete
    End If
  Next i
  With ActiveSheet.Pictures.Insert(sFile).ShapeRange
    .LockAspectRatio = True
    .Top = r.Top
    .Left = r.Left
    .Height = r.RowHeight * r.MergeArea.Rows.Count
  End With
  ActiveSheet.Protect ""

Reply | Quote

I’m using Excel 2007 and I’m trying to figure out if there is a way to insert a photo in a randomly selected cell and have both the photo and cell be re-sized the same using VBA
Example below
.Height = Application.InchesToPoints(1.5)
.Width = Application.InchesToPoints(1.75)
Currently I’m using code below to insert a photo into a pre sized cell, preselected cell.
Using my present method takes up to much space if photos are not used, so what I’m trying to do is save open space on a sheet if areas are not used for photos
Any possible suggestions

Code:

Dim sFile As Variant, r As Range
  Set r = Range("A1")
  
  sFile = Application.GetOpenFilename(FileFilter:="Pic Files (*.jpg;*.bmp), *.jpg;*.bmp", Title:="Browse to select a picture")
  If sFile = False Then Exit Sub
  ActiveSheet.Unprotect ""
  Dim i As Long
  For i = ActiveSheet.Pictures.Count To 1 Step -1
    If ActiveSheet.Pictures(i).Top = r.Top And _
        ActiveSheet.Pictures(i).Left = r.Left Then
      ActiveSheet.Pictures(i).Delete
    End If
  Next i
  With ActiveSheet.Pictures.Insert(sFile).ShapeRange
    .LockAspectRatio = True
    .Top = r.Top
    .Left = r.Left
    .Height = r.RowHeight * r.MergeArea.Rows.Count
  End With
  ActiveSheet.Protect ""

Reply | Quote

I’m using Excel 2007 and I’m trying to figure out if there is a way to insert a photo in a randomly selected cell and have both the photo and cell be re-sized the same using VBA
Example below
.Height = Application.InchesToPoints(1.5)
.Width = Application.InchesToPoints(1.75)
Currently I’m using code below to insert a photo into a pre sized cell, preselected cell.
Using my present method takes up to much space if photos are not used, so what I’m trying to do is save open space on a sheet if areas are not used for photos
Any possible suggestions

Code:

Dim sFile As Variant, r As Range
  Set r = Range("A1")
  
  sFile = Application.GetOpenFilename(FileFilter:="Pic Files (*.jpg;*.bmp), *.jpg;*.bmp", Title:="Browse to select a picture")
  If sFile = False Then Exit Sub
  ActiveSheet.Unprotect ""
  Dim i As Long
  For i = ActiveSheet.Pictures.Count To 1 Step -1
    If ActiveSheet.Pictures(i).Top = r.Top And _
        ActiveSheet.Pictures(i).Left = r.Left Then
      ActiveSheet.Pictures(i).Delete
    End If
  Next i
  With ActiveSheet.Pictures.Insert(sFile).ShapeRange
    .LockAspectRatio = True
    .Top = r.Top
    .Left = r.Left
    .Height = r.RowHeight * r.MergeArea.Rows.Count
  End With
  ActiveSheet.Protect ""

Reply | Quote

I’m using Excel 2007 and I’m trying to figure out if there is a way to insert a photo in a randomly selected cell and have both the photo and cell be re-sized the same using VBA
Example below
.Height = Application.InchesToPoints(1.5)
.Width = Application.InchesToPoints(1.75)
Currently I’m using code below to insert a photo into a pre sized cell, preselected cell.
Using my present method takes up to much space if photos are not used, so what I’m trying to do is save open space on a sheet if areas are not used for photos
Any possible suggestions

Code:

Dim sFile As Variant, r As Range
  Set r = Range("A1")
  
  sFile = Application.GetOpenFilename(FileFilter:="Pic Files (*.jpg;*.bmp), *.jpg;*.bmp", Title:="Browse to select a picture")
  If sFile = False Then Exit Sub
  ActiveSheet.Unprotect ""
  Dim i As Long
  For i = ActiveSheet.Pictures.Count To 1 Step -1
    If ActiveSheet.Pictures(i).Top = r.Top And _
        ActiveSheet.Pictures(i).Left = r.Left Then
      ActiveSheet.Pictures(i).Delete
    End If
  Next i
  With ActiveSheet.Pictures.Insert(sFile).ShapeRange
    .LockAspectRatio = True
    .Top = r.Top
    .Left = r.Left
    .Height = r.RowHeight * r.MergeArea.Rows.Count
  End With
  ActiveSheet.Protect ""

Reply | Quote

Morning

I was trying to do something similar in Excel 2003 and came across this article'Resize Image‘, I cannot claim to have done it with 100% accuracy because I am somewhat “code challenged”. Maybe you could adapt the suggestion to suit for Excel 2007

Editted: Amend Typo

Reply | Quote

Hi stevehocking
I thank you so much for this info, I well check it out, I am also somewhat “code challenged”. lol, getting better

Reply | Quote

Hi stevehocking
I thank you so much for this info, I well check it out, I am also somewhat “code challenged”. lol, getting better

Reply | Quote

Hi stevehocking
I thank you so much for this info, I well check it out, I am also somewhat “code challenged”. lol, getting better

Reply | Quote

Hi stevehocking
I thank you so much for this info, I well check it out, I am also somewhat “code challenged”. lol, getting better

Reply | Quote

Hi stevehocking
I thank you so much for this info, I well check it out, I am also somewhat “code challenged”. lol, getting better

Reply | Quote

Hi stevehocking
I thank you so much for this info, I well check it out, I am also somewhat “code challenged”. lol, getting better

Reply | Quote

Hi stevehocking
I thank you so much for this info, I well check it out, I am also somewhat “code challenged”. lol, getting better

Reply | Quote

Hi stevehocking
I thank you so much for this info, I well check it out, I am also somewhat “code challenged”. lol, getting better

Reply | Quote

Morning

I was trying to do something similar in Excel 2003 and came across this article'Resize Image‘, I cannot claim to have done it with 100% accuracy because I am somewhat “code challenged”. Maybe you could adapt the suggestion to suit for Excel 2007

Editted: Amend Typo

Reply | Quote

Morning

I was trying to do something similar in Excel 2003 and came across this article'Resize Image‘, I cannot claim to have done it with 100% accuracy because I am somewhat “code challenged”. Maybe you could adapt the suggestion to suit for Excel 2007

Editted: Amend Typo

Reply | Quote

Morning

I was trying to do something similar in Excel 2003 and came across this article'Resize Image‘, I cannot claim to have done it with 100% accuracy because I am somewhat “code challenged”. Maybe you could adapt the suggestion to suit for Excel 2007

Editted: Amend Typo

Reply | Quote

Morning

I was trying to do something similar in Excel 2003 and came across this article'Resize Image‘, I cannot claim to have done it with 100% accuracy because I am somewhat “code challenged”. Maybe you could adapt the suggestion to suit for Excel 2007

Editted: Amend Typo

Reply | Quote

Morning

I was trying to do something similar in Excel 2003 and came across this article'Resize Image‘, I cannot claim to have done it with 100% accuracy because I am somewhat “code challenged”. Maybe you could adapt the suggestion to suit for Excel 2007

Editted: Amend Typo

Reply | Quote

Morning

I was trying to do something similar in Excel 2003 and came across this article'Resize Image‘, I cannot claim to have done it with 100% accuracy because I am somewhat “code challenged”. Maybe you could adapt the suggestion to suit for Excel 2007

Editted: Amend Typo

Reply | Quote

Morning

I was trying to do something similar in Excel 2003 and came across this article'Resize Image‘, I cannot claim to have done it with 100% accuracy because I am somewhat “code challenged”. Maybe you could adapt the suggestion to suit for Excel 2007

Editted: Amend Typo

Reply | Quote

After reviewing the link you provided I was able to come up with a work around method that will serve my purpose. I first selected a row I wanted to insert photos in and then I set up width and height of selected cells to fit the size I needed for selected photos. Then by using a Check box (format control) using unchecked print and added a macro that will hide or unhide selected row using.

Code:

   Dim Rng As Range
    Dim MyCell As Range
    Set Rng = Range("A1")
    For Each MyCell In Rng
        If MyCell.Value = "" Then
            MyCell.EntireRow.Hidden = ActiveSheet.CheckBoxes(Application.Caller).Value = xlOn
        End If
    Next MyCell

I then inserted a command buttons, within selected cells in unhidden row, labeled “insert photo here” and set properties – PrintObject to False, then set Format Controls – properties to “move and size with cell”. This way if no photo is needed within this row I leave row hidden and command buttons. If I need a photo inserted within this row I unhide row and command buttons by using check box. Then I can select command button to insert photo. I’m using a code that adjusts photo size to selected cell. So far it seems to work OK. Here’s the code I use to insert and size photo.

Code:

    Dim PicLocation As String
    Dim MyRange As String
    
    ActiveSheet.Range("A1").Select
    MyRange = Selection.Address

    PicLocation = Application.GetOpenFilename(FileFilter:="Pic Files (*.jpg;*.bmp), *.jpg;*.bmp", Title:="Browse to select a picture")
    
    If PicLocation  "False" Then
        ActiveSheet.Pictures.Insert(PicLocation).Select
    Else
        Exit Sub
    End If
    
    With Selection.ShapeRange
        .LockAspectRatio = msoTrue
        If .Width > .Height Then
            .Width = Range(MyRange).Width
            If .Height > Range(MyRange).Height Then .Height = Range(MyRange).Height
        Else
            .Height = Range(MyRange).Height
            If .Width > Range(MyRange).Width Then .Width = Range(MyRange).Width
        End If
    End With
    
    With Selection
        .Placement = xlMoveAndSize
        .PrintObject = True
    End With
    
    Range("A2").Select

Reply | Quote

After reviewing the link you provided I was able to come up with a work around method that will serve my purpose. I first selected a row I wanted to insert photos in and then I set up width and height of selected cells to fit the size I needed for selected photos. Then by using a Check box (format control) using unchecked print and added a macro that will hide or unhide selected row using.

Code:

   Dim Rng As Range
    Dim MyCell As Range
    Set Rng = Range("A1")
    For Each MyCell In Rng
        If MyCell.Value = "" Then
            MyCell.EntireRow.Hidden = ActiveSheet.CheckBoxes(Application.Caller).Value = xlOn
        End If
    Next MyCell

I then inserted a command buttons, within selected cells in unhidden row, labeled “insert photo here” and set properties – PrintObject to False, then set Format Controls – properties to “move and size with cell”. This way if no photo is needed within this row I leave row hidden and command buttons. If I need a photo inserted within this row I unhide row and command buttons by using check box. Then I can select command button to insert photo. I’m using a code that adjusts photo size to selected cell. So far it seems to work OK. Here’s the code I use to insert and size photo.

Code:

    Dim PicLocation As String
    Dim MyRange As String
    
    ActiveSheet.Range("A1").Select
    MyRange = Selection.Address

    PicLocation = Application.GetOpenFilename(FileFilter:="Pic Files (*.jpg;*.bmp), *.jpg;*.bmp", Title:="Browse to select a picture")
    
    If PicLocation  "False" Then
        ActiveSheet.Pictures.Insert(PicLocation).Select
    Else
        Exit Sub
    End If
    
    With Selection.ShapeRange
        .LockAspectRatio = msoTrue
        If .Width > .Height Then
            .Width = Range(MyRange).Width
            If .Height > Range(MyRange).Height Then .Height = Range(MyRange).Height
        Else
            .Height = Range(MyRange).Height
            If .Width > Range(MyRange).Width Then .Width = Range(MyRange).Width
        End If
    End With
    
    With Selection
        .Placement = xlMoveAndSize
        .PrintObject = True
    End With
    
    Range("A2").Select

Reply | Quote

After reviewing the link you provided I was able to come up with a work around method that will serve my purpose. I first selected a row I wanted to insert photos in and then I set up width and height of selected cells to fit the size I needed for selected photos. Then by using a Check box (format control) using unchecked print and added a macro that will hide or unhide selected row using.

Code:

   Dim Rng As Range
    Dim MyCell As Range
    Set Rng = Range("A1")
    For Each MyCell In Rng
        If MyCell.Value = "" Then
            MyCell.EntireRow.Hidden = ActiveSheet.CheckBoxes(Application.Caller).Value = xlOn
        End If
    Next MyCell

I then inserted a command buttons, within selected cells in unhidden row, labeled “insert photo here” and set properties – PrintObject to False, then set Format Controls – properties to “move and size with cell”. This way if no photo is needed within this row I leave row hidden and command buttons. If I need a photo inserted within this row I unhide row and command buttons by using check box. Then I can select command button to insert photo. I’m using a code that adjusts photo size to selected cell. So far it seems to work OK. Here’s the code I use to insert and size photo.

Code:

    Dim PicLocation As String
    Dim MyRange As String
    
    ActiveSheet.Range("A1").Select
    MyRange = Selection.Address

    PicLocation = Application.GetOpenFilename(FileFilter:="Pic Files (*.jpg;*.bmp), *.jpg;*.bmp", Title:="Browse to select a picture")
    
    If PicLocation  "False" Then
        ActiveSheet.Pictures.Insert(PicLocation).Select
    Else
        Exit Sub
    End If
    
    With Selection.ShapeRange
        .LockAspectRatio = msoTrue
        If .Width > .Height Then
            .Width = Range(MyRange).Width
            If .Height > Range(MyRange).Height Then .Height = Range(MyRange).Height
        Else
            .Height = Range(MyRange).Height
            If .Width > Range(MyRange).Width Then .Width = Range(MyRange).Width
        End If
    End With
    
    With Selection
        .Placement = xlMoveAndSize
        .PrintObject = True
    End With
    
    Range("A2").Select

Reply | Quote

After reviewing the link you provided I was able to come up with a work around method that will serve my purpose. I first selected a row I wanted to insert photos in and then I set up width and height of selected cells to fit the size I needed for selected photos. Then by using a Check box (format control) using unchecked print and added a macro that will hide or unhide selected row using.

Code:

   Dim Rng As Range
    Dim MyCell As Range
    Set Rng = Range("A1")
    For Each MyCell In Rng
        If MyCell.Value = "" Then
            MyCell.EntireRow.Hidden = ActiveSheet.CheckBoxes(Application.Caller).Value = xlOn
        End If
    Next MyCell

I then inserted a command buttons, within selected cells in unhidden row, labeled “insert photo here” and set properties – PrintObject to False, then set Format Controls – properties to “move and size with cell”. This way if no photo is needed within this row I leave row hidden and command buttons. If I need a photo inserted within this row I unhide row and command buttons by using check box. Then I can select command button to insert photo. I’m using a code that adjusts photo size to selected cell. So far it seems to work OK. Here’s the code I use to insert and size photo.

Code:

    Dim PicLocation As String
    Dim MyRange As String
    
    ActiveSheet.Range("A1").Select
    MyRange = Selection.Address

    PicLocation = Application.GetOpenFilename(FileFilter:="Pic Files (*.jpg;*.bmp), *.jpg;*.bmp", Title:="Browse to select a picture")
    
    If PicLocation  "False" Then
        ActiveSheet.Pictures.Insert(PicLocation).Select
    Else
        Exit Sub
    End If
    
    With Selection.ShapeRange
        .LockAspectRatio = msoTrue
        If .Width > .Height Then
            .Width = Range(MyRange).Width
            If .Height > Range(MyRange).Height Then .Height = Range(MyRange).Height
        Else
            .Height = Range(MyRange).Height
            If .Width > Range(MyRange).Width Then .Width = Range(MyRange).Width
        End If
    End With
    
    With Selection
        .Placement = xlMoveAndSize
        .PrintObject = True
    End With
    
    Range("A2").Select

Reply | Quote

After reviewing the link you provided I was able to come up with a work around method that will serve my purpose. I first selected a row I wanted to insert photos in and then I set up width and height of selected cells to fit the size I needed for selected photos. Then by using a Check box (format control) using unchecked print and added a macro that will hide or unhide selected row using.

Code:

   Dim Rng As Range
    Dim MyCell As Range
    Set Rng = Range("A1")
    For Each MyCell In Rng
        If MyCell.Value = "" Then
            MyCell.EntireRow.Hidden = ActiveSheet.CheckBoxes(Application.Caller).Value = xlOn
        End If
    Next MyCell

I then inserted a command buttons, within selected cells in unhidden row, labeled “insert photo here” and set properties – PrintObject to False, then set Format Controls – properties to “move and size with cell”. This way if no photo is needed within this row I leave row hidden and command buttons. If I need a photo inserted within this row I unhide row and command buttons by using check box. Then I can select command button to insert photo. I’m using a code that adjusts photo size to selected cell. So far it seems to work OK. Here’s the code I use to insert and size photo.

Code:

    Dim PicLocation As String
    Dim MyRange As String
    
    ActiveSheet.Range("A1").Select
    MyRange = Selection.Address

    PicLocation = Application.GetOpenFilename(FileFilter:="Pic Files (*.jpg;*.bmp), *.jpg;*.bmp", Title:="Browse to select a picture")
    
    If PicLocation  "False" Then
        ActiveSheet.Pictures.Insert(PicLocation).Select
    Else
        Exit Sub
    End If
    
    With Selection.ShapeRange
        .LockAspectRatio = msoTrue
        If .Width > .Height Then
            .Width = Range(MyRange).Width
            If .Height > Range(MyRange).Height Then .Height = Range(MyRange).Height
        Else
            .Height = Range(MyRange).Height
            If .Width > Range(MyRange).Width Then .Width = Range(MyRange).Width
        End If
    End With
    
    With Selection
        .Placement = xlMoveAndSize
        .PrintObject = True
    End With
    
    Range("A2").Select

Reply | Quote

After reviewing the link you provided I was able to come up with a work around method that will serve my purpose. I first selected a row I wanted to insert photos in and then I set up width and height of selected cells to fit the size I needed for selected photos. Then by using a Check box (format control) using unchecked print and added a macro that will hide or unhide selected row using.

Code:

   Dim Rng As Range
    Dim MyCell As Range
    Set Rng = Range("A1")
    For Each MyCell In Rng
        If MyCell.Value = "" Then
            MyCell.EntireRow.Hidden = ActiveSheet.CheckBoxes(Application.Caller).Value = xlOn
        End If
    Next MyCell

I then inserted a command buttons, within selected cells in unhidden row, labeled “insert photo here” and set properties – PrintObject to False, then set Format Controls – properties to “move and size with cell”. This way if no photo is needed within this row I leave row hidden and command buttons. If I need a photo inserted within this row I unhide row and command buttons by using check box. Then I can select command button to insert photo. I’m using a code that adjusts photo size to selected cell. So far it seems to work OK. Here’s the code I use to insert and size photo.

Code:

    Dim PicLocation As String
    Dim MyRange As String
    
    ActiveSheet.Range("A1").Select
    MyRange = Selection.Address

    PicLocation = Application.GetOpenFilename(FileFilter:="Pic Files (*.jpg;*.bmp), *.jpg;*.bmp", Title:="Browse to select a picture")
    
    If PicLocation  "False" Then
        ActiveSheet.Pictures.Insert(PicLocation).Select
    Else
        Exit Sub
    End If
    
    With Selection.ShapeRange
        .LockAspectRatio = msoTrue
        If .Width > .Height Then
            .Width = Range(MyRange).Width
            If .Height > Range(MyRange).Height Then .Height = Range(MyRange).Height
        Else
            .Height = Range(MyRange).Height
            If .Width > Range(MyRange).Width Then .Width = Range(MyRange).Width
        End If
    End With
    
    With Selection
        .Placement = xlMoveAndSize
        .PrintObject = True
    End With
    
    Range("A2").Select

Reply | Quote

After reviewing the link you provided I was able to come up with a work around method that will serve my purpose. I first selected a row I wanted to insert photos in and then I set up width and height of selected cells to fit the size I needed for selected photos. Then by using a Check box (format control) using unchecked print and added a macro that will hide or unhide selected row using.

Code:

   Dim Rng As Range
    Dim MyCell As Range
    Set Rng = Range("A1")
    For Each MyCell In Rng
        If MyCell.Value = "" Then
            MyCell.EntireRow.Hidden = ActiveSheet.CheckBoxes(Application.Caller).Value = xlOn
        End If
    Next MyCell

I then inserted a command buttons, within selected cells in unhidden row, labeled “insert photo here” and set properties – PrintObject to False, then set Format Controls – properties to “move and size with cell”. This way if no photo is needed within this row I leave row hidden and command buttons. If I need a photo inserted within this row I unhide row and command buttons by using check box. Then I can select command button to insert photo. I’m using a code that adjusts photo size to selected cell. So far it seems to work OK. Here’s the code I use to insert and size photo.

Code:

    Dim PicLocation As String
    Dim MyRange As String
    
    ActiveSheet.Range("A1").Select
    MyRange = Selection.Address

    PicLocation = Application.GetOpenFilename(FileFilter:="Pic Files (*.jpg;*.bmp), *.jpg;*.bmp", Title:="Browse to select a picture")
    
    If PicLocation  "False" Then
        ActiveSheet.Pictures.Insert(PicLocation).Select
    Else
        Exit Sub
    End If
    
    With Selection.ShapeRange
        .LockAspectRatio = msoTrue
        If .Width > .Height Then
            .Width = Range(MyRange).Width
            If .Height > Range(MyRange).Height Then .Height = Range(MyRange).Height
        Else
            .Height = Range(MyRange).Height
            If .Width > Range(MyRange).Width Then .Width = Range(MyRange).Width
        End If
    End With
    
    With Selection
        .Placement = xlMoveAndSize
        .PrintObject = True
    End With
    
    Range("A2").Select

Reply | Quote

After reviewing the link you provided I was able to come up with a work around method that will serve my purpose. I first selected a row I wanted to insert photos in and then I set up width and height of selected cells to fit the size I needed for selected photos. Then by using a Check box (format control) using unchecked print and added a macro that will hide or unhide selected row using.

Code:

   Dim Rng As Range
    Dim MyCell As Range
    Set Rng = Range("A1")
    For Each MyCell In Rng
        If MyCell.Value = "" Then
            MyCell.EntireRow.Hidden = ActiveSheet.CheckBoxes(Application.Caller).Value = xlOn
        End If
    Next MyCell

I then inserted a command buttons, within selected cells in unhidden row, labeled “insert photo here” and set properties – PrintObject to False, then set Format Controls – properties to “move and size with cell”. This way if no photo is needed within this row I leave row hidden and command buttons. If I need a photo inserted within this row I unhide row and command buttons by using check box. Then I can select command button to insert photo. I’m using a code that adjusts photo size to selected cell. So far it seems to work OK. Here’s the code I use to insert and size photo.

Code:

    Dim PicLocation As String
    Dim MyRange As String
    
    ActiveSheet.Range("A1").Select
    MyRange = Selection.Address

    PicLocation = Application.GetOpenFilename(FileFilter:="Pic Files (*.jpg;*.bmp), *.jpg;*.bmp", Title:="Browse to select a picture")
    
    If PicLocation  "False" Then
        ActiveSheet.Pictures.Insert(PicLocation).Select
    Else
        Exit Sub
    End If
    
    With Selection.ShapeRange
        .LockAspectRatio = msoTrue
        If .Width > .Height Then
            .Width = Range(MyRange).Width
            If .Height > Range(MyRange).Height Then .Height = Range(MyRange).Height
        Else
            .Height = Range(MyRange).Height
            If .Width > Range(MyRange).Width Then .Width = Range(MyRange).Width
        End If
    End With
    
    With Selection
        .Placement = xlMoveAndSize
        .PrintObject = True
    End With
    
    Range("A2").Select

Reply | Quote