Userform Loading Time

[AndrewGKenneth]

Hi there,

I have created a userform but when adding data to my spreadsheet it is very slow and takes around 30 seconds to input data from the userform to the spreadsheet. Here is my code for my add button, is there anything wrong with my code that is causing it to be slow? Any help would be much appreciated.

Private Sub CommandButton_Add_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")


'Check for Customer Number
If Trim(Me.TextBox_LotNumber.Value) = "" Or Me.TextBox_JobNumber.Value = "" Or Me.TextBox_FG.Value = "" Then
Me.TextBox_LotNumber.SetFocus
MsgBox "Please complete all fields"
Exit Sub
End If


'Find first empty row in database
EmptyRow = WorksheetFunction.CountA(Range("B:B")) + 1


Cells(EmptyRow, 1).Value = TextBox_JobNumber.Value
Cells(EmptyRow, 2).Value = TextBox_Start.Value
Cells(EmptyRow, 4).Value = TextBox_End.Value
Cells(EmptyRow, 6).Value = TextBox_LotNumber.Value
Cells(EmptyRow, 7).Value = TextBox_ProductNumber.Value
Cells(EmptyRow, 8).Value = TextBox_PartName.Value
Cells(EmptyRow, 9).Value = TextBox_DrawingNumber.Value
Cells(EmptyRow, 10).Value = TextBox_Customer.Value
Cells(EmptyRow, 11).Value = TextBox_Order.Value
Cells(EmptyRow, 12).Value = TextBox_FG.Value
Cells(EmptyRow, 13).Value = TextBox_NG.Value
Cells(EmptyRow, 14).Value = TextBox_MAT_NG.Value


TextBox_Start.Value = ""
TextBox_End.Value = ""
TextBox_LotNumber.Value = ""
TextBox_ProductNumber.Value = ""
TextBox_PartName.Value = ""
TextBox_DrawingNumber.Value = ""
TextBox_Customer.Value = ""
TextBox_Order.Value = ""
TextBox_FG.Value = ""
TextBox_NG.Value = ""
TextBox_MAT_NG.Value = ""


MsgBox "Data Added", vbOKOnly + vbInformation, "Data Added"
'clear the data
TextBox_JobNumber.Value = ""
TextBox_Start.Value = ""
TextBox_End.Value = ""
TextBox_LotNumber.Value = ""
TextBox_ProductNumber.Value = ""
TextBox_PartName.Value = ""
TextBox_DrawingNumber.Value = ""
TextBox_Customer.Value = ""
TextBox_Order.Value = ""
TextBox_FG.Value = ""
TextBox_NG.Value = ""
TextBox_MAT_NG.Value = ""
End Sub

Thanks,
Andrew

 

[RoryA]

Is there a lot of calculation going on?

 

[AndrewGKenneth]

Hi Rory,

Yes there is in the actual excel form, there are some sumif formulas as well as some auto filtering. But other than that nothing too huge. The main thing I wanted to check was my VBA coding if it is right, as I am very new to VBA. Taught myself in the last few weeks.

Thanks,
Andrew

 

[RoryA]

The code looks fine (other than the fact you seem to clear the same textboxes twice) - you could disable calculation and screenupdating and then turn them back on after making the changes but I suspect the recalculation time would be much the same. If you manually enter new data, without the form, is the calculation time about the same?

 

[My Aswer Is This]

If you want to clear all the Textbox's in your Userform you can use this script.

Private Sub CommandButton2_Click()
    'Modified  8/14/2018  3:21:42 AM  EDT
        
        For Each xcontrol In Me.Controls
            If TypeName(xcontrol) = "TextBox" Then xcontrol.Value = ""
    Next xcontrol
End Sub

 

[dmt32]

Hi,
See if this update to your code helps

Option Base 1
Private Sub CommandButton_Add_Click()
    Dim iRow As Long
    Dim i As Integer
    Dim ws As Worksheet
    Dim DataEntry() As Variant, ControlsArr As Variant
    
    Set ws = Worksheets("Sheet1")
              
    ControlsArr = Array("TextBox_JobNumber", "TextBox_Start", "TextBox_End", _
                        "TextBox_LotNumber", "TextBox_ProductNumber", "TextBox_PartName", _
                        "TextBox_DrawingNumber", "TextBox_Customer", "TextBox_Order", _
                        "TextBox_FG", "TextBox_NG", "TextBox_MAT_NG")
    
    ReDim DataEntry(1 To UBound(ControlsArr))
    
    For i = 1 To UBound(ControlsArr)
        With Me.Controls(ControlsArr(i))
            Select Case i
            Case 1, 4, 10
'Check required fields
                If Len(.Text) = 0 Then MsgBox "Please complete required field.", 48, "Required Entry": .SetFocus: Exit Sub
            End Select
'add data to array
            DataEntry(i) = .Text
        End With
        Next i
        
        On Error GoTo myerror
'turn events & calculation off
        With Application
            .EnableEvents = False: .Calculation = xlCalculationManual
        End With
        
'Find first empty row in database
        EmptyRow = WorksheetFunction.CountA(ws.Range("B:B")) + 1
'write data to worksheet
        ws.Cells(EmptyRow, 1).Resize(, UBound(DataEntry)).Value = DataEntry


'clear the data
        For i = 1 To UBound(ControlsArr)
            Me.Controls(ControlsArr(i)).Value = ""
        Next i
'inform user
        MsgBox "Data Added", 64, "Data Added"
            
myerror:
        With Application
                .EnableEvents = True: .Calculation = xlCalculationAutomatic
        End With
'report errors
        If Err <> 0 Then MsgBox (Error(Err)), 48, "Error"
End Sub

Code passes textbox values to an array which writes once to your worksheet which may overcome possible re-calculation issue already suggested.

Note Option Base 1 statement at Top of Code - this MUST sit at very TOP of your forms code page OUTSIDE any procedure

Hope Helpful

Dave