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
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,788
Office Version
  1. 365
Platform
  1. Windows
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?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,655
Office Version
  1. 365
Platform
  1. Windows
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

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,655
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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:

skoorBmaS

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

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,788
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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:

skoorBmaS

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

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

Watch MrExcel Video

Forum statistics

Threads
1,109,530
Messages
5,529,385
Members
409,869
Latest member
snake93
Top