Results 1 to 5 of 5

Thread: macro enabled Excel spreadsheet with insert images autofit help

  1. #1

    macro enabled Excel spreadsheet with insert images autofit help



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi all,

    This is my first post here, hope someone can help. I have a work related task in which I need to find a way to insert images (from a specified folder - which has another macro for it) by clicking on a command button - the images insert in the H column. However, the problem is that the images from the source are not all of the same size hence they dont actually fit the H column cells ... is there any way this can be automatically done using another macro I suppose,. any help in this matter is appreciated with example codes or fixes - please see attached spreadsheet file template of the same.

    Any questions please let me know, I am looking forward to someone helping me out here. Many thanks for looking into this, and please I am still a newbie and go easy with me

    cheers,
    Sid
    Attached Files Attached Files

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,195
    Articles
    57
    Blog Entries
    14
    Welcome to the forum,

    You've got some pretty inefficient code in there, but maybe this will help. This should scale your picture to fit in the target cell exactly:

    Code:
    Sub InsertPicture(PictureFileName As String, TargetCell As Range, CenterH As Boolean, CenterV As Boolean)
    ' inserts a picture at the top left position of TargetCell
    ' the picture can be centered horizontally and/or vertically
    Dim p As Object, t As Double, l As Double, w As Double, h As Double
        If TypeName(ActiveSheet) <> "Worksheet" Then Exit Sub
        If Dir(PictureFileName) = "" Then Exit Sub
        ' import picture
        Set p = ActiveSheet.Pictures.Insert(PictureFileName)
        ' position picture
        With p
            .Top = TargetCell.Top
            .Left = TargetCell.Left
            .Width = TargetCell.Width
            .Height = TargetCell.Height
        End With
        Set p = Nothing
    End Sub
    Hope it helps,
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Hi thank you so much for the reply,

    Could you advise where should I put that code? should I amend the existing code in the module?


    Regards
    Sid

  4. #4
    Quote Originally Posted by Ken Puls View Post
    Welcome to the forum,

    You've got some pretty inefficient code in there, but maybe this will help. This should scale your picture to fit in the target cell exactly:

    Code:
    Sub InsertPicture(PictureFileName As String, TargetCell As Range, CenterH As Boolean, CenterV As Boolean)
    ' inserts a picture at the top left position of TargetCell
    ' the picture can be centered horizontally and/or vertically
    Dim p As Object, t As Double, l As Double, w As Double, h As Double
        If TypeName(ActiveSheet) <> "Worksheet" Then Exit Sub
        If Dir(PictureFileName) = "" Then Exit Sub
        ' import picture
        Set p = ActiveSheet.Pictures.Insert(PictureFileName)
        ' position picture
        With p
            .Top = TargetCell.Top
            .Left = TargetCell.Left
            .Width = TargetCell.Width
            .Height = TargetCell.Height
        End With
        Set p = Nothing
    End Sub
    Hope it helps,

    Well I got it thanks mate It works great now.. also is it possible to have the H column width and length automatically resize at the same time to say Row Height = 105 and column width 16.5 ???

    thank you

  5. #5
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,195
    Articles
    57
    Blog Entries
    14
    Sure,

    Right before the line that reads "with p", you could insert the following:

    Code:
    With TargetCell
         .Entirecolumn.Width=16.5
         .Entirerow.Height=105
    End With
    Again, this is far from efficient, but it will work.

    The main thrust of the efficiency point is aimed at the fact that you have 100 routines in there that essentially do the same thing. Those could be consolidated down to one routine to make things much easier to maintain.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •