Userform Close causes Excel to Crash

[jlhoover3]

Hello,

I am creating a program that is considered a Speed Quoting system. I have everything working from Userforms to sheets, however, if there is a need to edit the quote, I want to bring the values back in to the Userforms. I have most of that functioning already, but the last little bit.

User will click a button in which will take them through a Module Macro to the Userform that it needs to based on cell value. Within the Userform_Initialize, I have a man for loops going and other modules being called. However, when I start to debug and fix my issues by closing the userform, excel crashes with no error produced. From everything I researched, it may have something to do with memory and the amount of variables that I'm using, but I can't get it resolved. Does anyone know what I can do to stop the crashing?

*I'm not sure if code is the most efficient, so don't harp on it to much

I_Edit_V2 (Module)

Public Series As Integer
Sub Return_Editable_Values()
Dim WS As Worksheet
Dim rng, cel As Range

    Set WS = Sheets("Entry")
    Set rng = WS.ListObjects("DataEntry_Table").ListColumns(1).DataBodyRange
    
    WS.Select
    For Each cel In rng
        If cel.Offset(0, 1).Value = Series Then GoTo NextCel
        Series = cel.Offset(0, 1).Value
        If cel.Offset(0, 2).Value = "Apparel" Then
            If cel.Offset(0, 5).Value = "SanMar" Or cel.Offset(0, 5).Value = "S&S Activewear" Then
                With New Z_frmGCIApparel_Edit2
                    .Show
                End With
            Else
                M_frmApparelInfo_Edit.Show
            End If
        End If
NextCel:
    Next cel
End Sub

Z_frmGCIApparel_Edit2 (Userform)

[/B]
Option Explicit
Const strPath As String = "C:\Temp\"
Dim m_Width As Long, m_Height As Long, R As Long, LR1 As Long, LR As Long
Dim picURL As String, DateURL As String
Dim tbCollection As Collection

Private Sub UserForm_Initialize()
Dim lRow As Long
Dim ctrl As MSForms.Control
Dim obj As clsTextBox_Edit
Dim i, j, z, Target_Value, Total_Count As Integer
Dim ProductID As Range
Dim WS, WS2, WS3 As Worksheet
Dim c As Range
Dim firstAddress As String
Dim CodeHasRun As Boolean


    Set WS = Sheets("Entry")
    Set WS2 = Sheets("Shirt Wizard")
    Set WS3 = Sheets("Dropdown Lists")
    i = 1
    
    With WS.Range("DataEntry_Table[Series]")
        Set c = .Find(What:=Series, _
                After:=.Cells(.Cells.count), _
                LookIn:=xlValues, _
                LookAt:=xlWhole, _
                SearchOrder:=xlByRows, _
                SearchDirection:=xlNext, _
                MatchCase:=False)
                
        If Not c Is Nothing Then
            firstAddress = c.Address
            c.Select
            Do
                If CodeHasRun = False Then
                    'Vendor
                    Me.cmb_Vendor.Value = c.Offset(0, 4).Value
                    WS2.Range("B1").Value = c.Offset(0, 4).Value
                    WS2.Calculate
                    lRow = WS2.Cells(Rows.count, 8).End(xlUp).Row
                    cmb_Vendor.List = WS2.Range("H2:H" & lRow).Value
                    
                    'Apparel SKU
                    Me.cmb_SKU.Value = c.Offset(0, 3).Value
                    WS2.Range("B2").Value = c.Offset(0, 3).Value
                    WS2.Calculate
            
                    'Print Type
                    Me.cmb_PrintType.Value = c.Offset(0, 9).Value
                    lRow = WS3.Cells(Rows.count, 23).End(xlUp).Row
                    cmb_PrintType.List = WS3.Range("W2:W" & lRow).Value
                    
                    'Sitch Count
                    Me.txt_StitchCount.Value = c.Offset(0, 12).Value
                    
                    'Digitized/Shirt Type
                    Me.cmb_Digitized.Text = c.Offset(0, 15).Value
                    If Me.cmb_PrintType.Value = "DTG" Then
                        lRow = WS3.Cells(Rows.count, 11).End(xlUp).Row
                        Me.Title_Digitized.Caption = "Shirt Type"
                        cmb_Digitized.List = WS3.Range("K2:K" & lRow).Value
                    Else
                        lRow = WS3.Cells(Rows.count, 4).End(xlUp).Row
                        Me.Title_Digitized.Caption = "Digitized"
                        cmb_Digitized.List = WS3.Range("D2:D" & lRow).Value
                    End If
                    
                    'Print Location
                    Me.cmb_PrintLocations.Text = c.Offset(0, 10).Value
                    lRow = WS3.Cells(Rows.count, 22).End(xlUp).Row
                    cmb_PrintLocations.List = WS3.Range("V2:V" & lRow).Value
                    
                    'Print Colors
                    Me.cmb_PrintColors.Text = c.Offset(0, 11).Value
                    lRow = WS3.Cells(Rows.count, 22).End(xlUp).Row
                    cmb_PrintColors.List = WS3.Range("V2:V" & lRow).Value
                    CodeHasRun = True
                End If

                'LABEL
                Z_frmGCIApparel_Edit2.Controls("Label" & i).Caption = c.Offset(0, -1).Value
            
                '*APPAREL COLOR*'
                Z_frmGCIApparel_Edit2.Controls("cmb_Color" & i).Visible = True
                Z_frmGCIApparel_Edit2.Controls("cmb_Color" & i).Value = c.Offset(0, 6).Value
                Sheets("Shirt Wizard").Range("B3").Value = c.Offset(0, 6).Value
            
                '*WEB BROWSER*'
                If Me.cmb_Vendor.Value = "S&S Activewear" Then
                    picURL = WS2.Range("B5").Value
                Else
                    picURL = WS2.Range("B6").Value
                End If
                fnCreateHTML (picURL)
                Z_frmGCIApparel_Edit2.Controls("WebBrowser" & i).Navigate strPath & "Tmp.html"
            
                For j = 1 To 10
                    '*APPAREL SIZE*' '*APPAREL COST*'
                    If WS2.Cells(j + 1, 5).Value <> "" Then
                        Z_frmGCIApparel_Edit2.Controls("lbl" & i & "_Size" & j).Visible = True
                        Z_frmGCIApparel_Edit2.Controls("lbl" & i & "_Size" & j).Caption = WS2.Cells(j + 1, 5).Value
                        Z_frmGCIApparel_Edit2.Controls("lbl" & i & "_Cost" & j).Visible = True
                        Z_frmGCIApparel_Edit2.Controls("lbl" & i & "_Cost" & j).Caption = Format(WS2.Cells(j + 1, 6).Value, "$#,##0.00")
                        Z_frmGCIApparel_Edit2.Controls("txt" & i & "_Qty" & j).Visible = True
                    Else
                        Z_frmGCIApparel_Edit2.Controls("lbl" & i & "_Size" & j).Visible = False
                        Z_frmGCIApparel_Edit2.Controls("lbl" & i & "_Cost" & j).Visible = False
                    End If
                Next j
            
                For j = 1 To 10
                    '*APPAREL QUANTITY*'
                    If c.Offset(0, 5).Value = Z_frmGCIApparel_Edit2.Controls("lbl" & i & "_Size" & j).Caption Then
                        Z_frmGCIApparel_Edit2.Controls("txt" & i & "_Qty" & j).Value = c.Offset(0, 7).Value
                        Exit For
                    Else
                        Z_frmGCIApparel_Edit2.Controls("txt" & i & "_Qty" & j).Value = ""
                    End If
                Next j
            
                '*TOTAL COST AND TOTAL QUANTITY*'
                Call UpdateQtyLabel_Edit
                Set c = .FindNext(c)
                i = i + 1
            Loop While Not c Is Nothing And c.Address <> firstAddress
        End If
    End With

    'All TextBoxes Run Macro
    Set tbCollection = New Collection
    For Each ctrl In Me.Controls
        If TypeOf ctrl Is MSForms.TextBox Then
            Set obj = New clsTextBox_Edit
            Set obj.Control = ctrl
            tbCollection.Add obj
        End If
    Next ctrl
    Set obj = Nothing
    
End Sub
[B]

H_UpdateQtyLabel_Edit (Module)

[/B]
Sub UpdateQtyLabel_Edit()
Dim Total_1, Total_2, Total_3, Total_4, Total_5, Total_6, Total_7, Total_8, Total_9, Total_10, GTotal As Double
Dim qtyTotal_1, qtyTotal_2, qtyTotal_3, qtyTotal_4, qtyTotal_5, qtyTotal_6, qtyTotal_7, qtyTotal_8, qtyTotal_9, qtyTotal_10, GqtyTotal As Double
Dim j, k As Integer
    
    For j = 1 To 8
        For k = 1 To 10
            qtyTotal = qtyTotal + Z_frmGCIApparel_Edit2.Controls("txt" & j & "_Qty" & k).Value
        Next k
    Next j
        If qtyTotal = "" Or qtyTotal = 0 Then Exit Sub

    'Update Pricing
    For j = 1 To 8
        Total = 0
        qtyTotal = 0
        For k = 1 To 10
            If Z_frmGCIApparel_Edit2.Controls("txt" & j & "_Qty" & k).Value <> "" And Z_frmGCIApparel_Edit2.Controls("txt" & j & "_Qty" & k).Value > 0 And Z_frmGCIApparel_Edit2.Controls("txt" & j & "_Qty" & k).Value < 999999 Then
                qtyTotal = qtyTotal + Z_frmGCIApparel_Edit2.Controls("txt" & j & "_Qty" & k).Value
                Total = Total + CDbl(Z_frmGCIApparel_Edit2.Controls("lbl" & j & "_Cost" & k).Caption) * CDbl(Z_frmGCIApparel_Edit2.Controls("txt" & j & "_Qty" & k).Value)
            End If
        Next k
        
        If qtyTotal > 0 Then
            Z_frmGCIApparel_Edit2.Controls("Title_TotalQty" & j).Visible = True
            Z_frmGCIApparel_Edit2.Controls("Title_TotalCost" & j).Visible = True
            Z_frmGCIApparel_Edit2.Controls("lbl" & j & "_TotalQty").Visible = True
            Z_frmGCIApparel_Edit2.Controls("lbl" & j & "_TotalCost").Visible = True
            Z_frmGCIApparel_Edit2.Controls("lbl" & j & "_TotalQty").Caption = qtyTotal
            Z_frmGCIApparel_Edit2.Controls("lbl" & j & "_TotalCost").Caption = Format(Total, "$#,##0.00")
        Else
            Z_frmGCIApparel_Edit2.Controls("lbl" & j & "_TotalQty").Caption = 0
            Z_frmGCIApparel_Edit2.Controls("lbl" & j & "_TotalCost").Caption = Format(0, "$#,##0.00")
        End If
        
        GqtyTotal = GqtyTotal + Z_frmGCIApparel_Edit2.Controls("lbl" & j & "_TotalQty")
        GTotal = GTotal + Z_frmGCIApparel_Edit2.Controls("lbl" & j & "_TotalCost")
    Next j
    
    'Set Totals
    If GqtyTotal > 0 Then
        Z_frmGCIApparel_Edit2.Controls("cmb_Complete").Visible = True
        Z_frmGCIApparel_Edit2.Controls("Title_TotalQty").Visible = True
        Z_frmGCIApparel_Edit2.Controls("Title_TotalCost").Visible = True
        Z_frmGCIApparel_Edit2.Controls("lbl_TotalQty").Visible = True
        Z_frmGCIApparel_Edit2.Controls("lbl_TotalCost").Visible = True
        Z_frmGCIApparel_Edit2.Controls("lbl_TotalQty").Caption = GqtyTotal
        Z_frmGCIApparel_Edit2.Controls("lbl_TotalCost").Caption = Format(GTotal, "$#,##0.00")
    Else
        Z_frmGCIApparel_Edit2.Controls("cmb_Complete").Visible = False
        Z_frmGCIApparel_Edit2.Controls("Title_TotalQty").Visible = False
        Z_frmGCIApparel_Edit2.Controls("Title_TotalCost").Visible = False
        Z_frmGCIApparel_Edit2.Controls("lbl_TotalQty").Visible = False
        Z_frmGCIApparel_Edit2.Controls("lbl_TotalCost").Visible = False
    End If
    
End Sub

[B]

 

[dmt32]

Hi,

As general observation, there is some inconsistences with your variable declarations

This is correct

Dim m_Width As Long, m_Height As Long, R As Long, LR1 As Long, LR As Long

But this

Dim WS, WS2, WS3 As Worksheet

Only the last variable is of the data type specified the others are all variants. When declaring variables even if on same line, you must explicitly declare data type for each.

UpdateQtyLabel_Edit procedure

Variables declared in same manner as mentioned but most are not used in the code.

You mention that you have Userforms in your project, presumably one to Add the data & the other to edit it? If this is case you can normally perform required functions from just one userform.

With a complex project it is more helpful to forum if you can place copy of your workbook with dummy data on a file sharing site like DropBox & provide a link to it.

Dave

 

[jlhoover3]

Dave,

I appreciate the response. I cleaned up all the info you have given me, and I've been able to get the Userform to close without crashing now. I had so many variables not being used because I was trying to figure out the best solution to not crashing. However, I didn't know you had to explicitly declare the data type even on the same line. That was very useful information, and made sure I changed all modules and userforms to reflect their data types. Again, thank you so much for the help! I thought I was going insane for a second haha!

Jacob

 

[dmt32]

Pleased guidance assisted you always though, worth searching for such information in the helpfiles :Declaring variables (VBA)

Glad managed to resolve your issue & appreciate feedback

Dave