none
worksheet format RRS feed

  • Question

  • Hi,

    I use code for worksheet format as below. Is there any better way ? It is slow.

    Private Sub Worksheet_Activate()
        Application.ScreenUpdating = False
            With ActiveSheet.PageSetup
            .LeftMargin = Application.InchesToPoints(0)
            .RightMargin = Application.InchesToPoints(0)
            .TopMargin = Application.InchesToPoints(0)
            .BottomMargin = Application.InchesToPoints(0)
            .HeaderMargin = Application.InchesToPoints(0)
            .FooterMargin = Application.InchesToPoints(0)
            .CenterHorizontally = True
            .CenterVertically = False
            .BlackAndWhite = False
            ActiveWindow.View = xlPageLayoutView
            ActiveWindow.DisplayWhitespace = False
            .Orientation = xlPortrait
            .PaperSize = xlPaperA4
         End With
            Application.ScreenUpdating = True
    End Sub

    thank you.

    Sunday, June 14, 2015 4:05 PM

Answers

  • 1) Why would you want to run this code EACH time the sheet is activated? After the first time, nothing will be changed.

    2) If you're using Excel 2010 or later, add the following line immediately below Private Sub ...:

        Application.PrintCommunication = False

    and immediately above End Sub:

        Application.PrintCommunication = True

    This should eliminate the delay that occurs when you set the page setup.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by L.HlModerator Wednesday, June 24, 2015 11:44 PM
    Sunday, June 14, 2015 7:29 PM
  • Macros such as Worksheet_Activate and Workbook_Open are so-called event procedures. They are executed automatically by Excel when a specific event occurs: Worksheet_Activate when the worksheet is activated, and Workbook_Open when the workbook is opened.

    Since such macros aren't intended to be run by themselves, or to be called from other modules, they are declared as Private, i.e. visible only within their own module.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by L.HlModerator Wednesday, June 24, 2015 11:43 PM
    Monday, June 15, 2015 2:18 PM

All replies

  • 1) Why would you want to run this code EACH time the sheet is activated? After the first time, nothing will be changed.

    2) If you're using Excel 2010 or later, add the following line immediately below Private Sub ...:

        Application.PrintCommunication = False

    and immediately above End Sub:

        Application.PrintCommunication = True

    This should eliminate the delay that occurs when you set the page setup.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by L.HlModerator Wednesday, June 24, 2015 11:44 PM
    Sunday, June 14, 2015 7:29 PM
  • Hi,

    Thank you Hans Vogelaar,

    I want it , because when this worksheet goes to different OS and Version of MS OFFICE, it keeps the same format.

    When I used else where I always saw a change in format and again needed formatting page set up.

    regards

    Monday, June 15, 2015 1:43 AM
  • It should be enough to run the code when the workbook is opened (i.e. in the Workbook_Open event in the ThisWorkbook module). That is more efficient than running it each time the worksheet is activated.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, June 15, 2015 5:15 AM
  • All worksheet do not have same formatting. It was very slow in Workbook_Open event in the ThisWorkbook module. I divided code in this way. I thought may be any better way. What is private sub attribute() ? Is it for formatting ?

    regards


    Monday, June 15, 2015 11:46 AM
  • Macros such as Worksheet_Activate and Workbook_Open are so-called event procedures. They are executed automatically by Excel when a specific event occurs: Worksheet_Activate when the worksheet is activated, and Workbook_Open when the workbook is opened.

    Since such macros aren't intended to be run by themselves, or to be called from other modules, they are declared as Private, i.e. visible only within their own module.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by L.HlModerator Wednesday, June 24, 2015 11:43 PM
    Monday, June 15, 2015 2:18 PM
  • Thank you Hans Vogelaar,

    Is private sub attribute() for formatting or else ?

    regards

    Tuesday, June 16, 2015 1:35 PM
  • Sub starts the definition of a procedure, i.e. a piece of code that performs a specific task.

    Private means that the procedure can be called from other procedures in the same code module, but not from procedures in other modules. [I'm over-simplifying things a bit, but that doesn't matter for now]

    If you omit the word Private, the procedure can be called from procedures in other modules within the same workbook as well: a procedure is public by definition.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, June 16, 2015 3:23 PM
  • Thank you Hans Vogelaar,

    I am sorry, I did not ask properly. please forgive.

    Is sub attribute() for formatting or else ? I used this, got from net. It disturbed my sheet. I do not know why.

    regards

    Wednesday, June 17, 2015 1:59 AM
  • In

    Private Sub attribute()

    attribute is the name of the macro. It is not the name of one of the event procedures that will be run automatically by Excel, so the macro must be called from another procedure (sub) in the same module, otherwise it will never be executed.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, June 17, 2015 5:25 AM
  • Thank you,

    regards

    Wednesday, June 17, 2015 12:27 PM