none
Code makes Excelsheet to open taking long time and flashes few trimes. RRS feed

  • Question

  • Hi,
    I am using code to format Excel layout so that it keeps it's shape always same. This code makes Excel sheet opening very slow. Can I make some changes so that it become faster than what it is now?

    Option Explicit
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        Application.EnableEvents = False
        If Target.Address = "$B$88" Then GoTo SkipCommands  'user edited B88 directly
        If Range("B88").Value <> "" Then GoTo SkipCommands
        If Range("B82").Value <> "" Then Range("B88").Value = "The cause of death will be ascertained after receiveing toxicological investigation report ."
        If Range("B82").Value = "" And Range("B88").Value = "The cause of death will be ascertained after receiveing toxicological investigation report ." Then Range("B88").Value = ""
    SkipCommands:
       Application.EnableEvents = True
    End Sub
    Private Sub Worksheet_Change(ByVal Target As Range)
        If Not Intersect(Range("A1"), Target) Is Nothing Then
            Application.EnableEvents = False
            ' Your code here

            Application.EnableEvents = True
        End If
    End Sub





    Private Sub Worksheet_Activate()
        Application.ScreenUpdating = False
        With ActiveSheet.PageSetup
        If .LeftMargin <> Application.InchesToPoints(0) Then .LeftMargin = Application.InchesToPoints(0)
        If .RightMargin <> Application.InchesToPoints(0) Then .RightMargin = Application.InchesToPoints(0)
        If .BottomMargin <> Application.InchesToPoints(0) Then .BottomMargin = Application.InchesToPoints(0)
        If .TopMargin <> Application.InchesToPoints(0) Then .TopMargin = Application.InchesToPoints(0)
        If .HeaderMargin <> Application.InchesToPoints(0) Then .HeaderMargin = Application.InchesToPoints(0)
        If .FooterMargin <> Application.InchesToPoints(0) Then .FooterMargin = Application.InchesToPoints(0)
        If .CenterHorizontally <> True Then .CenterHorizontally = True
        If .BlackAndWhite <> False Then .BlackAndWhite = False
        If ActiveWindow.View <> xlPageLayoutView Then ActiveWindow.View = xlPageLayoutView
        If ActiveWindow.DisplayWhitespace <> False Then ActiveWindow.DisplayWhitespace = False
        If .Orientation <> xlPortrait Then .Orientation = xlPortrait
        If .PaperSize <> xlPaperA4 Then .PaperSize = xlPaperA4
        Columns.ColumnWidth = 1.799301
        Rows.RowHeight = 12.5
        End With
            
        Cells.Select
        With Selection
        .Font.Size = 11
        If .Font.Size <> 11 Then .Font.Size = 11
        If .Font.name <> "Calibri" Then .Font.name = "Calibri"
        If .WrapText = True Then .WrapText = False
        End With
        
        Range("A8,A10,A12,A14,A16,A18,A20,A23,A25,A27,A30,A33,A36,A38,A43,A46,A49,A52,A55,A57,A60,A67,A70,A75,A80,A83,A85,A87,A89").EntireRow.RowHeight = 3.75
        Range("A22,A29,A32,A35,A40,A42,A45,A48,A51,A54,A59,A62,A64,A66,A69,A72,A74").EntireRow.RowHeight = 8.25
        Range("A22,A29,A32,A35,A40,A42,A45,A48,A51,A54,A59,A62,A64,A66,A69,A72,A74").EntireRow.Font.Size = 8
        
        If Range("A3").EntireRow.RowHeight <> 14 Then Range("A3").EntireRow.RowHeight = 14
        If Range("R3:AA3").HorizontalAlignment <> xlCenter Then Range("R3:AA3").HorizontalAlignment = xlCenter
        If Range("R3:AA3").VerticalAlignment <> xlCenter Then Range("R3:AA3").VerticalAlignment = xlCenter
        If Range("R3:AA3").Font.Bold <> True Then Range("R3:AA3").Font.Bold = True
        If Range("R3:AA3").Font.Size <> 13 Then Range("R3:AA3").Font.Size = 13
        Range("R3:AA3").Select
        With Selection.Borders(xlEdgeBottom)
        .Weight = xlMedium
        End With
        
        If Range("A26").EntireRow.RowHeight <> 14 Then Range("A26").EntireRow.RowHeight = 14
        If Range("Q26:AB26").HorizontalAlignment <> xlCenter Then Range("Q26:AB26").HorizontalAlignment = xlCenter
        If Range("Q26:AB26").VerticalAlignment <> xlTop Then Range("Q26:AB26").VerticalAlignment = xlTop
        If Range("Q26:AB26").Font.Bold <> True Then Range("Q26:AB26").Font.Bold = True
        If Range("Q26:AB26").Font.Size <> 11.5 Then Range("Q26:AB26").Font.Size = 11.5
        Range("Q26:AB26").Select
        With Selection.Borders(xlEdgeBottom)
        .Weight = xlMedium
        End With
        Range("N26").Select
        Range("G84,B91,B92,B93,B94,P91,P92,P93,P94,AE91,AE92,AE93,AE94").Font.Size = 10.5
        
        'If Range("B82").Value <> "" Then Range("B88").Value = "The cause of asphyxial death will be ascertained after receiveing toxicological investigation report ."
        'If Range("B82").Value = "" Then Range("B88").Value = ""
       

        
        Application.ScreenUpdating = True
    End Sub


    Please help.

    Regards

    Tuesday, July 17, 2018 6:12 AM

All replies

  • Hello drsantoshsinghrathore,

    I did a quickly test with your code on an empty sheet and a sheet with some simple sheet, both of them could be opened quickly.

    I'm wondering if it is related to the data in the sheet. How many data is in your worksheet? Could you reproduce the issue on a newly created workbook? Would you mind sharing a workbook for us to reproduce the issue? Please remember to remove any sensitive information.

    For sharing file, you could share the file via Cloud Storage, such as One Drive, and then put link address here.

    Thanks for understanding.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, July 18, 2018 5:50 AM