Macro VBA

[Abusalma02]

Hello! Please what can i do to make my macro VBA working fast i have about 6 to 7 pages of marco (Userform up to 7) on my workbook and it took a long time before it open. But for the first, send and third page of my sheet, the userform on them were working as expected, but for the fourth page their is about three userforms which the first one for it to post data on to worksheet it is so annoying. so Please if there is anything to do to aid the workbook very fast. Thank you

 

[DanteAmor]

Put your macro here to review them.
Use the code tag to put the code.

 

[Abusalma02]

Here is the code

Private Sub CommandButton1_Click()
'enter UserForm data in worksheet
Dim totalRows As Long
Dim str As String

If TextBox1.Text = "" Then
MsgBox "Please Enter the Transaction Date", vbOKOnly, "Date Error!"
Exit Sub
ElseIf ComboBox1.Value = "" Then
MsgBox "Please Select the Customer Name", vbOKOnly, "CustomerName Error!"
Exit Sub
End If
If TextBox3.Text = "" Then
MsgBox "Please Enter the Debit Amount", vbOKOnly, "Debit Error!"
Exit Sub
End If

'determine worksheet row to post data
totalRows = Sheet4.Cells(Rows.Count, "A").End(xlUp).Row
If totalRows < 5 Then
totalRows = 5
Else
totalRows = totalRows
End If

'Post Data in worksheet:
Sheet4.Cells(totalRows + 1, 1) = TextBox1.Text
Sheet4.Cells(totalRows + 1, 2) = ComboBox1.Value
Sheet4.Cells(totalRows + 1, 5) = TextBox3.Text
MsgBox "Data Added Sucessfully!", vbOKOnly + vbInformation, "Data Added"
'clear the data
Me.ComboBox1 = ""
Me.TextBox2 = ""
Me.TextBox3 = ""
End Sub

Private Sub CommandButton2_Click()
Dim iCancle As VbMsgBoxResult
iCancel = MsgBox("Confirm if you want to Cancel", vbQuestion + vbYesNo, "Transaction Form")
If iCancel = vbYes Then
Unload Me
End If
End Sub
Private Sub TextBox3_Change()
TextBox3.Value = Format(TextBox3.Value, "#N###,##")
End Sub

Private Sub UserForm_Initialize()
Dim i As Long, LastRow As Long, ws As Worksheet
Set ws = Sheets("Customers")
LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row
    For i = 6 To LastRow
    Me.ComboBox1.AddItem ws.Cells(i, "C").Value
Next i

Set ws = Sheets("Banking")
LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row
    For i = 6 To LastRow
        If Val(Me.ComboBox1.Value) = ws.Cells(i, "B") Then
        Me.TextBox2 = ws.Cells(i, "D").Value
        End If
    Next i
    
TextBox1.Text = Format(Now(), "dd mmm, yyyy")

End Sub

The Problem am experiencing was that the Userform 3 is working very slowly before it will post data into worksheet. As I said earlier, I have Seven worksheets with different userforms on each at least about 2 to 4 userform on each pages. And also the workbook is loading slowly as well.

 

[DanteAmor]

Which part of the code is slow?
How many records do you have on each sheet, Sheet4, Customers, Banking?

 

[dmt32]

Hi,
with a complex project it's helpful to the forum if you could place copy of your workbook (with sensitive data removed) in a dropbox & provide a link to it here.

Dave

 

[Abusalma02]

The Part of the code that is slow is posting data to worksheet on Sheet 4 (Banking).
The record that would exist on each sheet would be unlimited as the template would be use for future purpose.
The Customers (Sheet 2) is for creating an Account for a customer. And the Banking (Sheet 4) is for making a Credit/Deposit of money, so this is were am facing some challenges. Before it's working fine, but meanwhile I don't known what's wrong along the line.

 

[dmt32]

If project is as complex as you describe could be a number of factors - can only suggest that before your code posts data to worksheet that you turn of events, calculation etc & see if this helps

This at beginning

Private Sub CommandButton1_Click()
'enter UserForm data in worksheet
Dim totalRows As Long
Dim str As String

If TextBox1.Text = "" Then
MsgBox "Please Enter the Transaction Date", vbOKOnly, "Date Error!"
Exit Sub
ElseIf ComboBox1.Value = "" Then
MsgBox "Please Select the Customer Name", vbOKOnly, "CustomerName Error!"
Exit Sub
End If
If TextBox3.Text = "" Then
MsgBox "Please Enter the Debit Amount", vbOKOnly, "Debit Error!"
Exit Sub
End If

With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
        .EnableEvents = False
    End With

'rest of code
'
'




're-set
With Application
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
        .EnableEvents = True
    End With

End Sub

Dave

 

[DanteAmor]

The Part of the code that is slow is posting data to worksheet on Sheet 4 (Banking).

But you are only adding one record!
Do you have images on your sheet?
Do you have many formulas?
You can copy only the data from "Banking" sheet to a new sheet, but only the data and delete the old "Banking" sheet.
Rename the new sheet as "Banking"
Let's see if that improves time.

 

[Abusalma02]

But you are only adding one record!
Do you have images on your sheet?
Do you have many formulas?
You can copy only the data from "Banking" sheet to a new sheet, but only the data and delete the old "Banking" sheet.
Rename the new sheet as "Banking"
Let's see if that improves time.

Ok, I will.

 

[Abusalma02]

If project is as complex as you describe could be a number of factors - can only suggest that before your code posts data to worksheet that you turn of events, calculation etc & see if this helps

This at beginning

Private Sub CommandButton1_Click()
'enter UserForm data in worksheet
Dim totalRows As Long
Dim str As String

If TextBox1.Text = "" Then
MsgBox "Please Enter the Transaction Date", vbOKOnly, "Date Error!"
Exit Sub
ElseIf ComboBox1.Value = "" Then
MsgBox "Please Select the Customer Name", vbOKOnly, "CustomerName Error!"
Exit Sub
End If
If TextBox3.Text = "" Then
MsgBox "Please Enter the Debit Amount", vbOKOnly, "Debit Error!"
Exit Sub
End If

With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
        .EnableEvents = False
    End With

'rest of code
'
'




're-set
With Application
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
        .EnableEvents = True
    End With

End Sub

Dave

Thank you sir, but am still experiencing the same problem.
Don't you think it might be the heaviness of the workbook or something else?
May be I need a code to run it faster!