Building Userform that adds additional line of Data

[Joshdaniel]

Hi I am building a userform that will add 3 fields to the next row in a worksheet. The worksheet has 3 columns with 2 command buttons in the 4th column labeled Add DAA and Edit DAA. Like below
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]DAA
[/TD]
[TD]Career Path
[/TD]
[TD]Skills/Strengths
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]Add DAA
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]Edit DAA
[/TD]
[/TR]
</tbody>[/TABLE]

The UserForm has a TextBox for DAA, TextBox for Career Pathing and 2 listboxes to handle the addition of specific Skills and Strengths. Looks kind of like the below.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]DAA
[/TD]
[TD]Career Path
[/TD]
[TD]Skills/Strengths
[/TD]
[TD][/TD]
[TD]Skills/Strengths
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]Add
[/TD]
[TD][/TD]
[TD="align: center"]Submit
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]Remove
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Technical
Problem Solving
Creative
Teamwork
Planning
Organization
leadership
[/TD]
[TD="align: center"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][] Check/UnCheck All
[/TD]
[TD][/TD]
[TD][]Check/Uncheck All
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The items in the Skills and Strengths columns on the left can be added to the column on the right. Then when you hit the Submit Button it should populate all the data into the worksheet. I am able to get columns 1 and 2 to populate but the listbox is not populating in column 3 I have code that I am assuming will push multiple skills into one cell in a comma delimited list. It is not working. Please help.

See my code for this below.

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

For i = 0 To Me.lbSkills_Strengths2.ListCount - 1
If Me.lbSkills_Strengths2.Selected(i) = True Then
If strAction = vbNullString Then
strAction = Me.lbSkills_Strengths2.List(i)
Else
strAction = strAction & ", " & Me.lbSkills_Strengths2.List(i)
End If
End If
Next i

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()

'Variable Declaration
Dim iCnt As Integer

'Move Selected Items from lbSkills_Strengths to lbSkills_Strengths2

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

For iCnt = Me.lbSkills_Strengths.ListCount - 1 To 0 Step -1
If Me.lbSkills_Strengths.Selected(iCnt) = True Then
Me.lbSkills_Strengths.RemoveItem iCnt
End If
Next


End Sub

Private Sub cmbRemove_Click()

'Variable Declaration
Dim iCnt As Integer

'Move Selected Items from lbSkills_Strengths2 to lbSkills_Strengths
For iCnt = 0 To Me.lbSkills_Strengths2.ListCount - 1
If Me.lbSkills_Strengths2.Selected(iCnt) = True Then
Me.lbSkills_Strengths.AddItem Me.lbSkills_Strengths2.List(iCnt)
End If
Next

For iCnt = Me.lbSkills_Strengths2.ListCount - 1 To 0 Step -1
If Me.lbSkills_Strengths2.Selected(iCnt) = True Then
Me.lbSkills_Strengths2.RemoveItem iCnt
End If
Next

End Sub

Private Sub lbSkills_Strengths2_Click()

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
​

 

[dmt32]

Hi,
In your cmbSubmit_Click code try replacing this line:

ssheet.Cells(nr, 3) = Me.lbSkills_Strengths2

with this:

ssheet.Cells(nr, 3) =  strAction

Dave

 

[Joshdaniel]

That worked perfectly. Thank you Dave!