Page 2 of 6 FirstFirst 1234 ... LastLast
Results 11 to 20 of 57

Thread: VBA to insert a custom image

  1. #11
    Board Regular
    Join Date
    Aug 2018
    Location
    NSW, Australia
    Posts
    945
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to insert a custom image

    That code is pretty much what I had, YAY!!, I worked out some code myself.

    If the notes textbox is near the bottom of the page though, it will insert the image partly on one page and partly on the next. If this happens, I need it pushed to the second page will it will all appear on the one page.

  2. #12
    Board Regular
    Join Date
    Aug 2018
    Location
    NSW, Australia
    Posts
    945
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to insert a custom image

    Could you help me with this new issue in the last post please Michael?

  3. #13
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,814
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

    Default Re: VBA to insert a custom image

    ONLY LIGHTLY TESTED
    you will need to play with lr in relation to where the picture goes.

    Code:
    Sub MM1()
    Dim fNameAndPath As Variant
    Dim img As Picture, shp As Shape
    Set shp = Worksheets("sheet1").Shapes("textbox 1")
    fNameAndPath = Application.GetOpenFilename(Title:="Select Picture To Be Imported")
    If fNameAndPath = False Then Exit Sub
     Dim h As Long, lr As Long
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    Set img = Worksheets("sheet1").Pictures.Insert(fNameAndPath)
      For h = 1 To 100
        If Sheets("Sheet1").Rows(h).PageBreak <> xlPageBreakNone Then
        If lr >= h - 5 Then 'this is 5 rows before the page break
        With img
           .Left = 0
           .Top = lr + 20 + shp.Top + shp.Height + 50
        End With
        Else
        With img
           .Left = 0
           .Top = shp.Top + shp.Height + 50
        End With
        End If
        End If
      Next h
        
    End Sub
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  4. #14
    Board Regular
    Join Date
    Aug 2018
    Location
    NSW, Australia
    Posts
    945
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to insert a custom image

    Thanks Michael. I am still having issues with it so I am going to leave it and come back to it later.

  5. #15
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,814
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

    Default Re: VBA to insert a custom image

    whats the issue ??
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  6. #16
    Board Regular
    Join Date
    Aug 2018
    Location
    NSW, Australia
    Posts
    945
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to insert a custom image

    Quote Originally Posted by Michael M View Post
    whats the issue ??
    I have not changed any code yet (not sure what to change), so with the current code:


    • It takes about 11 or 12 seconds to insert the picture so I would like to speed that up if I could.
    • The image is inserted 4 rows below the bottom of the table, which is right in the middle of the notes box.


    I changed the following line of code to have a B instead of an A and that fixed the horizontal alignment.
    Code:
    lr = Cells(Rows.Count, "B").End(xlUp).Row
    I updated this line of code to have the correct references to the shape name

    Code:
    Set shp = Worksheets("sheet1").Shapes("textbox 1")

    I am just not sure:
    • how to get it appearing below the textbox.
    • how to speed it up so it isn't so slow.


    Thanks

  7. #17
    Board Regular
    Join Date
    Aug 2018
    Location
    NSW, Australia
    Posts
    945
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to insert a custom image

    I just worked part of it out Michael,

    I didn't realise that it is case sensitive so I changed it to the correct case and now it is fast enough and it is appearing below the text box.

    Only issue left is that it is still splitting the image between 2 pages if it is close to the bottom of the page when the image is inserted.

  8. #18
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,814
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

    Default Re: VBA to insert a custom image

    You need to play with this line, as we don't know the picture size
    Try changing the numbers in red, maybe a large number to start with, say 100

    Code:
    .Top = lr + 20 + shp.Top + shp.Height + 50
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  9. #19
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,814
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

    Default Re: VBA to insert a custom image

    or a bit shorter
    Code:
    .Top = lr + 150 + shp.Top + shp.Height
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  10. #20
    Board Regular
    Join Date
    Aug 2018
    Location
    NSW, Australia
    Posts
    945
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to insert a custom image

    I thought of an idea Michael.

    What if you had code that measured the distance between 2 lines below the bottom of the box and the end of the page and compared it to the height of the image. If there is not enough room to insert the image between the point below the box and the end of the page, push the image to the next page by inserting a page break before the image?

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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