Subscript out of range error

Joshdaniel

New Member
Joined
Apr 30, 2014
Messages
11
I am attempting to have a user form where the user clicks on a button to Add content into a worksheet. The code is as follows.

'Summary: Button for User to Add DAA. User clicks button and userform1 appears.
'User adds DAA, Career Path, Moves applicable Skills and Strengths to the right listbox
'and then hits submit. Submit command then adds the values into the CareerPathing Worksheet.

Rich (BB code):
Dim i As Integer

Private Sub btnSubmit_Click()
Dim ssheet As Worksheet

Set ssheet = ThisWorkbook.Sheets("CareerPathing")  ***SubScript Out of Range Error Here***

nr = ssheet.Cells(Rows.Count, 1).End(xlUp).Row + 1

ssheet.Cells(nr, 1) = Me.tbDAA
ssheet.Cells(nr, 2) = Me.tbCareerPath
ssheet.Cells(nr, 3) = Me.lbSkills_Strengths2


End Sub

Private Sub CheckBox1_Click()

If CheckBox1.Value = True Then
    For i = 0 To lbSkills_Strengths.ListCount - 1
        lbSkills_Strengths.Selected(i) = True
    Next i
End If

If CheckBox1.Value = False Then
    For i = 0 To lbSkills_Strengths.ListCount - 1
        lbSkills_Strengths.Selected(i) = False
    Next i
End If

End Sub

Private Sub CheckBox2_Click()

If CheckBox2.Value = True Then
    For i = 0 To lbSkills_Strengths2.ListCount - 1
        lbSkills_Strengths2.Selected(i) = True
    Next i
End If

If CheckBox2.Value = False Then
    For i = 0 To lbSkills_Strengths2.ListCount - 1
        lbSkills_Strengths2.Selected(i) = False
    Next i
End If

End Sub

Private Sub cmbAdd_Click()

For i = 0 To lbSkills_Strengths.ListCount - 1
    If lbSkills_Strengths.Selected(i) = True Then lbSkills_Strengths2.AddItem lbSkills_Strengths.List(i)
Next i
    
End Sub

Private Sub cmbRemove_Click()

Dim counter As Integer
counter = 0

For i = 0 To lbSkills_Strengths2.ListCount - 1
    If lbSkills_Strengths2.Selected(i - counter) Then
        lbSkills_Strengths2.RemoveItem (i - counter)
        counter = counter + 1
    End If
Next i

CheckBox2.Value = False

End Sub

Private Sub lbSkills_Strengths2_Click()

If Target.Column = 3 Then
    If oldVal = "" Then
    Else
        If newVal = "" Then
        Else
        Target.Value = oldVal & ", " & newVal
        End If
    End If
End If
        
End Sub

Private Sub UserForm_Initialize()

With lbSkills_Strengths
    .AddItem "Effective Communication"
    .AddItem "Technical"
    .AddItem "Problem solving"
    .AddItem "Creative"
    .AddItem "Teamwork"
    .AddItem "Planning"
    .AddItem "Organization"
    .AddItem "Leadership"
    .AddItem "Management"
    .AddItem "Adaptable"
    .AddItem "Flexible"
    .AddItem "Professional"
    .AddItem "Responsibility"
    .AddItem "Work Ethic"
    .AddItem "Energy"
    .AddItem "Positive Attitude"
    .AddItem "Commercial Awareness"
    .AddItem "Analytical"
    .AddItem "Drive"
    .AddItem "Time Management"
    .AddItem "Global Skills"
    .AddItem "Negotiation"
    .AddItem "Numeracy"
    .AddItem "Stress Tolerance"
    .AddItem "Independence"
    .AddItem "decision making"
    .AddItem "integrity"
End With
 

    
End Sub


I am getting an error for subscript out of range but I have a worksheet labeled CareerPathing
 
Last edited by a moderator:

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
It means there is no sheet by that name.
 
Upvote 0
A little more specifically..
Set ssheet = ThisWorkbook.Sheets("CareerPathing")
There is no sheet by that name within 'ThisWorkBook'

ThisWorkBook refers to the book that contains the code.
Not necessarily the book that is currently active, or any book that may have been opened in previous code.
 
Upvote 0
I do have a sheet with that name. That is what is so frustrating. :confused: Here is an image of the sheet.

Z
 
Upvote 0
No, you use the worksheet name.

But maybe the worksheet is named "Career Pathing", or has a leading or trailing space.

And I can't see your picture.
 
Upvote 0
No, you use the worksheet name.

But maybe the worksheet is named "Career Pathing", or has a leading or trailing space.

And I can't see your picture.

Private Sub btnSubmit_Click()
Dim ssheet As Worksheet

Set ssheet = Workbook.Sheets("CareerPathing") ***So you are saying right here that it should be "CareerPathing" The name of the document is "DAAProjectList" which i am assuming is the name of the workbook is that not correct?***
Code:
nr = ssheet.Cells(Rows.Count, 1).End(xlUp).Row + 1

ssheet.Cells(nr, 1) = Me.tbDAA
ssheet.Cells(nr, 2) = Me.tbCareerPath
ssheet.Cells(nr, 3) = Me.lbSkills_Strengths2


End Sub

See Above comment. I don't know how to attach a picture. I tried and it disappeared.
 
Last edited by a moderator:
Upvote 0
Try

Set ssheet = Workbooks("DAAProjectList.xlsx").Sheets("CareerPathing")

The extension (.xlsx) may or may not actually be required.
I think it depends on a certain setting within Windows, so try either way.
 
Upvote 0
Okay thank you I will try that and let you know if it works....I have to rebuild the form as it somehow disappeared. :mad:
 
Upvote 0
Okay so that worked for one problem. Now I have another issue.

Run-time error '424':

Object required.

When I debug it sends me to this section and highlights the text in red.
Rich (BB code):
Private Sub lbSkills_Strengths2_Click()

If Target.Column = 3 Then
If oldVal = "" Then
Else
If newVal = "" Then
Else
Target.Value = oldVal & ", " & newVal
End If
End If
End If

End Sub


If anyone knows how to attach a jpeg I would be happy to share an image of the userform and the worksheet I am trying to send the information to.
 
Last edited by a moderator:
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,034
Members
448,543
Latest member
MartinLarkin

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