Difference makes the DIFFERENCE

gallery/tryjesus_bl_nobg

Consulting - Quality

Categorised Menu List

Format a spreadsheet based on standard formatting methods.

How it works: 

based on the code, the spread sheet is formatted into different styles.

 

to experiment:

  1. Put some data in excel file
  2. copy and paste the following code
  3. assign this to a button on screen
  4. click the button to execute the code.

Context: 

Every spread sheet, before we send to stakeholder should undergo some beautification process.  As a part of this, we see that they are of same font, alignment, row width and so on...

 

    FORMAT SPREAD SHEET

    STANDARD FORMATTING WITH MACRO CODE

    Sub prcFormatSheet()
        Application.StatusBar = "Formatting current sheet to Standard Formatting..........."
        Cells.Select
        With Selection.Font
            .Name = "Calibri"
            .Size = 10
        End With


        Selection.RowHeight = 20.25
        With Selection
            .VerticalAlignment = xlCenter
            .ColumnWidth = 8.57
        End With

     

        Rows("1:1").RowHeight = 40.5
        Rows("1:1").Select

        With Selection
            .VerticalAlignment = xlCenter
            .WrapText = True
        End With

     

    End Sub

    Issues you may encounter: While you copy and paste the code from here to code window, replace double quotes in code window again, as these quotes will appear as smart quotes.

     

    The following code is sufficient, a new text file is may not be necessary here...

    It is always advisable to make best use of status bar.  Application.StatusBar = "some text"

    This will help end user to know that the work is in progress...