Calculations for ages

[SLinas]

Hey Guys,

I have kinda complicated UserForm and tried to do some tricks with one extra button, but as soon as I have created it and try to press it takes me ages it keeps saying "Calculating" for like one minute before transfering any data to the sheets. Maybe you guys have any idea which of the code lines makes this happen... Thanks in advance!

Private Sub CommandButton1_Click()

Dim emptyRow As Long




'Activates barcode sheet
Worksheets("barcode").Activate


'where the data will be transfered
emptyRow = WorksheetFunction.CountA(Range("B:B")) + 1


If clicked = True Then
GoTo JustPrint


End If


'Data transfer


Cells(emptyRow, 2).Value = DateTextBox.Value
Cells(emptyRow, 3).Value = ClientTextBox.Value
Cells(emptyRow, 4).Value = ComboBox1.Value
Cells(emptyRow, 5).Value = TextBox1.Value


Application.ScreenUpdating = False
Dim LastRow As Long
LastRow = Cells(Rows.Count, "E").End(xlUp).Row
Range("A" & LastRow).Copy Destination:=Range("F1")
Range("A" & LastRow).Copy Destination:=Sheets("Stock").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
Application.ScreenUpdating = True


With Sheets("barcodeprint")


.Range("A1").Value = ClientTextBox.Text
.Range("C4").Value = DateTextBox.Text
.Range("C5").Value = ComboBox1.Value
.Range("C6").Value = TextBox1.Value


'Print
JustPrint:
Sheets("barcodeprint").PrintOut


clicked = True


End With
    
End Sub

 

[JazzSP8]

You asking Excel to Calculate when you're using CountA to get the emptyRow, if it's taking a while then maybe it's because there is a lot of data there?

I made a few changes to your code, I changed the way you find the last row to a VBA function and switched off Calculation see if this helps any?

Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Dim emptyRow As Long

'Activates barcode sheet
Worksheets("barcode").Activate

'where the data will be transfered
emptyRow = Range("A" & Rows.Count).End(xlUp).Row + 1

If clicked = True Then GoTo JustPrint

'Data transfer
Cells(emptyRow, 2).Value = DateTextBox.Value
Cells(emptyRow, 3).Value = ClientTextBox.Value
Cells(emptyRow, 4).Value = ComboBox1.Value
Cells(emptyRow, 5).Value = TextBox1.Value

Dim LastRow As Long
LastRow = Cells(Rows.Count, "E").End(xlUp).Row
Range("A" & LastRow).Copy Destination:=Range("F1")
Range("A" & LastRow).Copy Destination:=Sheets("Stock").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)

With Sheets("barcodeprint")
    .Range("A1").Value = ClientTextBox.Text
    .Range("C4").Value = DateTextBox.Text
    .Range("C5").Value = ComboBox1.Value
    .Range("C6").Value = TextBox1.Value
End With

'Print
JustPrint:
Sheets("barcodeprint").PrintOut
clicked = True

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
    
End Sub

 

[SLinas]

You asking Excel to Calculate when you're using CountA to get the emptyRow, if it's taking a while then maybe it's because there is a lot of data there?

I made a few changes to your code, I changed the way you find the last row to a VBA function and switched off Calculation see if this helps any?

Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Dim emptyRow As Long

'Activates barcode sheet
Worksheets("barcode").Activate

'where the data will be transfered
emptyRow = Range("A" & Rows.Count).End(xlUp).Row + 1

If clicked = True Then GoTo JustPrint

'Data transfer
Cells(emptyRow, 2).Value = DateTextBox.Value
Cells(emptyRow, 3).Value = ClientTextBox.Value
Cells(emptyRow, 4).Value = ComboBox1.Value
Cells(emptyRow, 5).Value = TextBox1.Value

Dim LastRow As Long
LastRow = Cells(Rows.Count, "E").End(xlUp).Row
Range("A" & LastRow).Copy Destination:=Range("F1")
Range("A" & LastRow).Copy Destination:=Sheets("Stock").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)

With Sheets("barcodeprint")
    .Range("A1").Value = ClientTextBox.Text
    .Range("C4").Value = DateTextBox.Text
    .Range("C5").Value = ComboBox1.Value
    .Range("C6").Value = TextBox1.Value
End With

'Print
JustPrint:
Sheets("barcodeprint").PrintOut
clicked = True

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
    
End Sub

Thank you very much, Manual calculation sorted out everything. Thanks again, saved my day!

[COLOR=#333333]Application.Calculation = xlCalculationAutomatic[/COLOR]