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]

It means there is no sheet by that name.

 

[Jonmo1]

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.

 

[Joshdaniel]

I do have a sheet with that name. That is what is so frustrating. Here is an image of the sheet.

 

[Joshdaniel]

Okay so do I need to use the name of the workbook there? "DAA Project List"

 

[shg]

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.

 

[Joshdaniel]

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?***

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.

 

[Jonmo1]

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.

 

[Joshdaniel]

Okay thank you I will try that and let you know if it works....I have to rebuild the form as it somehow disappeared.

 

[Joshdaniel]

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.

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.