Updating userform multiselect listbox with worksheet cell range

cuubc1

New Member
Joined
Sep 11, 2020
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
Hi,

I have created a useform which populates a worksheet table using comboboxes, dtpickers, textboxes,.... and a multiselect listbox.

My multiselect listbox is populating a table cell using the following code:


Private Sub CommandButton1_Click()

Dim count As Long

Dim listitems As String
With Q_conditions

For i = 0 To .ListCount - 1
If .Selected(i) Then listitems = listitems & .List(i) & ", "
Next i
End With



If Incid = "" Then
MsgBox ("Make sure you fill in the Incident Type field")
GoTo errorcheck
End If


If Sheets("Engine").Range("B4").Value <> "Edit" Then

If Application.WorksheetFunction.CountIf(Sheets("Tracker").Range("I6:I10008"), Who_name) > 0 Then

MsgBox "This person already has a Incident Record. Reference the Incident Tracker tab to see details and input any updates.", 0, "Employee has exisiting record"

GoTo errorcheck
End If


End If

If Sheets("Engine").Range("B4").Value = "New" Then

count = Sheets("Engine").Range("B3").Value + 1

ElseIf Sheets("Engine").Range("B4").Value = "Edit" Then

count = Sheets("Engine").Range("B5").Value

End If

'count = WorksheetFunction.CountA(Sheets("Tracker").Range("B:B"))

'''BEGIN INPUT DATA INTO REPORT'''
With Sheets("Tracker").Range("Data_Start2")
.Offset(count, 0).Value = count 'A
.Offset(count, 1).Value = Status 'B
.Offset(count, 2).Value = Incid 'C
.Offset(count, 3).Value = Summ 'D
.Offset(count, 4).Value = Country 'E
.Offset(count, 5).Value = State 'F
.Offset(count, 6).Value = Region 'G
.Offset(count, 7).Value = Locatonid 'H
.Offset(count, 8).Value = Who_name 'I
.Offset(count, 9).Value = Who_title 'J
.Offset(count, 10).Value = Who_id 'K
.Offset(count, 11).Value = Who_tm 'L
.Offset(count, 12).Value = When_ldw 'M
.Offset(count, 13).Value = When_symptom 'N
.Offset(count, 14).Value = When_test 'O
.Offset(count, 15).Value = When_testresult 'P
.Offset(count, 16).Value = Pay_sit 'Q
.Offset(count, 17).Value = Pay_type 'R
.Offset(count, 18).Value = Pay_reco 'S
.Offset(count, 19).Value = Pay_txt 'T
.Offset(count, 20).Value = Q_yn 'U
.Offset(count, 21).Value = When_quarantinestart 'V
.Offset(count, 22).Value = When_quarantineend 'w
.Offset(count, 23).Value = listitems 'x
.Offset(count, 24).Value = Q_satisifed 'y
.Offset(count, 25).Value = When_rtw 'z
.Offset(count, 26).Value = Tracing_lead 'AA
.Offset(count, 27).Value = Tracing_support 'AB
.Offset(count, 28).Value = When_contacttracing 'AC
.Offset(count, 29).Value = Tracing_condition 'AD
.Offset(count, 30).Value = When_infectiousperiod 'AE
.Offset(count, 31).Value = Tracing_shift 'AF
.Offset(count, 32).Value = Tracing_gov 'AG
.Offset(count, 33).Value = tracing_location 'AH
.Offset(count, 34).Value = Tracing_txt 'AI

End With


The listbox is populating 1 text box in a worksheet "Tracker" table . example "option 1, option 2, option 4,"


I have made an update/edit function for my userform and use it to pull data from the worksheet "Tracker" table to repopulate my userform to show me information about previous entry's:

Dim count As Integer

count = Application.WorksheetFunction.Match(Update, Sheets("Tracker").Range("Employee3"), 0)
Sheets("Engine").Range("B5").Value = count

Data_UF.Status = Sheets("Tracker").Range("Data_Start2").Offset(count, 1).Value
Data_UF.Incid = Sheets("Tracker").Range("Data_Start2").Offset(count, 2).Value
Data_UF.Summ = Sheets("Tracker").Range("Data_Start2").Offset(count, 3).Value
Data_UF.Country = Sheets("Tracker").Range("Data_Start2").Offset(count, 4).Value
...
Data_UF.Show

End Sub

Question:

What code can i write in my update/edit function to pull information from the text box originally populated by the listbox userform?

I basically want to pull the text box information and repopulate the multiselect listbox.

Thanks in advance for any advice.
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

cuubc1

New Member
Joined
Sep 11, 2020
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
Any support would be greatly appreciated. Thank you!
 

Watch MrExcel Video

Forum statistics

Threads
1,118,358
Messages
5,571,710
Members
412,414
Latest member
KasunC
Top