Can't find my run time error

[ashley12]

Hi everyone,

So I have this userform for data entry (image below). The problem started when I add the "Delete selected row" button.

When I tried adding and deleting a rows, it works fine the first few times, but then, the error message (2nd image) popped up around the 5th or 6th addition. Do you know what may have gone wrong? Thanks so much for your time.

Here is my codes to add a row:

Sub AddIng()
    
    Dim ListBoxRow
    Dim IngLstTB As ListObject
        
    Set IngLstTB = IngListBox.ListObjects(1)
    
    'Add ingredients to ingredient list box
    IngLstTB.ListRows.Add   '<------ Here is where the debugger pointed to
    ListBoxRow = IngLstTB.ListRows.Count
    IngLstTB.DataBodyRange.Cells(ListBoxRow, 1) = Meal_form.Ingredients.Value
    IngLstTB.DataBodyRange.Cells(ListBoxRow, 2) = Meal_form.Weight.Value
    
    'show the list of entered data in the listbox
    Meal_form.Controls("lstIngredient").RowSource = "Ingredient_listbox!" & IngListBox.ListObjects(1).DataBodyRange.Address
       
    'reset fields
    Meal_form.Controls("Ingredients").Value = ""
    Meal_form.Controls("Weight").Value = ""
    
    
End Sub

Here is my codes to delete a row:

Private Sub cmdDeleteIng_Click()
       
    Dim IngRow As Long
    Dim ListBoxRow As Integer

    ListBoxRow = lstIngredient.ListIndex + 1
      
    IngListBox.ListObjects(1).ListRows(ListBoxRow).Delete

End Sub

 

[Michael M]

Does it happen when you are down to 1 row in the list box ?
Have you tried removing the +1 from this row

ListBoxRow = lstIngredient.ListIndex + 1

 

[ashley12]

Hi Michael,

I don't really know the pattern of the error. It show up pretty random. Sometimes when I have some rows in the listbox and sometimes when I have one left. Sometimes it works fine for more then 10 entries, but if I keep adding and deleting, the error will show up at some point.

It only deletes the correct row if I have the +1.

Also, for some reason, after this error, my excel sheet sort of stop working normally. Do you think it's something beyond the VBA codes?

 

[Michael M]

Do you think it's something beyond the VBA codes?

I doubt it.
Try inserting On error as below

Sub AddIng()
    
    Dim ListBoxRow
    Dim IngLstTB As ListObject
        
    Set IngLstTB = IngListBox.ListObjects(1)
    
    'Add ingredients to ingredient list box
On error resume next    
IngLstTB.ListRows.Add   '<------ Here is where the debugger pointed to
    ListBoxRow = IngLstTB.ListRows.Count
    IngLstTB.DataBodyRange.Cells(ListBoxRow, 1) = Meal_form.Ingredients.Value
    IngLstTB.DataBodyRange.Cells(ListBoxRow, 2) = Meal_form.Weight.Value
    
    'show the list of entered data in the listbox
    Meal_form.Controls("lstIngredient").RowSource = "Ingredient_listbox!" & IngListBox.ListObjects(1).DataBodyRange.Address
       
    'reset fields
    Meal_form.Controls("Ingredients").Value = ""
    Meal_form.Controls("Weight").Value = ""
    
    
End Sub

 

[ashley12]

I found the solution! It stop giving error when I unload the table from the listbox before adding new row. So, I've added this at the top in my AddIng sub: Meal_form.Controls("lstIngredient").RowSource = vbNullString
:D

 

[Michael M]

Glad to hear you solved the problem...Thanks for sharing the solution..