Selected Answer
Hi again Amin
Now you've attached the file I see it includes an implied request:
I need here code to delete selected item in the list box and the sheet also depends on the department Name( Food, Beverage etc)
but this is a Q&A Forum really (though we might give a lot more help for your first question or two) so I'll give you hints on your question instead...
Your Data worksheet (object Da in your code) has 10 tables which can be addressed by name. e.g.
Da.ListObjects("T_Food").HeaderRowRange(1,2)
refers to the second column of that table, i.e. the heading "Item Name".
When you're deleting table rows, you can refer to rows like:
Data.ListObjects("T_Food").DataBodyRange.Rows(2)
which is the second data item and the range A8:H8.
Within that, you can use:
Data.ListObjects("T_Food").DataBodyRange.Cells(2,2)
which is the second column in row 2 so the subcategory "Ground Beef".
You can then read properties or use methods to manipulate table elements.
Hope this is enough help to get you going since I'll be busy with family issues for tomorrow at least.
Revision 01 February 2024
In the FIRST attached file, I've added this code behind the Delete button of your UserForm
Private Sub CbDelete_Click()
Dim n As Long, Deltd As String
On Error Resume Next
Set Da = Data
' check UF has been completed
If comDepartment.Text = "" Or CbSubCategory.Text = "" Or txtItemName.Text = "" Then
MsgBox "Please complete entries"
Exit Sub
End If
' search in Department table (starting "T_"), looking for Item
With Da.ListObjects("T_" & comDepartment).ListColumns("Item Name").DataBodyRange
' loop down contents
For n = 1 To .Count
' if row matches (and Sub Category is same)...
If txtItemName.Text = .Cells(n) And CbSubCategory.Text = .Cells(n).Offset(0, 6) Then
' delete and shift lower rows up
.Cells(n).Offset(0, -1).Resize(1, 8).Delete Shift:=xlShiftUp
Deltd = txtItemName.Text
' update ListBox
Call CbSubCategory_Change
' tell user
MsgBox "Deleted entry for " & Deltd
Exit For
End If
Next n
End With
End Sub
The ListBox gets updated using the cbSubCategory_Change module but for some reason it doesn't limit to just the chosen values- I used Shisha / Charcoal to test). I don't have time to fix your cbSubCategory_Change code to use table names but you should have enough information to do so yourself.
Revision #2 01 February 2024
The SECOND file attached corrects the ListBox Update with this code, with additions in bold:
Private Sub CbDelete_Click()
Dim n As Long, Deltd As String
On Error Resume Next
Set Da = Data
' check UF has been completed
If comDepartment.Text = "" Or CbSubCategory.Text = "" Or txtItemName.Text = "" Then
MsgBox "Please complete entries"
Exit Sub
End If
' search in Department table (starting "T_"), looking for Item
With Da.ListObjects("T_" & comDepartment).ListColumns("Item Name").DataBodyRange
' loop down contents
For n = 1 To .Count
' if row matches (and Sub Category is same)...
If txtItemName.Text = .Cells(n) And CbSubCategory.Text = .Cells(n).Offset(0, 6) Then
'disable Dept change
UFEvents = False
' delete and shift lower rows up
.Cells(n).Offset(0, -1).Resize(1, 8).Delete Shift:=xlShiftUp
Deltd = txtItemName.Text
' update ListBox
Call CbSubCategory_Change
' tell user
MsgBox "Deleted entry for " & Deltd
UFEvents = True
Exit For
End If
Next n
End With
End Sub
This is becasue the comDepartment_Change was being triggered. I had to declare a variable at the top of the UserForm module as follows:
Dim UFEvents As Boolean
then use this to cut short that module with a new line (in bold):
Private Sub comDepartment_Change()
On Error Resume Next
If UFEvents = False Then Exit Sub
<< existing code >>
Hope this answers your stated question above if so please remember to mark this Answer as Selected.
