UserForm Malfunctioning after 70 plus calls

[StreetSwan]

Good Afternoon all!
I am a noob to vba and have been working on a project for work. It's basically an inspection form for our machined parts.
We measure the part with the caliper, the measurement goes into the userfrom and then gets transferred to the next empty cell in the corresponding column. It is a back and forth between the thickness and the width of the part. The code works great up until i get to 70 or so measurements in, then i get a run time error method range of object worksheet failed. I have tried everything i can think of and so far nothing has worked. Can someone please help me understand this?
I have the following code to execute:

Private Sub Enter_Click()
'Finds next empty cell and places the value in it
eRow = Measurements.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Row
Cells(eRow, 2).Value = RadialTB.Value
'Adds beep to notify inspector
Beep
'Acitvates the cell so the it will page down as necessary
Cells(eRow, 2).Activate
'Allows switching back and forth between stagnant and alternating
If StagnantCB.Value = True Then
RadialTB.Value = ""
RadialTB.SetFocus
Else
Unload Me
GrindEnter.Show (this is where the code is breaking)
End If
End Sub

 

[Gokhan Aycan]

Try this.
1. Step through the code with F8. If that doesn't give you a clue then
2. In VBA Editor, open Tools-Options. Open the General tab and in "Error Trapping" select "Break On All Errors". See where it would take you. Don't forget to re-select "Break on Unhandled Errors" when you are done.

Shift+F8 would step through calls to other subs in 1 step, instead of stepping all lines inside that sub.

 

[StreetSwan]

Try this.
1. Step through the code with F8. If that doesn't give you a clue then
2. In VBA Editor, open Tools-Options. Open the General tab and in "Error Trapping" select "Break On All Errors". See where it would take you. Don't forget to re-select "Break on Unhandled Errors" when you are done.

Shift+F8 would step through calls to other subs in 1 step, instead of stepping all lines inside that sub.

Hi thanks for the quick reply! I Turned on break on all errors, which showed me a break at the userform initialize code. To test that issue I commented out the code on both userforms. I ran the program again and about 70-75 entries in I got a stack overflow error at the grindenter.show step.

 

[Gokhan Aycan]

It may be that your code is recursive, as in one sub calls the other, and the other call first one. This results in a neverending execution, filling the call stack with each call. Call stack in VBA iirc is 1MB in size. In your case it takes 70 calls to fill it. You have to device a different approach to break this recursion.

 

[Gokhan Aycan]

You can try instead to show the next userform with application.run (not sure it would work) or application.ontime coupled with DoEvents. This would ensure the calling sub actually continue executing and finish. Not at home atm to test.

Edit: You cant app.ontime to a sub on a userform but you can have a simple proxy sub that show the userform.

 

[StreetSwan]

It may be that your code is recursive, as in one sub calls the other, and the other call first one. This results in a neverending execution, filling the call stack with each call. Call stack in VBA iirc is 1MB in size. In your case it takes 70 calls to fill it. You have to device a different approach to break this recursion.

This would explain why it happens roughly at the same time every time. I suppose I will have to come up with some other way to approach this. Thanks

 

[StreetSwan]

You can try instead to show the next userform with application.run (not sure it would work) or application.ontime coupled with DoEvents. This would ensure the calling sub actually continue executing and finish. Not at home atm to test.

Edit: You cant app.ontime to a sub on a userform but you can have a simple proxy sub that show the userform.

Are you saying to create sub "x" that calls the userform, then call sub "x" from my code? Sorry if that doesn't make any sense, but I do not know what a proxy sub is...

 

[Gokhan Aycan]

Just a normal sub on a normal module with only a userform.show inside.