Get inserted image to adjust the row height in Excel

0 votes

I am having trouble getting Excel to automatically change the row height to the inserted image. I've used a cell. WholeRow = image Height, but it doesn't change the row's width to correspond to the image height. To find the code, it iterates through numerous worksheets. After finding the code, it chooses the subsequent empty cell and inserts the picture there. Additionally, since there is typically more than one Photo1 on a worksheet, I'm not sure if this is the proper order in which to complete it. If I can work this out, I can apply whatever solution is discovered to photos 2 and 3 as well.

Here is my code

Private Sub cmdInsertPhoto1_Click()
'insert the photo1 from the folder into each worksheet
Dim ws As Worksheet
Dim fso As FileSystemObject
Dim folder As folder
Dim rng As Range, cell As Range
Dim strFile As String
Dim imgFile As String
Dim localFilename As String
Dim pic As Picture
Dim findit As String

Application.ScreenUpdating = True

'delete the two sheets if they still exist
For Each ws In ActiveWorkbook.Worksheets
If ws.Name = "PDFPrint" Then
    Application.DisplayAlerts = False
    Sheets("PDFPrint").Delete
    Application.DisplayAlerts = True
End If
Next

For Each ws In ActiveWorkbook.Worksheets
If ws.Name = "DataSheet" Then
    Application.DisplayAlerts = False
    Sheets("DataSheet").Delete
    Application.DisplayAlerts = True
End If
Next
    

Set fso = New FileSystemObject
Set folder = fso.GetFolder(ActiveWorkbook.Path & "\Photos1\")
  
'Loop through all worksheets
For Each ws In ThisWorkbook.Worksheets
ws.Select


     Set rng = Range("A:A")
    ws.Unprotect
     For Each cell In rng
      If cell = "CG Code" Then
      'find the next adjacent cell value of CG Code
       strFile = cell.Offset(0, 1).Value 'the cg code value
       imgFile = strFile & ".png" 'the png imgFile name
       localFilename = folder & "\" & imgFile 'the full location
               
       'just find Photo1 cell and select the adjacent cell to insert the image
       findit = Range("A:A").Find(what:="Photo1", MatchCase:=True).Offset(0, 1).Select
       
       Set pic = ws.Pictures.Insert(localFilename)
         With pic
            .ShapeRange.LockAspectRatio = msoFalse
            .ShapeRange.Width = 200
            .ShapeRange.Height = 200 'max row height is 409.5
            .Placement = xlMoveAndSize
         End With
        cell.EntireRow = pic.Height
      End If
        
        'delete photo after insert
        'Kill localFilename
        
     Next cell

Next ws



Application.ScreenUpdating = True

 ' let user know its been completed
 MsgBox ("Worksheets created")
 End Sub

What it currently looks like enter image description here

Jan 24 in Others by Kithuzzz
• 33,140 points
58 views

1 answer to this question.

0 votes

Try this:

'just find Photo1 cell and select the adjacent cell to insert the image
       findit = Range("A:A").Find(what:="Photo1", MatchCase:=True).Offset(0, 1).Select
       ActiveCell.EntireRow.RowHeight = 200 'max row height is 409.5
            
       Set pic = ws.Pictures.Insert(localFilename)
         With pic
            .ShapeRange.LockAspectRatio = msoFalse
            .ShapeRange.Width = 200
            '.ShapeRange.Height = 200 'max row height is 409.5
            .ShapeRange.Height = ActiveCell.MergeArea.Height
            .ShapeRange.Top = ActiveCell.MergeArea.Top
            .ShapeRange.Left = ActiveCell.MergeArea.Left
            .Placement = xlMoveAndSize
         End With
answered Jan 24 by narikkadan
• 58,640 points

Related Questions In Others

0 votes
2 answers

How to get the URL of the current tab in Google Chrome?

Its so simple.... If you want to ...READ MORE

answered Aug 12, 2020 in Others by Steve
• 200 points
1,739 views
0 votes
1 answer

Language independent way to get "My Documents" folder in VBA Excel 2003

 Hello :)  This code may help you in your ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,630 points
186 views
0 votes
1 answer

How to get the JasperReports into Excel format with proper alignment

Remove at least one parameter: exporter.setParameter(JRXlsExporterParameter.IS_COLLAPSE_ROW_SPAN,Boolean.TRUE) Note that JRXlsExportParameter is deprecated the correct ...READ MORE

answered Sep 21, 2022 in Others by narikkadan
• 58,640 points
1,037 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
570 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
2,523 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 22,970 points
142 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,630 points
384 views
0 votes
1 answer

How to insert a new row in the specified cell only, of Excel sheet using c#?

I have this worksheet with a matrix ...READ MORE

answered Nov 24, 2022 in Others by narikkadan
• 58,640 points
553 views
0 votes
1 answer

How to get the excel file name / path in VBA

Use FullName, for example: strFileFullName = ThisWorkbook.FullName ...READ MORE

answered Dec 23, 2022 in Others by narikkadan
• 58,640 points
80 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP