Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Loop until table column names


 I attached the file here and I only want in UF frmCodeItem,   CbSubCategory to mention the table instead the column and also frmCode same 

Private Sub CbSubCategory_Change()

    Dim sutr, sr As Long
    Dim deg1, deg2 As String
    LB_01.RowSource = Empty
    If Me.comDepartment.Value = Data.Range("AE7") Then
    'HERE Want to replace row number and column number with column table name          like (T_Food[Item Name])
    For sut = 7 To Data.Cells(Rows.Count, 2).End(xlUp).Row
    deg1 = Data.Range("H" & sut)   'here also replacing the latter H to (T_Food[Dep])

    deg2 = CbSubCategory.Value
    'TextBox1.Text = duzenle(TextBox1.Text)
    If UCase(deg1) Like UCase(deg2) & "*" Then
    LB_01.List(s, 0) = Data.Range("A" & sut)           'Here also
    LB_01.List(s, 1) = Data.Range("B" & sut)
    LB_01.List(s, 2) = Data.Range("C" & sut)
    LB_01.List(s, 3) = VBA.Format(Data.Range("D" & sut), "$#,##0.00")
    LB_01.List(s, 4) = VBA.Format(Data.Range("E" & sut), "$#,##0.00")
    LB_01.List(s, 5) = Data.Range("F" & sut)
    LB_01.List(s, 6) = ""
    LB_01.List(s, 7) = Data.Range("H" & sut)
    s = s + 1
    End If
            End If


Selected Answer

Hi again Amin

Please take care with your questions- you attached a file without your latest change (my recent addition of the Delete button code). The attached revised code includes that code and a revised code for CbSubCategory_Change - which populates the list using a singleloop rather than several If then loops per category as you had-as below:

Private Sub CbSubCategory_Change()

    Dim n As Long, s As Long    
    Dim deg2 As String, tName as String

    LB_01.RowSource = Empty

    deg2 = CbSubCategory.Value

    Select Case Me.comDepartment.Value

        Case "Food": tName = "T_Food"
        Case "Beverage": tName = "T_Beverage"
        Case "Shisha": tName = "T_Shisha"
        Case "Other": tName = "T_Other"
        ' do nothing if value isn't one of the above
        Case Else: Exit Sub
    End Select

    s = 0
    ' search in Department table, looking for matching subcategory
    With Data.ListObjects(tName)
        ' loop down contents
        For n = 1 To .ListColumns("Dep").DataBodyRange.Count
            ' if row matches Sub Category ...
            If UCase(deg2) = UCase(.ListColumns("Dep").DataBodyRange.Cells(n)) Then
                'then populate list
                LB_01.List(s, 0) = .ListColumns("Code #").DataBodyRange.Cells(n)
                LB_01.List(s, 1) = .ListColumns("Item Name").DataBodyRange.Cells(n)
                LB_01.List(s, 2) = .ListColumns("Unit").DataBodyRange.Cells(n)
                LB_01.List(s, 3) = Format(.ListColumns("Cost").DataBodyRange.Cells(n), "$#,##0.00")
                LB_01.List(s, 4) = Format(.ListColumns("Selling").DataBodyRange.Cells(n), "$#,##0.00")
                LB_01.List(s, 5) = .ListColumns("Price").DataBodyRange.Cells(n)
                LB_01.List(s, 6) = ""
                LB_01.List(s, 7) = .ListColumns("Dep").DataBodyRange.Cells(n)

                s = s + 1
            End If
        Next n
    End With

    ' show the item number for the last item in list (if any)
    If s > 0 Then Me.txtCode.Value = Me.LB_01.List(s - 1, 0) + 1

End Sub

You'll see (in bold above) that the table is identified by name (using a modified version of your Select tname code from elsewhere in the UF) and the ListBox columns populated using column names as requested.

Hope this works well for you (I did only limited testing)- if so, please remember to mark this Answer as Selected



Amin. Please note that if you want to use this approach for other UF controls (e.g. ComboBoxes, buttons), you'll have to modify those codes yourself- I have no intention of modifying all your code, even if you ask separate questions.
John_Ru (rep: 5992) Feb 5, '24 at 6:36 am
For UF frmcode want txtcode is look for item code   (for example if CbSubCategory value is chicken breast so txtcode show me the code of this item etc. this is the last question really 
Amin25 (rep: 16) Feb 5, '24 at 8:04 am
Amin. Sorry but I'm getting fed up with this...

This is a Q&A Forum- you ask a question, we answer it and (if correct) you select the (best) answer. We sometimes allow follow-on questions from new users but generally we don't want a question expanding.

I already told you (twice) that I won't correct more of your code- you will learn nothing if I do everything for you.

Your additional request should involve a simple loop (and you have seen several now). Please try to work it out and only come back if you hit a problem that you can't resolve after a few attempts.

In the meantime, please select my Answer (if the CbSubCategory_Change code works in the file I attached).
John_Ru (rep: 5992) Feb 5, '24 at 8:43 am
Sorry if I disturbed you, Actually I don't have any experience with VBA what I'm doing with my program is looking in Google for code and trying to solve it with my program, and no one before helped me like you, but these codes I can't find to solve this code as I don't have any experience, by the way I appreciate you and thank you so much for everything  
Amin25 (rep: 16) Feb 5, '24 at 9:14 am
Amin. Understood about your VBA experience but I just don't have the time to guide you through learning VBA. If you're doing things for work (and you seem to be), I'd recommend you buy Don's course (advertised on this site). I think it's very good. BTW I get absolutely no money from this site or for recommending that course. Thanks again for selecting my Answer.
John_Ru (rep: 5992) Feb 5, '24 at 9:28 am
I will buy this course 
Amin25 (rep: 16) Feb 5, '24 at 9:37 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login