A custom image has to be forced to the next page if it is placed on the pagebreak

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,343
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I have a spreadsheet that has a button to add a signature. If the signature is partly one one page and partly on the next page, it will be pushed over to the following page so the signature is all on one page. This is for several defined signatures that are stored in the spreadsheet and this function works as desired.

I have another button for adding a custom signature for anyone whose signature is not stored in the sheet. When I received assistance to update the button/s to add the defined signatures to the spreadsheet and to push them over to the next page if they are across it, I forgot to update the custom signature button.

This is the code from one of the signature buttons that works.
VBA Code:
Sub cmdTraceySig()
    Quoting.Unprotect Password:=ToUnlock
    Dim user As String
        user = "ImgT"
        Call cmdNoSig
        Call cmdPush(user)
    Quoting.Protect Password:=ToUnlock
End Sub

The signature is stored on a hidden sheet and is called ImgT.

This procedure calls several others
VBA Code:
Sub cmdNoSig()
    Dim Pic As Object
    
    For Each Pic In Quoting.Pictures
        If Pic.Name <> "lblActivities" And Pic.Name <> "TextBox3" And Pic.Name <> "lblNotes" And Pic.Name <> "cmdAdd_Nlines" And Pic.Name <> "cmdDeleteRow" And Pic.Name <> "cmdClearNotDates" And _
        Pic.Name <> "cmdDelSelect" And Pic.Name <> "cmdGarrettB" And Pic.Name <> "cmdNoSignature" And Pic.Name <> "cmdSendTCT" And Pic.Name <> "cmdSort" And _
        Pic.Name <> "cmdDeleteQuoteLines" And Pic.Name <> "ImgLogo" And Pic.Name <> "cmdCustom" And Pic.Name <> "chkIncrease" And Pic.Name <> "lblIncrease" And _
        Pic.Name <> "cmdTraceyS" And Pic.Name <> "cmdJonathanA" And Pic.Name <> "cmdAnotherName" And Pic.Name <> "cmdPrintPdf" And Pic.Name <> "cmdQuoteTips" And _
        Pic.Name <> "Label1" And Pic.Name <> "cmdSendTCTPrint" And Pic.Name <> "textbox4" And Pic.Name <> "lblNotes" And Pic.Name <> "CmdSpacer" And Pic.Name <> "cmdUnlock" Then
             ' If Not Intersect(Pic.TopLeftCell, Range("A12:A300")) Is Nothing Then
                  Pic.Delete
              'End If
        End If
    Next Pic
End Sub

Sub cmdPush(user As String)
Dim LastRow As Long, ImageHeight As Double, LastRowBeforeLastPageBreak As Double, DividerBottomPlusSpace As Long
Dim NoPages As Long, ws As Worksheet, n As Long
Set ws = Sheets("CSS_quote_sheet")
'Finds the number of pages
n = ws.HPageBreaks.Count
NoPages = ((ActiveSheet.HPageBreaks.Count + 1) * (ActiveSheet.VPageBreaks.Count + 1)) / 2
Application.ScreenUpdating = False
    With Sheets("Sheet2")
        .Shapes(user).Duplicate.Name = "Signature"
        .Shapes("Signature").Cut
    End With
        ws.Cells(43, 1).PasteSpecial
        ws.Shapes(Selection.Name).Name = "Signature"
        Selection.Top = ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Offset(1).Top + 140
        LastRow = ws.Columns("A:H").Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
        ImageHeight = ws.Shapes("Signature").Height
        'LastRowBeforeLastPageBreak = ws.HPageBreaks(n).Location.Row - 1
        DividerBottomPlusSpace = ws.Shapes("Divider").BottomRightCell.Top + 140
    With ws.Shapes("Signature")
        .Left = ActiveSheet.Range("A1").Left
        .Top = Rows(LastRow).Top + 140
        .Placement = 1
        'End If
    End With
Call PushOver
End Sub

Sub PushOver()
Dim hPB As HPageBreak, lRow As Long
    For Each hPB In ActiveSheet.HPageBreaks
    lRow = hPB.Location.Row
        With ActiveSheet.Shapes("Signature")
            If lRow - .TopLeftCell.Row <= 5 Then .Top = Range("A" & lRow + 2).Top
    End With
        Next hPB
Application.ScreenUpdating = True
End Sub

So these all work together to push the signature to the following page if it is split over the page break.

Here is the code for the custom signature button and the procedure it calls
VBA Code:
Private Sub cmdCustom_Click()
    Quoting.Unprotect Password:=ToUnlock
        Call cmdCustomSig
    Quoting.Protect Password:=ToUnlock
End Sub

Sub cmdCustomSig()
Dim fNameAndPath As Variant
Dim img As Picture, shp As Shape
    fNameAndPath = Application.GetOpenFilename(Title:="Select Picture To Be Imported")
    If fNameAndPath = False Then Exit Sub
        Set img = Worksheets("CSS_quote_sheet").Pictures.Insert(fNameAndPath)
        With img
           'If img.Height > Rows(Sheets("CSS_quote_sheet").HPageBreaks(1).Location.Row).Top - Sheets("CSS_quote_sheet").Shapes("TextBox4").Top + Sheets("CSS_quote_sheet").Shapes("TextBox4").Height Then
             '   .Top = Rows(Sheets("CSS_quote_sheet").HPageBreaks(1).Location.Row).Top
             '   Else
              '  .Top = Sheets("CSS_quote_sheet").Shapes("TextBox4").Top + Sheets("CSS_quote_sheet").Shapes("TextBox4").Height
            'End If
           .Top = Cells(LastRow, 1).Top + "100"
           .Left = "0"
        End With
End Sub

How do I incorporate the working code that is used to place the defined image stored in sheet2 into the custom signature code so that it is pushed over to the next page if it is across the page break too?

Thanks.
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,194
Messages
5,768,776
Members
425,492
Latest member
blueexcel123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top