Automatically display page breaks in Excel(Excel 2003, 2007, 2010)

                You would like to control the page breaks regularly for a printable configuration of tables and so you are looking for an option to turn on the page break display.

                In Excel you can easily view the page breaks by selecting the Page Break Preview option. You can also change to normal view again and see the dotted markings of the paging there. However, it remains only so long as the worksheet is open. Once you reopen the worksheet you will have to redo the Page Break Preview in order to see the page breaks. You can easily automate the Page Break Preview option with the help of a simple Macro.
               Open the relevant workbook and then VBA editor with the key combination [Alt] + [F11]. Now search the open file to the upper left in the project explorer and find “This workbook” in the structure below it.
               Open the related code window on it with a double click. If the code frame is no prepared yet, open via the upper combination fields “Workbook” and “Open”. Then enter the code as under:

Private Sub Workbook _ Open()
Dim i As Integer
For i = 1 To ThisWorkbook.
Sheets.Count
Sheets(i).
DisplayAutomaticPageBreaks =
True
Next i
End Sub

Save the macro and close the workbook. Make sure that the Macros are active for the workbook, when you open the file next time the sheets will automatically have the page breaks. If you have added new sheets you will have to save the workbook and reopen them to see the changes. If the preview is not required later, remove the Macro from the workbook.

Read More...

Record the last modified date internally(Excel 2003, 2007, 2010)

             You want to keep a track of when your Excel sheet was last modified. You want Excel to insert and update the date automatically.
             As a solution for this, you need a VBA code in the working folder so that a macro automatically ensures an update. Open the relevant working folder for this and select the command"Tools → Macro →Macros”. From Excel 2007 onwards, activate the “View” tab in the taskbar and click the “Macros” icon. Now enter a macro name and click “Create” or if a macro already exists, click “Edit”. In the VBA editor, navigate to the “This working folder” entry in the upper left of the project explorer under “VBAProject" of the current file. Double click and open the relevant code window that is now ready for your entries.In the right combination field, select the “SheetChange” system procedure whereupon the relevant number of macros is added below. Add all the further rows as follows:

Private Sub Workbook _ SheetChange(ByValSh As Object, ByVal Target As Range) Application.EnableEvents = False Sh.Range("A1").Value = Date Sh.Range("A1").NumberFormat = "mm/dd/yyyy" Applications.EnableEvents = True End Sub
               The sample code is triggered on the current sheet by the result of the change. The value of the current date is then entered on the sheet in the “A1” cell. It is mandatory to switch the event processing off within the macros in this example; else, the change in the A1 cell automatically triggers the next SheetChange event. This would always lead to an endless recursions. Instead of a separate entry for every table, you can also access the document property and update it centrally in the same table. Then also specify the relevant work sheet, for instance:
Worksheet("Sheet1").Range("A1"). Value= ThisWorkbook. BuiltDocumentProperties(12)
               This command will change the A1 cell in Table1 and assigns it with the document property specified with the last change. This is the information that can be called up in the file properties under Windows. After entering the script, close the VBA editor and save the Excel file in your document folder. When calling up the sheet, ensure that the contained macros are activated; else, the security settings prevent the functioning of the macro.

Read More...