江阴网站建设,江阴网站制作,江阴网站设计,江阴SEO优化,江阴小程序开发-江阴雨辰互联

  1. JustNews 首页
  2. questions

excel - VBA Subscript out of range when closing a UserForm? - Stack Overflow

admin••questions•阅读3

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

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

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
  • excel
  • vba
  • userform
Share Improve this question edited Mar 4 at 14:54 BigBen 50.2k7 gold badges28 silver badges44 bronze badges asked Mar 4 at 14:53 Artybunny Artybunny 32 bronze badges 12
  • I'm assuming the adjusted list .values are list boxes on your form? I don't think they have that property, I think it sets ListIndex, and you need to then look it up in the list for the selected value – NickSlash Commented Mar 4 at 15:01
  • 1 Pro Tip: Use intelligent naming for your controls. I use a semi-Hungarian naming convention that uses a prefix which identifies the controls type (cmb for ComboBox, lb for List Box, cb for Check Box, etc.) and then a descriptive name: cmbColors for Combo Box of Colors, lbWidths for a LIst Box of Widths, cbPrint for a Checkbox to indicate if something gets printed. This makes both writing and reading your code much simpler and if done well makes it self-commenting, since your control names indicate what information is being managed. – Frank Ball Commented Mar 4 at 15:11
  • 2 Side note: consider Me.Hide instead of Unload Me. – BigBen Commented Mar 4 at 15:52
  • 2 If you're seeing an error message without a "Debug" button, go into the VB editor and select Tools >> Options >> General >> Error Trapping >> "Break in Class Module" – Tim Williams Commented Mar 4 at 17:07
  • 1 ...on review it looks like that error is coming from an error handler, maybe within your form. You can *temporarily" set your error handling to "Break on all errors" to identify what is triggering that message. – Tim Williams Commented Mar 4 at 17:11
 |  Show 7 more comments

1 Answer 1

Reset to default 0

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

发布者：admin，转转请注明出处：http://www.yc00.com/questions/1745037304a4607598.html

adminadmin
0

相关推荐

发表回复

评论列表（0条）

  • 暂无评论

联系我们

400-800-8888

在线咨询： QQ交谈

邮件：admin@example.com

工作时间：周一至周五，9:30-18:30，节假日休息

关注微信