UserForm is very slow to write data

skoorBmaS

New Member
Joined
Feb 5, 2016
Messages
34
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:

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

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Try adding this as the first line of your code
Code:
Application.ScreenUpdating = False: Application.Calculation = xlCalculationManual

and this as the last line
Code:
Application.ScreenUpdating = True: Application.Calculation = xlCalculationAutomatic

Does it speed things up?
 
Upvote 0
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?
 
Upvote 0
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:

Code:
=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)))))
 
Upvote 0
In that case, add the 1st line below to the top of your code & the 2nd line at the end
Code:
Application.Calculation = xlCalculationManual
Application.Calculation = xlCalculationAutomatic
 
Last edited:
Upvote 0
In that case, add the 1st line below to the top of your code & the 2nd line at the end
Code:
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.
 
Upvote 0
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?
 
Last edited:
Upvote 0
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
 
Upvote 0
@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 :)
 
Upvote 0

Forum statistics

Threads
1,213,581
Messages
6,114,440
Members
448,573
Latest member
BEDE

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