Macro VBA

Abusalma02

New Member
Joined
Feb 29, 2020
Messages
13
Office Version
2019, 2016
Platform
Windows, Web
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
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
11,702
Office Version
2007
Platform
Windows
Put your macro here to review them.
Use the code tag to put the code.

1591402141239.png
 

Abusalma02

New Member
Joined
Feb 29, 2020
Messages
13
Office Version
2019, 2016
Platform
Windows, Web
Here is the code
VBA 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

Well-known Member
Joined
Dec 3, 2018
Messages
11,702
Office Version
2007
Platform
Windows
Which part of the code is slow?
How many records do you have on each sheet, Sheet4, Customers, Banking?
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
5,811
Office Version
2019
Platform
Windows
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

New Member
Joined
Feb 29, 2020
Messages
13
Office Version
2019, 2016
Platform
Windows, Web
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

Well-known Member
Joined
Jul 3, 2012
Messages
5,811
Office Version
2019
Platform
Windows
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
Rich (BB 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

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

Well-known Member
Joined
Dec 3, 2018
Messages
11,702
Office Version
2007
Platform
Windows
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

New Member
Joined
Feb 29, 2020
Messages
13
Office Version
2019, 2016
Platform
Windows, Web
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

New Member
Joined
Feb 29, 2020
Messages
13
Office Version
2019, 2016
Platform
Windows, Web
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
Rich (BB 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

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!
 

Watch MrExcel Video

Forum statistics

Threads
1,099,000
Messages
5,465,932
Members
406,453
Latest member
MarkB5432

This Week's Hot Topics

Top