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.
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Any support would be greatly appreciated. Thank you!
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top