Userform ListBox when source is blank

[david763]

hello, help PLEASE!

I haven't used userforms very much before so still trying to understand what causes error messages.

Scenario:
• I have a master list of job roles which I load into ListBox1 on userform.
• There is another worksheet which contains a list of business functions or activies. Against each function, ONE OR MORE job roles are assigned from the master list (in the one cell, separated by "; " and line break).

The idea is that when I double-click one of these cells:
(1) the user form opens
(2) master list loads into ListBox1
(3) contents of active cell are unravelled ("textsplit") and loaded into ListBox2 (and also removes these from ListBox1)
(4) I can then transfer jobs between ListBox1 and ListBox2, then save back to the cell from where they came...

So far:
Much trial and error, this works perfectly when there is already some information in the active cell, that is, it loads the master list and contents of current cell into listbox 1 & 2 respectively. I can move the jobs from one box to the other, then save the changes back to the original cell. Yay!

My problem:
When the active (source) cell is BLANK:
(1) I double click and ListBox1 is populated with the full list as planned;
(2) ListBox2 is empty (consistent with empty active cell)
(3) I transfer information from ListBox1 to ListBox2 successfully
(4) I click update, then...

I get "runtime error 13 type mismatch"

The error occurs in the ActiveCell row in code below...

Private Sub btn_update_Click()

ActiveCell.Value = WorksheetFunction.TextJoin("; " & Chr(10), True, ListBox2.List)

Unload Me

End Sub

Any ideas why this works perfectly when there is already information in the active cell and throws an error when I'm running from an empty cell? The ListBox2 contents look OK before I update...

 

[david763]

...it just got worse...

All previous testing had two or more EXISTING job roles in a cell.
In addition to the previous blank active cell problem, I get a different error if there is already a single job role in the active/source cell...

runtime error 381 - could not set the List property. Invalid property array index.

This occurs at the Set form row in the code below...

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim form As New frm_update_list

CurrCol = ActiveCell.Column
ValidCol = Application.WorksheetFunction.CountIf(Sheets("admin tables").Range("dbl_click_column"), CurrCol)
If ValidCol = 0 Then GoTo NoCanDo:

Sheets("admin tables").Range("input1") = ActiveCell.Value
Set form = New frm_update_list

form.Show

Exit Sub

NoCanDo:
MsgBox "can't edit this cell", vbOKOnly, "Oops"

End Sub

I'm not sure but I think this relates to the initialization of the form..???

Private Sub UserForm_Initialize()

Dim i As Integer, j As Integer

ListBox1.List = Range("tbl_staff[Function Resp]").Value

If Not IsEmpty(Range("input1")) Then
ListBox2.List = Range("InputGuess2#").Value
End If

For i = 0 To ListBox2.ListCount - 1
For j = ListBox1.ListCount - 1 To 0 Step -1

If ListBox2.List(i) = ListBox1.List(j) Then
ListBox1.RemoveItem j
End If
Next
Next

End Sub

This must be fixable but I feel out of my depth here. Can someone please help me sort this out..?