Userform Close causes Excel to Crash

jlhoover3

jlhoover3

Board Regular
Joined
Nov 9, 2015
Messages
60
Office Version
  1. 365
Platform
  1. Windows
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)
VBA Code: 
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)
VBA Code: 
[/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)
VBA Code: 
[/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]
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Sort by date Sort by votes
Hi,

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

This is correct

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

But this

Code: 
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
 
Upvote 0
Solution
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
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Copy Textbox values from UserForm to the worksheet
Replies
6
Views
278
AlphaFrog
AlphaFrog
N
  • Solved
Frame scrollbar resets when clicking on/in frame
Replies
5
Views
313
Micron
M
H
  • Question Question
Advanced Multi VBA - Creating multiple temporary files and attach them to one email on outlook
Replies
2
Views
240
Hayf13
H
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
1K
AndyEd
A
L
  • Solved
Input multiple ID and QTY in Excel VBA userform
Replies
7
Views
465
Legacy 143009
L

Forum statistics

Threads
1,223,098
Messages
6,170,103
Members
452,302
Latest member
TaMere

Share this page

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
Back
Top