Problem with Update function on a ListObject

Question

This is was supposed to be a prof of concept, I have a much larger project with the same issue and have retied different approaches and always end up with the same results. I was creating additional errors in my main code so I wrote this to test myself on the functionality of what I am doing. So just a heads up, I am coming back into programming after a 8 Year stint doing Engineering/Tech Stuff in the field. And therefor I am making some Newbie mistakes again. I need help seeing my error.

When I attempt to update the .Value of a Cell in a listObject it evokes a event function on the UserForm.

To explain a bit more:

I have a table - Table1 with ID|Arg1|Arg2|Arg3|Arg4

I put a simple Int in ID ie 1, and simple text(string) Data in Arg1-Arg4 ie Test1, Data1, Data2, Data3

I created a Simple UserForm1(Default Name) to put a listBox and 4 TextBoxes

I have a Function ListBox1_Click() that populates the UserFrom1 Textboxes with the data from the ListObject(Table1). This function works just fine.

I also have a function that Updates the ListObject(Table1). Here is the Problem. When you reference the ListObject(Table1)'s Cell Value to change it, Then ListBox1_Click() Event Function gets evoked and my TextBoxes Revert. I have Destructers for the my Object that I 'Set', But I have no idea why/how the function is called.

Private Sub cbUpdate_Click()

    Dim myListObj As ListObject
    Dim myRange As Range

    Set myListObj = ActiveSheet.ListObjects("Table1")
    Set myRange = myListObj.ListColumns(1).DataBodyRange.Find(What:= _ 
      Trim(Me.ListBox1), SearchDirection:=xlNext, MatchCase:=False)

    myRange.Offset(, 1).Value = Me.tbArg1
    myRange.Offset(, 2).Value = Me.tbArg2
    myRange.Offset(, 3).Value = Me.tbArg3
    myRange.Offset(, 4).Value = Me.tbArg4

    Set myRange = Nothing
    Set myListObj = Nothing
End Sub

Private Sub ListBox1_Click()

    Dim myListObj As ListObject
    Dim myRange As Range

    Set myListObj = ActiveSheet.ListObjects("Table1")
    Set myRange = myListObj.ListColumns(1).DataBodyRange.Find(What:= _ 
      Trim(Me.ListBox1), SearchDirection:=xlNext, MatchCase:=False)

    Me.tbArg1 = myRange.Offset(, 1).Value
    Me.tbArg2 = myRange.Offset(, 2).Value
    Me.tbArg3 = myRange.Offset(, 3).Value
    Me.tbArg4 = myRange.Offset(, 4).Value

    Set myRange = Nothing
    Set myListObj = Nothing
End Sub

I need to understand how to get the ListObject(Table1) Cells to receive updated values without evoking uncalled functions/Methods.

Eric Lendrum Eric Lendrum · Accepted Answer · 2019-04-24T19:45:26

Ok, so them answer seems to be that I need a local variable to pass the value of the userForm controls thru. When I used the listObjects .Add function and passed data durectly from the controls(TextBoxs) it works great. But, When Updating if I pass values directly from the control it invokes the listbox's _Click() event function. If I 'Dim myArg1 As Variant' and assign the controls value to it and them when updating the ListObject Cell value from that, presto it takes the value with no issues. I'm note sure why this works, but, I get to code on! I hate my answer more than I hated my problem. I spent days on trouble shooting this issue before deciding to post it. My Answer is not very elegant but it seems to be functional. Thanks for reading.

Problem with Update function on a ListObject

1 Answers