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

Update item or modified

0

In this code every time I try to Click  commendButon (Update) giving me an error message 

rum time error '438'

object doesn't support this property or method  

Private Sub CmdUpdate_Click()
 Const PW As String = "8521" 'using const for fixed values
    Dim allTables(), lo As ListObject, rw As ListRow, tName
     allTables = Array("T_Food", "T_Beverage", "T_Shisha", "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 "Shisha": tName = "T_Shisha"
        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
        '-----------------------------------------------------
           'here is the error 
        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, 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

Here is the error 

 'Here is the error 
        rw.DataBodyRange.Value = Array(Me.txtCode.Text, new_name, _
                                    Me.txtCode.Text, Me.comUnit.Text, _
                                    Me.txtPrice.Text, Me.TxtSalePrice.Text, _ 
                                     Me.comDepartment.Text)
Answer
Discuss

Answers

0
Selected Answer

Hi again Amin

Your Userform frmCodeItem uses the variable rw, which you declared as a ListRow.

In the Update code, you match it to a single row and get the error since a single ListRow does not have a DataBodyRange- that's a property of a ListObject). 

In the attached revised file, I've corrected the eroor by setting value of the .Range property (but had to correct the array items since it was putting things in the wrong places and causing errors). I also call a sub to update the list once it has been updated- see chnages in bold in the code extract below:

        '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)

        ' update the row values (but I don't know where Start and Price come from)
        rw.Range.Value = Array(Me.txtCode.Text, new_name, Me.comUnit.Text, _
                                    Me.txtPrice.Text, Me.TxtSalePrice.Text, _
                                    "", "", Me.CbSubCategory.Text)

        '-----------------------------------------------------

        Data.Protect Password:=PW
    Else
        MsgBox "Edited row not found!"
    End If

    ' update the userfrom
    Call CbSubCategory_Change

End Sub

If you add values for txtPrice and TxtSalePrice, they now appear in the right vcolumns (under Sisha at least) but it you launch the form again, the txtPrice incorrectly shows the TxtSalePrice - I leave you to find and fix that error (which not introduced by my changes!).

Hope this fixes things for you- if so please rememeber to mark this Answer as Selected.

Discuss

Discussion

I tried to edit item name and after I clicked update its gave me a message run time error 9 
subscribe out of the range  

Function AnyTableRowMatch(arrTableNames, colName, colValue) As ListRow
    Dim el, rw As ListRow
    For Each el In arrTableNames
    
      here the error
        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
Amin25 (rep: 16) Jan 21, '24 at 4:06 am
Ah. Think I understand now. I'll try to respond later today (in an hour or two)
John_Ru (rep: 6142) Jan 21, '24 at 6:05 am
Oops- gave the answer in a revision (and file) to your other question- please check via Alerts. I don't have time to fix that today!
John_Ru (rep: 6142) Jan 21, '24 at 8:47 am
Thank you so much
Amin25 (rep: 16) Jan 21, '24 at 9:47 am
Thanks for selecting my Answer, Amin. Sorry for the confusion. 
John_Ru (rep: 6142) Jan 21, '24 at 10:02 am
Add to Discussion


Answer the Question

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