Subscript out of range error

[Joshdaniel]

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.

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

 

[shg]

What is Target? Where is it declared and initiliazed?

What are oldval and newval?

 

[Joshdaniel]

So what I have is two list boxes. One with a list of Skills and Strengths then an add and a remove button then a blank list box (lbSkills_Strengths2) the user will move the desired skill/strengths into the second box and when I hit submit button this code is supposed to allow for multiple skills to appear in the same cell separated by a comma. so the userform is supposed to do the following. The user clicks on the Add DAA Button and the userform pops up. They input the DAA and career path into a text box and the skills/strengths they have a list to choose from where they can choose multiple selections and they should look like column 3 below but when I hit submit it does not put anything into column 3.

DAA	Career Path	Skills/Strengths	Add DAA Button
J Daniel	Program Manager	Analytical, Technical, Creative,

<tbody>
</tbody>

Here is the entire code for reference if that helps. The red text is where the run-time error is saying the problem lies.

Dim i As Integer

Private Sub cmbSubmit_Click()
Dim ssheet As Worksheet

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

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