Hello all,
I have a UserForm where users can select a "log number" from a drop-down list and it brings up the remaining data in that row.
They can then edit and click the Update button which will transfer the new data back onto the Table.
My problem is:
I have a ListBox (named ListBox_Category) where users can select as many options as they like.
When I bring up the Edit box, this list is not populated at all.
If I select some options, these are not also transferred back onto the Table.
My question is:
Is it possible, using multi-select list boxes, to have this data in an Edit UserForm?
Let me know if having the full Excel is required, and I'll replace the data with test text.
Many thanks,
James
Code
Private Sub ComboBox_LogNumber_Change()
Dim Log As Integer
If Me.ComboBox_LogNumber.Value = "" Then
MsgBox "Please enter a log number.", vbExclamation, "Edit Log"
Exit Sub
End If
Log = ComboBox_LogNumber.Value
On Error Resume Next
Me.ComboBox_Initials.Value = Application.WorksheetFunction.VLookup(Log, Sheets("ACCLog").Range("A3:G30000"), 2, 0)
Me.TextBox_DateTime.Value = Application.WorksheetFunction.VLookup(Log, Sheets("ACCLog").Range("A3:G30000"), 3, 0)
Me.ListBox_Category.Value = Application.WorksheetFunction.VLookup(Log, Sheets("ACCLog").Range("A3:G30000"), 4, 0)
Me.ComboBox_Status.Value = Application.WorksheetFunction.VLookup(Log, Sheets("ACCLog").Range("A3:G30000"), 5, 0)
Me.CheckBox_CET.Value = Application.WorksheetFunction.VLookup(Log, Sheets("ACCLog").Range("A3:G30000"), 6, 0)
Me.TextBox_Detail.Value = Application.WorksheetFunction.VLookup(Log, Sheets("ACCLog").Range("A3:G30000"), 7, 0)
End Sub
Private Sub CommandButton_Close_Click()
Unload Me
ActiveSheet.Protect AllowFormattingCells:=True, DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFiltering:=True, Password:="secret"
End Sub
Private Sub CommandButton_UpdateLog_Click()
If Me.ComboBox_LogNumber.Value = "" Then
MsgBox "Please enter a log number.", vbExclamation, "Edit Log"
Exit Sub
End If
LogNumber = Me.ComboBox_LogNumber.Value
Sheets("ACCLog").Select
Dim rowselect As Double
Dim msg As String
Dim ans As String
rowselect = Me.ComboBox_LogNumber.Value
rowselect = rowselect + 2
Rows(rowselect).Select
Cells(rowselect, 2) = Me.ComboBox_Initials.Value
Cells(rowselect, 3) = Me.TextBox_DateTime.Value
Cells(rowselect, 4) = Me.ListBox_Category.Value
Cells(rowselect, 5) = Me.ComboBox_Status.Value
Cells(rowselect, 6) = Me.CheckBox_CET.Value
Cells(rowselect, 7) = Me.TextBox_Detail.Value
rowselect = rowselect - 1
msg = "Sl No " & rowselect & " Successfully Updated...Continue?"
Unload Me
ans = MsgBox(msg, vbYesNo, "Update")
If ans = vbYes Then
EditLog.Show
Else
Sheets("ACCLog").Select
End If
End Sub