My UserForm is really slow to post

[chenyu768]

I'm making multiple userforms on one Workbook. each userform is used to enter data on a different sheet (different inputs and different columns for different sheets)

It used to enter data almost instantaneously however now it's taking about 10 seconds after i press the enter button.

I've pasted my entire Userform code below but the data entry code is the last Sub called EnterButton_Click

What am i not seeing?

Private Sub AssigneeBox_Change()
TextBox2.Value = Format(WorksheetFunction.VLookup(AssigneeBox.Value, Worksheets("CustomerList").Range("A2:C245"), 3, False), "####0000")


End Sub


Private Sub AssignorBox_Change()
TextBox1.Value = Format(WorksheetFunction.VLookup(AssignorBox.Value, Worksheets("CustomerList").Range("A2:C245"), 3, False), "####0000")
End Sub




Private Sub UserForm_Initialize()


AssignorBox.RowSource = "Customer"
AssigneeBox.RowSource = "Customer"
RecByBox.RowSource = "UserID"
With RecMethodBox
.AddItem "Phone"
.AddItem "Email"
.AddItem "Fax"
'MktrID = WorksheetFunction.VLookup(CustBox.Value, Worksheets("CustomerSheet").Range("Customers"), 3, False)
End With
TextBox2.Locked = True
TextBox2.BackColor = &H80000000
TextBox1.Locked = True
TextBox1.BackColor = &H80000000


End Sub




Private Sub CancelButton_Click()
Unload Me
End Sub








Private Sub EnterButton_Click()




Dim LastRow As Long, ws As Worksheet


    Set ws = Sheets("Assignments")


    LastRow = ws.Range("D" & Rows.Count).End(xlUp).Row + 1 'Finds the last blank row


 
    
    ws.Range("C" & LastRow).Value = AssignorBox.Text
    
    ws.Range("D" & LastRow).Value = WorksheetFunction.VLookup(AssignorBox.Value, Worksheets("CustomerList").Range("A2:C245"), 3, False)


    ws.Range("E" & LastRow).Value = AssigneeBox.Text
    
    ws.Range("F" & LastRow).Value = WorksheetFunction.VLookup(AssigneeBox.Value, Worksheets("CustomerList").Range("A2:C245"), 3, False)
    
    ws.Range("B" & LastRow).Value = RecByBox.Text
      
    ws.Range("A" & LastRow).Value = Format(Now(), "MM/DD/YY")
    
    ws.Range("G" & LastRow).Value = RecMethodBox.Text
    
    ws.Range("J" & LastRow).Value = CommentBox.Text
    
    
    
    
    
    
Unload Me
End Sub

 

[JoeMo]

Have you tried turning off screen updating and setting calculation to manual during the posting macro. The latter may be especially important if you have recently added formulas to your workbook or if you have other workbooks open that take a long time to calculate.

 

[chenyu768]

Holy crap, thee manual calculations thing worked. its like night and day.

is there a way i can still get auto calculations to work? i have a summary sheet where i calculate metrics. If not i can put in a calculate button.

 

[Mychyl]

In my (admittedly limited, compared to some members) experience, slowdown like you're describing usually begins rearing its ugly head when there's too many formulas trying to run at once, especially when trying to pull from an external workbook. (I've encountered the same thing, for example, with a workbook that was trying to pull from a secondary workbook on SharePoint.) My solution - which might or might not work in your individual case - was setting up the formulas to only pull what you're currently working with. (In my case, I added a contextual filter to limit the number of rows calculating at once by setting up my data sheet to read what was selected by the user on a dropdown, so it's only processing that row at any given moment.)

In this case, if you're using a summary sheet, especially if it's across a large number of fields or processing a large amount of data, this solution may not work for you, but I hope it at least works as a starting point for your search.

 

[chenyu768]

There's no external source and the summary sheet is just a count function. But i did add application.calculate to the end of my code and while it does take about half a second to do the entry it does work. my team can live with half a second or they can go figure this out on their own lol.

In any case you've helped a lot good sir. thanks a million