Can't find my run time error

ashley12

New Member
Joined
Dec 6, 2020
Messages
43
Office Version
  1. 365
Platform
  1. Windows
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:

VBA Code:
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:

VBA Code:
Private Sub cmdDeleteIng_Click()
       
    Dim IngRow As Long
    Dim ListBoxRow As Integer

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

End Sub



1619997205859.png



1619998012102.png
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
21,104
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Does it happen when you are down to 1 row in the list box ?
Have you tried removing the +1 from this row
VBA Code:
ListBoxRow = lstIngredient.ListIndex + 1
 

ashley12

New Member
Joined
Dec 6, 2020
Messages
43
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Oct 27, 2005
Messages
21,104
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Rich (BB code):
Do you think it's something beyond the VBA codes?
I doubt it.
Try inserting On error as below
Rich (BB code):
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

New Member
Joined
Dec 6, 2020
Messages
43
Office Version
  1. 365
Platform
  1. Windows
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
 
Solution

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
21,104
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Glad to hear you solved the problem...Thanks for sharing the solution.. (y) (y)
 

Forum statistics

Threads
1,148,147
Messages
5,745,055
Members
423,917
Latest member
Frank1931

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top