Results 1 to 5 of 5

Thread: Make an image snap to a page
Thanks Thanks: 0 Likes Likes: 0

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

    Default Make an image snap to a page

    I have a button that inserts a signature at the bottom of a sheet under the last row but if the rows are nearing the bottom of the page, the signature image will be pasted half on one page and half on the next page. Here is the code to paste a set signature and code to paste a custom signature. If the signature is going to be split between pages, what is the code to make it snap to a page so it will just appear on the additional page and not be split between the two?

    Code:
      Sub cmdGarrettSig()
          Dim shp As Shape
          Set shp = Sheets("NPSS_quote_sheet").Shapes("textbox4")
              Sheets("Sheet2").Shapes("ImgG").Copy
              Sheets("NPSS_quote_sheet").Paste Destination:=Sheets("NPSS_quote_sheet").Cells(1, 1)
              Selection.Top = shp.Top + shp.Height + "50"
          'ActiveSheet.Protect Password:="npssadmin"
      End Sub
      
    Code:
      Private Sub cmdCustom_Click()
      'ActiveSheet.Unprotect Password:="npssadmin"
      Dim lr As Long
      lr = Sheets("NPSS_quote_sheet").Range("A" & Rows.Count).End(xlUp).Row + 4
       With Application.FileDialog(msoFileDialogFilePicker)
              .AllowMultiSelect = False
              .ButtonName = "Submit"
              .Title = "Select an image file"
              .Filters.Clear
              .Filters.Add "JPG", "*.JPG"
              
              If .Show = -1 Then
                  Dim img As Object
                  Set img = ActiveSheet.Pictures.Insert(.SelectedItems(1))
                  'Position Image
                  img.Left = 0
                  img.Top = ActiveSheet.Range("A" & lr + 6).Top
                  img.Width = 150
                  img.Height = 150
              Else
                  MsgBox ("Cancelled.")
              End If
          End With
      'ActiveSheet.Protect
    
      End Sub
    
      
    Thanks,
    Dave

  2. #2
    Board Regular Kenneth Hobson's Avatar
    Join Date
    Feb 2007
    Location
    Tecumseh, OK
    Posts
    3,051
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Make an image snap to a page

    Is lr always on page 1 or could it be on some other page number on that sheet?

    Be sure to obfuscate any sensitive data in a post. e.g. passwords, emails, ssn, phone, etc.
    Last edited by Kenneth Hobson; Sep 19th, 2019 at 11:03 PM.

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

    Default Re: Make an image snap to a page

    lr refers to the position 4 rows below the last row. It could be on any sheet, depending on how many row there are.

  4. #4
    Board Regular Kenneth Hobson's Avatar
    Join Date
    Feb 2007
    Location
    Tecumseh, OK
    Posts
    3,051
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Make an image snap to a page

    By any sheet, I don't know if you meant sheet or page. I will guess that you meant page on a sheet. In fact, as coded, it would have to be the last page on a sheet if column A is the must fill column.

    I deleted the file if it caused another page to be added. You can add code to go from there as I did not know what you wanted for that.

    Code:
    Private Sub cmdCustom_Click()
        Dim lr As Long, img As Object, pr As Long
        
        'Normally done at ThisWorbook's Open Event
        ActiveSheet.Protect "", UserInterfaceOnly:=True
        
        pr = LastPageRow(ActiveSheet)
        lr = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row + 4
        
        With Application.FileDialog(msoFileDialogFilePicker)
            .InitialFileName = Environ("userprofile") & "\pictures"
            .AllowMultiSelect = False
            .ButtonName = "Submit"
            .Title = "Select an image file"
            .Filters.Clear
            .Filters.Add "JPG", "*.JPG"
            If .Show = -1 Then
                Set img = ActiveSheet.Pictures.Insert(.SelectedItems(1))
                'Position Image
                img.Left = 0
                img.Top = ActiveSheet.Range("A" & lr + 6).Top
                img.Width = 150
                img.Height = 150
                Else
                    MsgBox "Cancelled.", vbInformation
                    Exit Sub
            End If
        End With
        img.TopLeftCell.Select
        If pr < LastPageRow(ActiveSheet) Then img.Delete
    End Sub
    
    Function LastPageRow(ws As Worksheet) As Long
        Dim lr As Long
        lr = 0
        On Error Resume Next
        'Location.Row is the next row after a page break.
        lr = ws.HPageBreaks.Item(ws.HPageBreaks.Count).Location.Row - 1
        LastPageRow = lr
    End Function
    Last edited by Kenneth Hobson; Sep 20th, 2019 at 12:09 AM.

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

    Default Re: Make an image snap to a page

    Thanks for your reply Ken, I tried the code and it didn't seem to work. I will start a new thread to fix another problem I have found before I get to this issue.

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
  •