Modify table data in excel sheet by userform

0 votes

When I click on any item in the listbox in the image's userform, all of the data will be filed into the textbox when I use it to add and amend data to tables.

enter image description here

But modify Button(Edit) the code is referring to columns so I want to make the code refer to the table name instead of the columns

The code

Private Sub CmdUpdate_Click()

Dim zz As Range
Dim rr As Range
Dim c As Range
Dim old_name, new_name, fname, new_mr, fmr

Dim EditRow As Cells

Set editBev = Data.ListObjects("T_Beverage")


ThisWorkbook.Activate

If Me.comDepartment.Text = "" Or Me.txtItemName.Text = "" Or Me.txtPrice.Text = "" _
  Or Me.comUnit.Text = "" Then
MsgBox "Please enter data  ", vbCritical, "Inventory program "
Exit Sub
End If

Set ww = Application.WorksheetFunction


old_name = Me.TxtEditItem.Value
new_name = Me.txtItemName.Text
'===== no change in name
If new_name = old_name Then GoTo 5

fname = ww.CountIf(Data.Range("T_Food[[Item Name]]"), new_name)
fname = ww.CountIf(Data.Range("T_Beverage[[#All],[Item Name]]"), new_name)
fname = ww.CountIf(Data.Range("T_Other[[#All],[Item Name]]"), new_name)

If fname >= 1 Then: MsgBox "  This name already exists  ", vbCritical, "Inventory Program ": Exit Sub
'==========================

5 Application.ScreenUpdating = False

  Application.Calculation = xlCalculationManual
  
'==change in data table====
With Data
.Unprotect ("0000")

If Me.comDepartment.Value = "Food" Then

For Each c In Data.Range("T_Food[[#All],[Item Name]]") 
If old_name = c Then GoTo 1
Next
Apr 2, 2023 in Others by narikkadan
• 63,420 points
480 views

1 answer to this question.

0 votes

Try this:

Private Sub CmdUpdate_Click()
    
    Const PW As String = "000" 'using const for fixed values
    Dim allTables(), lo As ListObject, rw As ListRow, tName
    
    allTables = Array("T_Food", "T_Beverage", "T_Other")
    
    If Me.comDepartment.Text = "" Or Me.txtItemName.Text = "" Or _
       Me.txtPrice.Text = "" Or Me.comUnit.Text = "" Then
        MsgBox "Please enter data  ", vbCritical, "Inventory program "
        Exit Sub
    End If
    
    old_name = Me.TxtEditItem.Value
    new_name = Me.txtItemName.Text
    
    If new_name <> old_name Then
        'see if the new name already exists in any of the tables
        If Not AnyTableRowMatch(allTables, "Item Name", new_name) Is Nothing Then
            MsgBox "The new name '" & new_name & "' already exists  ", _
                   vbCritical, "Inventory Program "
            Exit Sub
        End If
    End If
    
    'select the correct table
    Select Case Me.comDepartment.Value
        Case "Food": tName = "T_Food"
        Case "Beverage": tName = "T_Beverage"
        Case "Other": tName = "T_Other"
    End Select
    'find the record being edited
    Set rw = TableRowMatch(Data.ListObjects(tName), "Item Name", old_name)
    
    If Not rw Is Nothing Then
        Data.Unprotect PW
        'update the row
        rw.DataBodyRange.Value = Array(Me.txtCode.Text, new_name, _
                                    Me.txtCode.Text, Me.comUnit.Text, _
                                    Me.txtPrice.Text, Me.TxtSalePrice.Text, _
                                    Me.comDepartment.Text)
        Data.Protect Password:=PW
    Else
        MsgBox "Edited row not found!"
    End If
End Sub

'find any matching row in tables with names in `arrTableNames`
Function AnyTableRowMatch(arrTableNames, colName, colValue) As ListRow
    Dim el, rw As ListRow
    For Each el In arrTableNames
        Set rw = TableRowMatch(Data.ListObjects(el), colName, colValue)
        If Not rw Is Nothing Then
            Set AnyTableRowMatch = rw
            Exit Function
        End If
    Next el
End Function


'Find any matching row for value `colValue` in listobject `1o`, column `colName`
'  Returns Nothing if no match
Function TableRowMatch(lo As ListObject, colName, colValue) As ListRow
    Dim el, lo As ListObject, loCol As ListColumn, lr As ListRow, m
    Set loCol = lo.ListColumns(colName)
    m = Application.Match(colValue, loCol.DataBodyRange, 0)
    If Not IsError(m) Then Set TableRowMatch = lo.ListRows(m)
End Function
answered Apr 2, 2023 by Kithuzzz
• 38,010 points

Related Questions In Others

0 votes
1 answer
0 votes
1 answer

Create charts in excel sheet [ in same sheet along with data C#]

Unfortunately, it's not that simple. Use the ...READ MORE

answered Oct 11, 2022 in Others by narikkadan
• 63,420 points
746 views
0 votes
0 answers

Data Driven Framework -- how to read and write in excel sheet using Selenium WebDriver with java

I'm using this code to read something, ...READ MORE

Oct 31, 2022 in Others by Kithuzzz
• 38,010 points
476 views
0 votes
1 answer

In a excel formula I need to create a list of names on one sheet based upon criteria/data of another sheet

The final formula is: =IF(ROWS($H$3:H3)<=$I$1,INDEX(Personnel! ...READ MORE

answered Nov 25, 2022 in Others by narikkadan
• 63,420 points
759 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
898 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
3,214 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
505 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
750 views
0 votes
1 answer

Select data that meet criteria from a table, adding it to a combobox in userform VBA Excel

Fill Combo Box With Matches Sub GetSourceAcc() ...READ MORE

answered Mar 26, 2023 in Others by Kithuzzz
• 38,010 points
455 views
0 votes
1 answer
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP