VBA form with search and update controls for multi choice listbox

[MikeTE]

hello good people,

I'm looking for help with my code. I managed to add code for "search" (so fetch back the entries from table to form) however I can't get it to update and I believe the issue is caused by listbox. can anybody help me to get this to work? here's the code for entire form, the update is in bold. I would appreciate any help. thanks in advance!

Dim currentrow As Long
Private Sub CommandButton2_Click()
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("2019-2020 Tracker")
    Dim n As Long
 
    Dim Target As Worksheet
    Dim lastrow As Long
    Dim lastRowD As Long
 
 
 
If Application.WorksheetFunction.CountIf(sh.Range("F:F"), Me.TextBox3.Value) > 0 Then
MsgBox "Case already recorded for this employee", vbInformation
End If
n = sh.Range("p" & Application.Rows.Count).End(xlUp).Row
sh.Range("A" & n + 1).Value = Me.TextBox5.Value
sh.Range("B" & n + 1).Value = Me.ComboBox11.Value
sh.Range("C" & n + 1).Value = Me.ComboBox4.Value
sh.Range("D" & n + 1).Value = Me.ComboBox5.Value
sh.Range("e" & n + 1).Value = Me.TextBox2.Value
sh.Range("f" & n + 1).Value = Me.TextBox3.Value
sh.Range("g" & n + 1).Value = Me.TextBox4.Value
sh.Range("h" & n + 1).Value = Me.ComboBox66.Value
sh.Range("j" & n + 1).Value = Me.ComboBox77.Value
sh.Range("k" & n + 1).Value = Me.ComboBox80.Value
sh.Range("l" & n + 1).Value = Me.ComboBox81.Value
sh.Range("m" & n + 1).Value = Me.TextBox6.Value
sh.Range("n" & n + 1).Value = Me.TextBox7.Value
sh.Range("o" & n + 1).Value = Me.TextBox8.Value
sh.Range("p" & n + 1).Value = Me.ListBox1.Value
sh.Range("q" & n + 1).Value = Me.ListBox2.Value
sh.Range("r" & n + 1).Value = Me.TextBox9.Value
sh.Range("s" & n + 1).Value = Me.TextBox10.Value
sh.Range("u" & n + 1).Value = Me.TextBox11.Value
sh.Range("v" & n + 1).Value = Me.TextBox12.Value
sh.Range("w" & n + 1).Value = Me.ComboBox82.Value
sh.Range("x" & n + 1).Value = Me.TextBox13.Value
myVar = ""
For x = 0 To Me.ListBox1.ListCount - 1
If Me.ListBox1.Selected(x) Then
If myVar = "" Then
myVar = Me.ListBox1.List(x, 0)
Else
myVar = myVar & "," & Me.ListBox1.List(x, 0)
End If
End If
Next x
ThisWorkbook.Sheets("2019-2020 Tracker").Range("p" & n + 1) = myVar
myVar = ""
For x = 0 To Me.ListBox2.ListCount - 1
If Me.ListBox2.Selected(x) Then
If myVar = "" Then
myVar = Me.ListBox2.List(x, 0)
Else
myVar = myVar & "," & Me.ListBox2.List(x, 0)
End If
End If
Next x
ThisWorkbook.Sheets("2019-2020 Tracker").Range("q" & n + 1) = myVar
lastRowD = sh.Cells(sh.Rows.Count, "B").End(xlUp).Row
Me.TextBox5.Value = ""
Me.ComboBox11.Value = ""
Me.ComboBox4.Value = ""
Me.ComboBox5.Value = ""
Me.TextBox2.Value = ""
Me.TextBox3.Value = ""
Me.TextBox4.Value = ""
Me.ComboBox66.Value = ""
Me.ComboBox77.Value = ""
Me.ComboBox80.Value = ""
Me.ComboBox81.Value = ""
Me.TextBox6.Value = ""
Me.TextBox7.Value = ""
Me.TextBox8.Value = ""
Me.ListBox1.Value = ""
Me.ListBox2.Value = ""
Me.TextBox9.Value = ""
Me.TextBox10.Value = ""
Me.TextBox11.Value = ""
Me.TextBox12.Value = ""
Me.ComboBox82.Value = ""
Me.TextBox13.Value = ""
MsgBox "Done!"
End Sub
Private Sub COMBOBOX4_CLICK()
Dim x As Integer
x = ComboBox4.ListIndex
Select Case x
Case Is = 0
ComboBox5.RowSource = ""
Case Is = 1
ComboBox5.RowSource = ""
Case Is = 2
ComboBox5.RowSource = ""
Case Is = 3
ComboBox5.RowSource = ""
Case Is = 4
ComboBox5.RowSource = ""
Case Is = 5
ComboBox5.RowSource = ""
Case Is = 6
End Select
End Sub

Private Sub CommandButton3_Click() ''''''''''''''''''DELETE''''''''''''''
Dim lastrow
Dim myfname As String
lastrow = Sheets("2019-2020 Tracker").Range("A" & Rows.Count).End(xlUp).Row
myfname = TextBox3.Value
For currentrow = 3 To lastrow
If Cells(currentrow, 6).Value = myfname Then
Cells(currentrow, 1).EntireRow.Delete
End If
Next currentrow
TextBox3.SetFocus
End Sub

Private Sub CommandButton4_Click() ''''''''''''''''''SEARCH''''''''''''
Dim lastrow
Dim mylname As String
lastrow = Sheets("2019-2020 Tracker").Range("A" & Rows.Count).End(xlUp).Row
mylname = TextBox3.Value
For currentrow = 3 To lastrow
If Cells(currentrow, 6).Value = mylname Then

TextBox2.Value = Cells(currentrow, 5).Value
TextBox3.Value = Cells(currentrow, 6)
TextBox5.Value = Cells(currentrow, 1)
ComboBox11.Value = Cells(currentrow, 2)
ComboBox4.Value = Cells(currentrow, 3)
ComboBox5.Value = Cells(currentrow, 4)
TextBox4.Value = Cells(currentrow, 7)
ComboBox66.Value = Cells(currentrow, 8)
ComboBox77.Value = Cells(currentrow, 10)
ComboBox80.Value = Cells(currentrow, 11)
ComboBox81.Value = Cells(currentrow, 12)
TextBox6.Value = Cells(currentrow, 13)
TextBox7.Value = Cells(currentrow, 14)
TextBox8.Value = Cells(currentrow, 15)
TextBox9.Value = Cells(currentrow, 18)
TextBox10.Value = Cells(currentrow, 19)
TextBox11.Value = Cells(currentrow, 20)
TextBox12.Value = Cells(currentrow, 21)
ComboBox82.Value = Cells(currentrow, 22)
TextBox13.Value = Cells(currentrow, 23)

End If
Next currentrow
End Sub

Private Sub CommandButton5_Click() '''''''''''''''''''''UPDATE'''''''''''
Dim totalrow As Long
totalrow = Sheets("2019-2020 Tracker").Range("f2").CurrentRegion.Rows.Count
For currentrow = 3 To totalrow
If Trim(TextBox3) = Trim(Cells(currentrow, 6)) Then
     
     
      Cells(currentrow, 6) = TextBox3
     
     
End If
Next currentrow

End Sub

Private Sub UserForm_Initialize()
ComboBox4.RowSource = "VBA_RANGE"

With Me.ComboBox81 ''''Formal / Informal''''''
.AddItem "Formal"
.AddItem "Informal"
End With
With Me.ComboBox80 ''''Case Type''''''
.AddItem ""
.AddItem ""
.AddItem ""
.AddItem ""
End With
With Me.ComboBox82 ''''Category ''''''
.AddItem ""
.AddItem ""
.AddItem ""
.AddItem ""

End With
With Me.ComboBox11 ''''status''''''
.AddItem "Live"
.AddItem "On hold"
.AddItem "Closed"
End With

With Me.ComboBox66 '''''grade''''''
.AddItem ""
.AddItem ""
.AddItem ""
End With
With Me.ComboBox77 '''''GENDER''''''
.AddItem ""
.AddItem ""
.AddItem ""
.AddItem ""
.AddItem ""

End With
End Sub

 

[herofox]

where's the file !!!?

 

[MikeTE]

sorry here it is. I had to strip it of some info so may not work properly now

 

[Kenneth Hobson]

Please paste code between code tags. Click the dropdown right of the smiley face and pick code.

Since the file does not have named ranges for the rowsource fills, it errors which makes it hard to test.

Without data in the sheet from row 3 and down, totalrow="".

When testing and sharing a file, it is best to make one that is simple with sensitive data removed and duplicates your current problem.

At some point, you might want to make code easier to maintain and read by adding the column letter(s) to the Tag property for some controls. One can then iterate all controls and set the cell value with that column letter and some row number or vice-versa. This works well for linking cell values to control values using ControlSource property. Change one value and you change the other. The ControlSource value can be changed based on a combobox Change event. e.g. If combobox1.ListIndex=0, add some offset if needed like 2 so that is the row number to add for ControlSource. The combobox can be multicolumn so that the user can easily see what row to change to to edit data.

 

[MikeTE]

hi Kenneth,

sorry you lost me. where do I find the code?

I know that could be done better but I'm a self thought VBA user so I'm lacking of the core skills with coding, that's the best I could do really XD

 

[vletm]

MikeTE
Have You read these?

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org

Posting a sample workbook

The Chandoo.org Forum now has the facilities to upload files directly into a post. Simply use either the Media icon in the Edit menu or the Upload a File Button next to the Save Changes button Alternatively In the Chandoo.org Blog or here in the Forums you can still upload files using a Free...

chandoo.org