Spot the Stack Space Error! (Runtime Error 28)

[Tarkemelion]

Hi All,

It appears my code has a stack space error. I'm not entirely certain but I don't think I have an infinite call loop when the code executes. It appears the bug is subtler. Anyways, feel free to point out the issue and whether or not I'm wrong!!!

I've commented where the crash occurs to help hone in on the issue.

'UserForm Rev03

'Places UserForm in the middle of the screen
Private Sub UserForm_Activate()

Dim AppXCenter, AppYCenter As Long

    AppXCenter = Application.Left + (Application.Width / 2)
    AppYCenter = Application.Top + (Application.Height / 2)
    
    With Me
        .StartUpPosition = 0
        .Top = AppYCenter - (Me.Height / 2)
        .Left = AppXCenter - (Me.Width / 2)
    End With
    
End Sub

Private Sub UserForm_Initialize()

'Set dimensions
    With Add_New_Costing
        .Width = 300
        .Height = 225
    End With

'Disable Previous initially
    cmdPrevious.Enabled = False
        
'Empty Description
    TextBoxDescription.Value = ""
    
'Empty Docket Number
    TextBoxDocket.Value = ""
    
'Empty Date
    TextBoxDate.Value = ""
    
'Empty Rate
    TextBoxRate.Value = ""
    
'Empty Quantity
    TextBoxQuantity.Value = ""
    
'Empty Unit
    ComboBoxUnit.RowSource = "Table2[Unit]"
    
'Empty Addition
    TextBoxAddition.Value = ""
       
'List Box 1 Populated
    ComboBoxCompany.RowSource = "Table2[Company]"
    
'List Box 2 Populated
    ComboBoxShift.List = Array("Day", "Night")
    
'List Box 3 Populated
    ComboBoxCode.RowSource = "Table3[Code]"
    
End Sub

Private Sub cmdNext_Click()

'Go to Next MultiPage
    MultiPage1.Value = MultiPage1.Value + 1

End Sub


Private Sub cmdPrevious_Click()

'Go to Previous MultiPage
    Dim intPage As Integer
    intPage = MultiPage1.Value
    Do
        intPage = intPage - 1
        If intPage = 0 Then Exit Do
    Loop While Not MultiPage1.Pages(intPage).Enabled
    MultiPage1.Value = intPage
    
End Sub

Private Sub MultiPage1_Change()
     
'Enable or Disable cmdButtons if at beginning or end of Multipages
    If MultiPage1.Value = 0 Then
        cmdNext.Enabled = True
        cmdPrevious.Enabled = False
    ElseIf MultiPage1.Value = (MultiPage1.Pages.Count - 1) Then
        cmdNext.Enabled = False
        cmdPrevious.Enabled = True
    Else
        cmdNext.Enabled = True
        cmdPrevious.Enabled = True
    End If
     
End Sub

Private Sub cmdCancel_Click()

'Closes the User Form
    Unload Me
    
End Sub

Private Sub TextBoxRate_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

'Data Validation for TextBoxRate

    'Only allows: . (fullstop);                numbers;                        Upper Case;                             lower case
    If (KeyAscii = 46) Or (KeyAscii > 47 And KeyAscii < 58) Then 'Or (KeyAscii > 64 And KeyAscii < 91) Or (KeyAscii > 96 And KeyAscii < 123) Then
    KeyAscii = KeyAscii
    
Else
    
    'Otherwise, do not allow other characters
    KeyAscii = 0
    
    End If

End Sub

Private Sub TextBoxQuantity_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

'Data Validation for TextBoxQuantity

    'Only allows: . (fullstop);                numbers;
    If (KeyAscii = 46) Or (KeyAscii > 47 And KeyAscii < 58) Then
    KeyAscii = KeyAscii
    
Else
    
    'Otherwise, do not allow other characters
    KeyAscii = 0
    
    End If

End Sub

Private Sub TextBoxAddition_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

'Data Validation for TextBoxAddition

    'Only allows: . (fullstop);                numbers;
    If (KeyAscii = 46) Or (KeyAscii > 47 And KeyAscii < 58) Then
    KeyAscii = KeyAscii
    
Else
    
    'Otherwise, do not allow other characters
    KeyAscii = 0
    
    End If

End Sub

Private Sub ComboBoxCode_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

'Data Validation for ComboBoxCode

    'Only allows: 'numbers;
    If (KeyAscii > 47 And KeyAscii < 58) Then
    KeyAscii = KeyAscii
    
Else
    
    'Otherwise, do not allow other characters
    KeyAscii = 0
    
    End If

End Sub

Private Sub cmdAdd_Click()

'Defines the variables
    Dim Company_Name As String
    Dim Description As String
    Dim Docket_Number As String
    
    Dim Year As String
    Dim Month As String
    Dim Day As String
    Dim Shift As String
    
    Dim Code As String
    Dim Rate As String
    Dim Quantity As String
    Dim Unit As String
    Dim Addition As String
    
    Dim ws As Worksheet

'Sets variables to UserForm values
    Company_Name = ComboBoxCompany.Text
    Description = TextBoxDescription.Text
    Docket_Number = TextBoxDocket.Text
    
    'Year = TextBox1.Text
    'Month = TextBox2.Text
    'Day = TextBox3.Text
    Shift = ComboBoxShift.Text
    
    Code = ComboBoxCode.Text
    Rate = TextBoxRate.Text
    Quantity = TextBoxQuantity.Text
    Unit = ComboBoxUnit.Text
    Addition = TextBoxAddition.Text

Set ws = ThisWorkbook.Worksheets("Cost Detail")

Set tbl = ws.ListObjects("Table2")

'Test the EnableEvents
Application.EnableEvents = False

'Add row
    Dim lRow As ListRow
    Set lRow = tbl.ListRows.Add      ' <---- This is when the code crashes after clicking the cmdAdd button
    
    With lRow
        '.Range(1) = Year
        '.Range(2) = Month
        '.Range(3) = Day
        .Range(4) = Shift
        .Range(5) = Company_Name
        .Range(6) = Description
        .Range(7) = Docket_Number
        .Range(8) = Code
        .Range(9) = Rate
        .Range(10) = Quantity
        .Range(11) = Unit
        .Range(12) = Addition
    End With
    
'Test the EnableEvents
Application.EnableEvents = True

'Closes UserForm
    Unload Me
    
End Sub

Best Regards and Happy Hunting

 

[Colo]

Hi,
Does a table named "Table2" exist on the worksheet "Cost Detail"?
Sometimes, the name was changed by remaking, so just wanna make sure about that.

 

[Georgiboy]

Worth defining the table also:

Dim tbl As ListObject

 

[Tarkemelion]

Hi,
Does a table named "Table2" exist on the worksheet "Cost Detail"?
Sometimes, the name was changed by remaking, so just wanna make sure about that.

Yeah, I double checked anything that was referenced. "Table2" exists and is the right table!

 

[Tarkemelion]

Worth defining the table also:

Dim tbl As ListObject

tbl has been defined!

...the bug still persists...

Some additional information; the first time I ran the code it added the new row in "Table2". Subsequent attempts have resulted in crashes. ""Method "Add" of "Objects" failed"" prompts (rather than the Stack Space Error when I step through the code).

 

[Georgiboy]

It is difficult to test without the workbook but looking through the whole code i think it may have something to do with you setting the RowSource to Table2 in the initialize sub.
When you add a row at a later point with the userform still loaded it causes a crash as the Table2 size has changed.

Try testing the code with the RowSources commented out in the initialize sub and see if it behaves in the same way.

 

[Tarkemelion]

It is difficult to test without the workbook but looking through the whole code i think it may have something to do with you setting the RowSource to Table2 in the initialize sub.
When you add a row at a later point with the userform still loaded it causes a crash as the Table2 size has changed.

Try testing the code with the RowSources commented out in the initialize sub and see if it behaves in the same way.

Absolute legend and great pick-up!!!

I will have to see if I can create a work around where those boxes grab the source data and then no longer attempt to update. Many thanks once again.