Subscript out of range

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,352
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
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
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
VBA Code:
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.
VBA Code:
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?
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
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.
 
Upvote 0
What happens if you move this line:

VBA Code:
n = ws.HPageBreaks.Count

to just before the offending code line?
 
Upvote 0
I still get subscript out of range with the same line being highlighted.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
I think you're better off starting a new thread with a better description in the header.
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,317
Members
448,564
Latest member
ED38

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
Back
Top