excelvbauserform

VBA Subscript out of range when closing a UserForm?


I'm relatively new to VBA and still learning - but finding lots of helpful information on here so thank you!

I'm having a bit of a problem with a userform I am creating... It all runs okay and the inputs and listboxes etc all work and it loads and saves the changes to a worksheet. The only issue is when the Userform closes it pops up with a message saying 'Subscript out of range'. However, because the code has all run it gives me no option to debug and see which line might be causing it (image below), any suggestions would be much appreciated please?

Message Box image

Private Sub Save_Click()

'Save Current Lists

Sheets("Lists").Select
Range("A1:B20").Select
Selection.Copy
Range("A30").Select
ActiveSheet.Paste
Range("H1:I26").Select
Application.CutCopyMode = False
Selection.Copy
Range("H30").Select
ActiveSheet.Paste
Sheets("Add Data").Select
Range("A1").Select

' Save adjusted shift lists to sheet

Sheets("Lists").Range("A3:B30").ClearContents

Dim cntEntriesA As Long
Dim cntEntriesB As Long
cntEntriesA = AShift1.ListCount
cntEntriesB = BShift1.ListCount

Sheets("Lists").Range("A3").Resize(cntEntriesA).Value = AShift1.List
Sheets("Lists").Range("B3").Resize(cntEntriesB).Value = BShift1.List

' Save adjusted distribution list to sheet

Sheets("Lists").Range("H2").Value = LN1.Value
Sheets("Lists").Range("I2").Value = E1.Value
Sheets("Lists").Range("H3").Value = LN2.Value
Sheets("Lists").Range("I3").Value = E2.Value
Sheets("Lists").Range("H4").Value = LN3.Value
Sheets("Lists").Range("I4").Value = E3.Value
Sheets("Lists").Range("H5").Value = LN4.Value
Sheets("Lists").Range("I5").Value = E4.Value
Sheets("Lists").Range("H6").Value = LN5.Value
Sheets("Lists").Range("I6").Value = E5.Value
Sheets("Lists").Range("H7").Value = LN6.Value
Sheets("Lists").Range("I7").Value = E6.Value
Sheets("Lists").Range("H8").Value = LN7.Value
Sheets("Lists").Range("I8").Value = E7.Value
Sheets("Lists").Range("H9").Value = LN8.Value
Sheets("Lists").Range("I9").Value = E8.Value
Sheets("Lists").Range("H10").Value = LN9.Value
Sheets("Lists").Range("I10").Value = E9.Value
Sheets("Lists").Range("H11").Value = LN10.Value
Sheets("Lists").Range("I11").Value = E10.Value
Sheets("Lists").Range("H12").Value = LN11.Value
Sheets("Lists").Range("I12").Value = E11.Value
Sheets("Lists").Range("H13").Value = LN12.Value
Sheets("Lists").Range("I13").Value = E12.Value
Sheets("Lists").Range("H14").Value = LN13.Value
Sheets("Lists").Range("I14").Value = E13.Value
Sheets("Lists").Range("H15").Value = LN14.Value
Sheets("Lists").Range("I15").Value = E14.Value
Sheets("Lists").Range("H16").Value = LN15.Value
Sheets("Lists").Range("I16").Value = E15.Value
Sheets("Lists").Range("H17").Value = LN16.Value
Sheets("Lists").Range("I17").Value = E16.Value
Sheets("Lists").Range("H18").Value = LN17.Value
Sheets("Lists").Range("I18").Value = E17.Value
Sheets("Lists").Range("H19").Value = LN18.Value
Sheets("Lists").Range("I19").Value = E18.Value
Sheets("Lists").Range("H20").Value = LN19.Value
Sheets("Lists").Range("I20").Value = E19.Value
Sheets("Lists").Range("H21").Value = LN20.Value
Sheets("Lists").Range("I21").Value = E20.Value
Sheets("Lists").Range("H22").Value = LN21.Value
Sheets("Lists").Range("I22").Value = E21.Value
Sheets("Lists").Range("H23").Value = LN22.Value
Sheets("Lists").Range("I23").Value = E22.Value
Sheets("Lists").Range("H24").Value = LN23.Value
Sheets("Lists").Range("I24").Value = E23.Value
Sheets("Lists").Range("H25").Value = LN24.Value
Sheets("Lists").Range("I25").Value = E24.Value
Sheets("Lists").Range("H26").Value = LN25.Value
Sheets("Lists").Range("I26").Value = E25.Value

Unload Me

End Sub

Solution

  • Not an answer to your question but too long for a comment: just to show how your code could be shortened - see comments below for details

    Private Sub Save_Click()
        Dim i As Long
        
        With ThisWorkbook.Worksheets("Lists")
            'Save Current Lists
            .Range("A1:B20").Copy .Range("A30") 'can do this in one step...
            .Range("H1:I26").Copy .Range("H30")
            .Range("A3:B30").ClearContents
            'Avoid "single use" variables like `cntEntriesA` if they don't add much clarity
            '   and just add bulk to your code
            .Range("A3").Resize(AShift1.ListCount).Value = AShift1.List
            .Range("B3").Resize(BShift1.ListCount).Value = BShift1.List
        
            For i = 1 To 25 'loop and copy control values to worksheet
                'Use the `Controls` collection to access controls by name
                'In a userform code module, `Me` is the running instance of the form
                .Cells(i + 1, "H").Value = Me.Controls("LN" & i).Value
                .Cells(i + 1, "I").Value = Me.Controls("E" & i).Value
            Next i
        End With
        
        Unload Me
    End Sub