speeding up macro based excel program

[dss28]

Hi,
I read in several post on internet about speeding up the macro based excel programs.
I adopted the following procedure and as recommended added these lines in every macro.
however still i feel the speed is very less although it depends upon several factors besides correct coding to which i am very new.
can someone guide if this is the right approach.
the lines i added in every macro are:

Sub ()

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.EnableEvents = False
ActiveSheet.DisplayPageBreaks = False


Code....


Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.DisplayStatusBar = True
Application.EnableEvents = True
ActiveSheet.DisplayPageBreaks = True

End Sub

 

[Michael M]

MAybe you should post the code as well.....it seems it might be part of the issue.
AND
please use code tags when posting any codes....simply click on the VBA button on the reply toolbar, then paste your code between the tags that appear

 

[dss28]

MAybe you should post the code as well.....it seems it might be part of the issue.
AND
please use code tags when posting any codes....simply click on the VBA button on the reply toolbar, then paste your code between the tags that appear

Sub SaveADL()


        Application.Calculation = xlCalculationManual
        Application.ScreenUpdating = False
        Application.DisplayStatusBar = False
        Application.EnableEvents = False
        ActiveSheet.DisplayPageBreaks = False

    Dim iRow As Long
    
    If frmADLForm.txbRowNumber.Value = "" Then
    
        iRow = shDetailsADL.Range("A" & Rows.Count).End(xlUp).row + 1
        
    Else
    
        iRow = frmADLForm.txbRowNumber.Value
        
    End If

    With shDetailsADL.Range("A" & iRow)
    
        .Offset(0, 0).Value = "=Row()-1"
        .Offset(0, 1).Value = frmADLForm.txb1.Value
        .Offset(0, 2).Value = frmADLForm.txb2.Value
        .Offset(0, 3).Value = frmADLForm.cmb1.Value
        .Offset(0, 4).Value = frmADLForm.txb3.Value
        .Offset(0, 5).Value = frmADLForm.txb4.Value
        .Offset(0, 6).Value = frmADLForm.cmb2.Value
        .Offset(0, 7).Value = frmADLForm.txb5.Value
        .Offset(0, 8).Value = frmADLForm.txb6.Value
        .Offset(0, 9).Value = frmADLForm.cmb3.Value
        .Offset(0, 10).Value = frmADLForm.txb7.Value
        .Offset(0, 11).Value = frmADLForm.txb8.Value
          .Offset(0, 14).Value = frmADLForm.txb9.Value
        .Offset(0, 16).Value = frmADLForm.cmb4.Value
        .Offset(0, 15).Value = frmADLForm.txb10.Value
        .Offset(0, 19).Value = Application.UserName
        .Offset(0, 20).Value = Format([Now()], "DD-MMM-YYYY HH:MM")

        
    End With
    
    Call ResetADL_Form
    
    
    MsgBox "Data Submitted Successfully!"
    
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
Application.DisplayStatusBar = True
Application.EnableEvents = True
ActiveSheet.DisplayPageBreaks = True


End Sub



Sub ResetADLPO_Form()

        Application.Calculation = xlCalculationManual
        Application.ScreenUpdating = False
        Application.DisplayStatusBar = False
        Application.EnableEvents = False
        ActiveSheet.DisplayPageBreaks = False

    Dim iRow As Long
    Dim database As Worksheet
    
      Set database = ThisWorkbook.Sheets("DetailsADL")
    With frmADLForm
    
       .txb1.Value = ""
       .txb1.BackColor = vbWhite '2
       .txb2.Value = ""
       .txb2.BackColor = vbWhite   '3
       .cmb1.Value = ""
       .cmb1.BackColor = vbWhite    '4
       .txb3.Value = ""
       .txb3.BackColor = vbWhite    '5
       .txb4.Value = ""
       .txb4.BackColor = vbWhite  '6
       .cmb2.Value = ""
       .cmb2.BackColor = vbWhite    '7
       .txb5.Value = ""
       .txb5.BackColor = vbWhite    '8
        .txb6.Value = ""
        .txb6.BackColor = vbWhite    '9
       .cmb3.Value = ""
        .cmb3.BackColor = vbWhite    '10
         .txb7.Value = ""
        .txb7.BackColor = vbWhite    '11
         .txb8.Value = ""
        .txb8.BackColor = vbWhite     '12
        .cmb4.Value = ""
        .cmb4.BackColor = vbWhite   '13
        .txb9.Value = ""
        .txb9.BackColor = vbWhite    '14
          .txb10.Value = ""
        .txb10.BackColor = vbWhite    '15
        .txb11.Value = ""
        
        shDetailsADL.Range("C2", shDetailsADL.Range("C" & Rows.Count).End(xlUp)).Name = "ADL"
        
        .cmb1.RowSource = "ADLS"
        
        .cmb1.Value = ""
        
'        assigning rowsource to lstDetailsADL-listbox
        
        .lstDetailsADL.ColumnCount = 31
        
        .lstDetailsADL.ColumnHeads = True
        
        .lstDetailsADL.ColumnWidths = "15,20,50,80,100,50,15,200,30,20,50,50,0,0,200,50,30,0,0,50,50,0,0,0,0,0,0,0,0,0,0,0,0,0"
        
        'identify the last non-blank row in database sheet

        iRow = shDetailsADL.Range("A" & Rows.Count).End(xlUp).row

        If iRow > 1 Then

            .lstDetailsADL.RowSource = "DetailsADL!A2:Af" & iRow

        Else

            .lstDetailsADL.RowSource = "DetailsADL!A2:AF2"

        End If

        'identify the last non-blank row in database sheet
        
        iRow = shDetailsADL.Range("A" & Rows.Count).End(xlUp).row
        
        If iRow > 1 Then
        
            .lstDetailsADL.RowSource = "DetailsADL!A2:AF" & iRow
        
        
        Else
        
            .lstDetailsADL.RowSource = "DetailsADL!A2:AF2"
        
        End If
    
    End With
  Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.DisplayStatusBar = True
Application.EnableEvents = True

posting the codes which are taking relatively more time to execute among the 20 plus codes in the program.
will be delighted to recieve comments to improve upon.