Subscript out of range

[dpaton05]

I have a spreadsheet that has a table that can have x number of rows. At the bottom of the spreadsheet, I have multiple buttons to place one of 4 people's signatures.

This is the code behind the 4 buttons

Sub cmdTraceySig()
    Quoting.Unprotect Password:=ToUnlock
    Dim user As String
        user = "ImgT"
        Call cmdNoSig
        Call cmdPush(user)
    Quoting.Protect Password:=ToUnlock
End Sub
Sub cmdLynSig()
    Quoting.Unprotect Password:=ToUnlock
    Dim user As String
        user = "ImgL"
        Call cmdNoSig
        Call cmdPush(user)
    'Quoting.Protect Password:=ToUnlock
End Sub
Sub cmdGarrettSig()
    Quoting.Unprotect Password:=ToUnlock
    Dim user As String
        user = "ImgG"
        Call cmdNoSig
        Call cmdPush(user)
    'Quoting.Protect Password:=ToUnlock
End Sub
Sub cmdJonathanSig()
    Quoting.Unprotect Password:=ToUnlock
    Dim user As String
        user = "ImgJ"
        Call cmdNoSig
        Call cmdPush(user)
    'Quoting.Protect Password:=ToUnlock
End Sub

Here is cmdPush and cmdNoSig

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
Application.ScreenUpdating = True
End Sub
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 <> "cmdLynL" And Pic.Name <> "cmdJonathanA" 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

The problem is that when I try and press one of the buttons, I get a subscript out of range error with the following line in cmdPush highlighted.

LastRowBeforeLastPageBreak = ws.HPageBreaks(n).Location.Row - 1

It's strange as even after the VBE has appeared showing the error, I can see the signature has been inserted in the background.

The signature is also meant to be inserted below any notes that are written but the signature is put a set distance below the table, regardless of any notes.

I have looked at previous versions I have that work but the vba is identical.
Can someone help me please?

 

[dpaton05]

I just found that if I comment out that line, my sub works. I don't use it further on in my code but I think that I put it in originally when I was trying to work out how to make the signature image be pushed to another page if the spot where it is inserted causes it to be split across 2 pages.

 

[StephenCrump]

What happens if you move this line:

n = ws.HPageBreaks.Count

to just before the offending code line?

 

[dpaton05]

I still get subscript out of range with the same line being highlighted.

 

[dpaton05]

If I step through the code, n =0 if there is only 1 page. I guess this is as there is no horizontal page break yet.

That is why the error subscript out of range appears.

 

[StephenCrump]

If I step through the code, n =0 if there is only 1 page. I guess this is as there is no horizontal page break yet.

That is why the error subscript out of range appears.

Ahh, that makes sense. Now that I look properly at your code, I see you're not doing anything with LastRowBeforeLastPageBreak any way.

Hence your remark in Post #2 - commenting out this line has no impact on the code.

 

[dpaton05]

Do you have any ideas how I might be able to get the signature image pushed to the following page if it is split across 2 pages?

 

[StephenCrump]

I think you're better off starting a new thread with a better description in the header.

 

[dpaton05]

Thanks for the heads up, I have done that.