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

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

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,309
Messages
5,571,468
Members
412,395
Latest member
nielsvanlit
Top