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.
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.