Friday 31 August 2012

VBA Excel: Create Summary / Index Sheet

The below code will be used to create an index sheet of Hyperlinks to all sheets in the workbook.



Final Output: (You can add good formatting styles also :-))

VBA Excel: Double click event in Cell

This is an alternative/interactive way instead regular drop-down list. For example in your dashboard, you are using traffic indicators, In such case you can let the users to double click to change the value of the metric. The code is below, copy & enjoy :)

VBA Excel: Cell Change Tracking

In the 'Shared workbook' environment or a workbook going through multiple levels of updation, we can track of critical cell entries. We can capture those details and hide the columns containing the data with password protection.

VBA Excel: Dynamic Data Validation List ( Nested Drop Down )

Dynamic Data Validation List ( Nested Drop Down ): The below code will be used to do 'Data Validation' through macro. I have used this particular code to populate the drop-down list in a cell based on selection in the previous cell.

Thursday 30 August 2012

VBA Excel: Get Directory (Folder Path)

The 'Get Directory' function will be used to get a folder path with file explorer option. mostly used to open/save a file OR list of files from a particular directory.

VBA Excel: Combine multiple excel files

The below source code will combine multiple workbooks in a folder into single workbook.

VBA Excel: Create new excel file (xls, xlsx)

If you need to create a new workbook with the collected data from Userform or existing data in the source workbook/worksheet, use the below code. This code will sense the application version (Ms2003, Ms2007 and above) and create the new excel file.


VBA Excel: Send mail from Outlook with attachment