Spot the Stack Space Error! (Runtime Error 28)

Tarkemelion

New Member
Joined
Jun 28, 2022
Messages
12
Office Version
  1. 365
Platform
  1. Windows
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.

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

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Colo

MrExcel MVP,
Joined
Mar 20, 2002
Messages
1,659
Office Version
  1. 2016
  2. 2003 or older
Platform
  1. Windows
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.
 

Tarkemelion

New Member
Joined
Jun 28, 2022
Messages
12
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jun 28, 2022
Messages
12
Office Version
  1. 365
Platform
  1. Windows
Worth defining the table also:
VBA Code:
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

Active Member
Joined
Nov 7, 2008
Messages
358
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.
 
Solution

Tarkemelion

New Member
Joined
Jun 28, 2022
Messages
12
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,182,202
Messages
5,934,245
Members
436,938
Latest member
Mthalaj

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