UserForm is very slow to write data

[skoorBmaS]

I've currently made a user form which is used to add data into a data sheet. This was working fine up until recently when it has started taking 15 seconds to write data after the user presses save. I presume it is related to the VBA code running in an inefficient manner. The code is as follows:

Private Sub cmdSave_Click()

    'Click Ok
    Dim lRow As Long
    
    'Error Text Box'
If Me.cmbSite = "" Then
 MsgBox "Please enter a Site Name.", vbExclamation, "Staff Hours"
 Me.cmbSite.SetFocus
 End If
 If Me.cmbName = "" Then
 MsgBox "Please enter an Employee Name.", vbExclamation, "Staff Hours"
 Me.cmbName.SetFocus
 End If
If Me.cmbTimeIn = "" Then
 MsgBox "Please enter a Time In Value.", vbExclamation, "Staff Hours"
 Me.cmbTimeIn.SetFocus
 End If
If Me.cmbTimeOut = "" Then
 MsgBox "Please enter a Time Out Value.", vbExclamation, "Staff Hours"
 Me.cmbTimeOut.SetFocus
    End If
    
    Dim ws As Worksheet
    Set ws = Worksheets("Data")
    lRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    With ws
        .Cells(lRow, 1).Value = Me.txtDate.Value
        .Cells(lRow, 3).Value = Me.cmbSite.Value
        .Cells(lRow, 4).Value = Me.cmbName.Value
        .Cells(lRow, 5).Value = Me.cmbTimeIn.Value
        .Cells(lRow, 6).Value = Me.cmbTimeOut.Value
        .Cells(lRow, 8).Value = Me.cmbTransport.Value
        .Cells(lRow, 9).Value = Me.cmbMethod.Value
        .Cells(lRow, 10).Value = Me.txtTravelTime.Value
        
    End With
    
 'Clear input controls.
    Me.txtDate.Value = ""
    Me.cmbSite.Value = ""
    Me.cmbName.Value = ""
    Me.cmbTimeIn.Value = ""
    Me.cmbTimeOut.Value = ""
    Me.cmbTransport.Value = ""
    Me.cmbMethod.Value = ""
    Me.txtTravelTime.Value = ""


    
    End Sub

 

[Yongle]

Try adding this as the first line of your code

Application.ScreenUpdating = False: Application.Calculation = xlCalculationManual

and this as the last line

Application.ScreenUpdating = True: Application.Calculation = xlCalculationAutomatic

Does it speed things up?

 

[Fluff]

I can see no particular reason, for it to take that long.
Do you have any event code running on the Data sheet?
Also do you have a lot of formulae on that sheet?

 

[skoorBmaS]

Hi Guys.
@Yongle, that did help quite a bit however there still seems to be a couple of seconds of delay still.
@Fluff, I dont have any event code running on the data sheet. do have 5 rows of formulas in that sheet for the 14 rows of data which I have. One of the formulas is quite long mind you, as below:

=MAX(FREQUENCY(IF(ISNUMBER(MATCH(ROW(INDIRECT(MIN($A$3:$A$5001)&":"&MAX($A$3:$A$5001)))&O3,$A$3:$A$501&$D$3:$D$5001,0)),ROW(INDIRECT(MIN($A$3:$A$5001)&":"&MAX($A$3:$A$5001)))),IF(ISERROR(MATCH(ROW(INDIRECT(MIN($A$3:$A$5001)-1&":"&MAX($A$3:$A$5001)+1))&O3,$A$3:$A$5001&$D$3:$D$5001,0)),ROW(INDIRECT(MIN($A$3:$A$5001)-1&":"&MAX($A$3:$A$5001)+1)))))

 

[Fluff]

In that case, add the 1st line below to the top of your code & the 2nd line at the end

Application.Calculation = xlCalculationManual
Application.Calculation = xlCalculationAutomatic

 

[skoorBmaS]

In that case, add the 1st line below to the top of your code & the 2nd line at the end

Application.Calculation = xlCalculationManual
Application.Calculation = xlCalculationAutomatic

Not much difference unfortunately. I have run the userform whilst also viewing the data tab and I can see that once I press save, the data is inputted into the cells immediately, however there seems to be a delay between that and the UserForm text boxes being cleared.

 

[Yongle]

1. Big formulas are not my strength (and I cannot decipher yours) .. but..
- you say that you have 14 rows of data and yet your formula is catering for almost 5000 rows
- there appears to be an inconsistency in one of the ranges (see red text) - should it be row 501 when all the other ranges end in row 5001?

=MAX(FREQUENCY(IF(ISNUMBER(MATCH(ROW(INDIRECT(MIN($A$3:$A$5001)&":"&MAX($A$3:$A$5001)))&O3,$A$3:$A$501&$D$3:$D$5001,0)),ROW(INDIRECT(MIN($A$3:$A$5001)&":"&MAX($A$3:$A$5001)))),IF(ISERROR(MATCH(ROW(INDIRECT(MIN($A$3:$A$5001)-1&":"&MAX($A$3:$A$5001)+1))&O3,$A$3:$A$5001&$D$3:$D$5001,0)),ROW(INDIRECT(MIN($A$3:$A$5001)-1&":"&MAX($A$3:$A$5001)+1)))))

2. You workbook is taking a long time to calculate - that is your issue (not the userform code)
- how many sheets are there?
- how many rows in each sheet?
- do other sheets contain many formula?

 

[skoorBmaS]

1. Thank you so much, I hadn't noticed that but yes you are correct!

2. No problem. I have 4 sheets:

• Dashboard - This has 4 tables, all with 30 rows and 4 columns which have VLOOKUP formulas in them.
• Pivots - 3 pivot tables are in this sheet
• Data - Main data tab as mentioned above
• Ref - Sheet with 5 columns of references for populating the data in the userform etc.

It seems strange how it writes the data fine but then seems to stall afterwards it appears. I don't believe that the file size is that large to where it is the reason for the issue. Unless it's just my work PC being slow lol

 

[skoorBmaS]

@Yongle, I have just been playing around with the file and now it is back to being quick again. The mysteries of excel! Thank you for all the help