VBA Code

Event handler procedures must always be located in the correct module, otherwise they will not run. Never put any event-handlers in a standard code module.
When using event codes it is essential to include Application.EnableEvents = False. This will prevent an endless loop

VBA - Replace the main Excel caption

Application.Caption = "Your text" 

VBA - Replacing the File Name with the File Name and Path

Windows(1).Caption = ActiveWorkbook.FullName 

VBA - Performing an action when a particular cell is active

Private Sub Workbook_SheetChange(ByVal As Object, ByVal Target As Range) 
   If Target.Address = "$A$3" Then 
   End If 
End Sub 

Inserting the date the file was last saved.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) 
   Range("A3").Value = Now() 
End Sub 

Finding the version of Excel you are using

If Val(Application.Version) = 10 Then Call MsgBox("You are using Excel 2002") 

Application

Application.Quit 
Application.Run "Personal.xls!Macro2" 
Application.AskToUpdateLinks = False        'Remove the prompt to update the links  
Application.ScreenUpdating = False 
Application.Caption = "" 
Application.StatusBar = "please wait .." 
Application.RecentFiles = 9 
Application.StatusBar = False 
Application.Cursor = xlWait | xlDefault 
Application.ReferenceStyle = "A1" 
Application.FindFormat ?? 
Application.CellFormat 
Application.ReplaceFormat ?? 
Application.InchesToPoints(1.5) 
Application.CutCopyMode = False 
Application.OnTime TimeValue("2:30 PM"), "C:\temp\Temp.xls!WkbName.macro_name" 
Application.SheetsInNewWorkbook 
Application.Visible = False 
Application.Volatile ?? 

This does not appear in the object browser but can be used
This is only available in Excel, not in Word or PowerPoint.

Application.Pi() 

ActiveWorkbook

ActiveWorkbook.FullName 
ActiveWorkbook.Name 
ActiveWorkbook.Path 

ActiveWorkbook.VBProject.VBComponents.Remove _ 
ActiveWorkbook.VBProject.VBComponents("Module2") 

Runs a Microsoft Excel 4.0 macro function and then returns the result of the function. The return type depends on the function.

Application.ExecuteExcel4Macro 

Application.RecordRelative 

Allows modification of the macro attributes such as the name, description, shortcut key, category and associated help file
Equivalent to the macro options dialog box.

Application.MacroOptions 

Records code if the macro recorder is on.

Application.RecordMacro 
Application.RecordMacro BasicCode:="Application.Run ""MySub"" " 

BasicCode Optional Variant. A string that specifies the Visual Basic code that will be recorded if the macro recorder is recording into a Visual Basic module. The string will be recorded on one line. If the string contains a carriage return (ASCII character 10, or Chr$(10) in code), it will be recorded on more than one line.
XlmCode Optional Variant. This argument is ignored.
The RecordMacro method cannot record into the active module (the module in which the RecordMacro method exists).
If BasicCode is omitted and the application is recording into Visual Basic, Microsoft Excel will record a suitable Application.Run statement.
To prevent recording (for example, if the user cancels your dialog box), call this function with two empty strings.

This hierarchy is known as the Object model. Each Microsoft Office application (Excel, Word, Access, PowerPoint etc) has a different object model. These object models can be viewed using the Object Browser.
When you are looking at code it is often easier to think in terms of objects. An object being something that you can manipulate. There are hundreds if not thousands of objects in Excel. The most obvious ones are a Workbook and a Worksheet. There is a clearly defined hierarchy within the objects. Some objects are contained within other objects. There is an object that can represent a selection of cells. This object is contained within the corresponding worksheet object which is consequently contained within the corresponding workbook object.

Do not use the Range objects as arguments to procedures or function. Use the address instead.
Try to use Range("") with column letter references instead of Cells() with numerical references. If you have to use Cells() the always use a column enumeration so it is very quick to identify individual columns.
Use worksheet code names, rather than referring to the Sheets("---") descriptive names that can be easily changed by the user.
Never use ActiveWorkbook as you can never guarantee what workbook will be the active workbook.
Always use .Cells() instead of .Range(col_letter) when using enumerations
Conditional Formatting - no option on paste special but selecting "formats" will paste conditional formats
User defined functions - you can't use the same name for a module and a user defined function - otherwise the UDF is not recognised.
How to definitely update workbook/just one worksheet (do not use Application.Calculate) - do you have to go through each worksheet ?
Undo functionality is session specific, not workbook specific.

AutomationSecurity Property

Returns or sets an MsoAutomationSecurity constant that represents the security mode Microsoft Excel uses when programmatically opening files.
This property is automatically set to msoAutomationSecurityLow when the application is started.
Therefore, to avoid breaking solutions that rely on the default setting, you should be careful to reset this property to msoAutomationSecurityLow after programmatically opening a file.
Also, this property should be set immediately before and after opening a file programmatically to avoid malicious subversion. Read/write.
Setting ScreenUpdating to False does not affect alerts and will not affect security warnings.
The DisplayAlerts setting will not apply to security warnings.
For example, if the user sets DisplayAlerts equal to False and AutomationSecurity to msoAutomationSecurityByUI, while the user is on Medium security level, then there will be security warnings while the macro is running.
This allows the macro to trap file open errors, while still showing the security warning if the file open succeeds.

Sub Security() 
    Dim secAutomation As MsoAutomationSecurity 

    secAutomation = Application.AutomationSecurity 

    Application.AutomationSecurity = msoAutomationSecurityForceDisable 
    Application.FileDialog(msoFileDialogOpen).Show 

    Application.AutomationSecurity = secAutomation 

End Sub 

Registry Entries

The following registry entry contains the list of recently used workbooks

HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Excel\Security\ 

Worksheet Controls

Accessing your Controls Directly

Any object which is embedded on a worksheet appears in the Drawing layer.
There are several ways to access the objects in the Drawing layer:
1) Use the Shapes collection.
2) Use the OLE Objects collection (Control Toolbar controls only).
3) Refer to the specific name of the object (Control Toolbar controls only).
The first two methods are useful if you want to loop through all the controls on a particular worksheet.

Application.Caller

This property can be very useful for finding out which drawing object activated a procedure

Using the Shapes Collection

This method can be used to refer to controls that have been added from the Control Toolbox toolbar.

Dim shShape As Shape 
For Each shShape in ActiveSheet.Shapes 
   If shShape.Type = msoOLEControlObject Then 
      shShape.Select 
   End If 
Next shShape 

Command Button fonts - Perpetua Titling MT, Rockwell, Sylfaen

Shape Object Summary

This table summarises the different types of objects that can be embedded in the Drawing layer

ActiveX Controls

Accessing the Controls

There are several ways to access the objects in the Drawing layer:
1) Use the Shapes collection.
2) Use the OLE Objects collection.
3) Refer to the specific name of the object.

Using the Shapes Collection

This method can be used to refer to controls that have been added from the Control Toolbox toolbar.
If you do not specify the type of shape you are referring to you are limited to only the properties and methods for a general Shape object.

ActiveSheet.Shapes("CommandButton1").Select 

If you want to refer to the properties and methods of the control you must use the following:

ActiveSheet.Shapes("CommandButton1").OLEFormat.Object.Object.BackColor = RGB(20,20,20) 

ActiveSheet.Shapes.AddOLEObject 

Using the OLEObject Collection

The OLEObject is a container for the controls and therefore corresponds to the control's relationship with the worksheet.

Worksheets("Sheet1").OLEObjects.Count 
Worksheets("Sheet1").OLEObjects.Delete 

If you do not specify that you are referring to the object you are limited to only the properties and methods for a general OLEObject.

Worksheets("Sheet1").OLEObjects("CommandButton1").Select 

If you want to refer to the properties and methods of the control without declaraing an OLEObject datatype then you must use an additional Object property.

Worksheets("Sheet1").OLEObjects("CommandButton1").Object.Caption = "Run" 

In addition to the standard properties and methods for ActiveX controls the following are available for ActiveX control embedded on worksheets.

Dim obObject As OLEObject 
For Each obObject in ActiveSheet.OLEObjects 
   obObject.OLEType = xlOLELink 
   obObject.Name 
   obObject.AutoUpdate = False 
   obObject.BottomRightCell 
   obObject.LinkedCell 
   obObject.ListFillRange 
   obObject.Placement 
   obObject.PrintObject = False 
   obObject.TopLeftCell 
   obObject.Zorder 

   obObject.Object.BackColor = RGB(10,10,10) 
   obObject.Object.Caption = "" 
   obObject.Object.TakeFocusOnClick = False 
Next obObject 

Using the Specific Object Name

This method can only be used to refer to controls that have been added from the Control Toolbox toolbar.

Worksheets("Sheet1").CommandButton1.Select 
CommandButton1.BackColour = RGB(20,20,20) 

Adding Controls

The ClassType is the so-called "pragrammatic identifier" or ProgID for the control
The ClassType and the size and position are the only parameters that are relevant. All the othes can be ignored.

ActiveSheet.OLEObjects.Add (ClassType:="Forms.Textbox.1", _ 
                            FileName:= 
                            Link:= 
                            DisplayAsIcon:= 
                            IconFileName:= 
                            IconIndex:= 
                            IconLabel:= 
                            Left:=10, _ 
                            Top:=10, _ 
                            Width:=10, _ 
                            Height:=10) 

Handling Events

Control Toolbox controls must be placed in the class module behind the worksheet in which they are embedded.
The procedure name must be the same as the name of the control and the name of the event.

Form Controls

Using the Shapes Collection

This is the only method you use to refer to controls that have been added from the Forms toolbar.

ActiveSheet.Shapes("Check Box 1").Select 
ActiveSheet.Shapes("Check Box 1").LinkedCell = "H3" 
Dim shShape As Shape 
For Each shShape in ActiveSheet.Shapes 
   If shShape.Type = msoFormControl Then 
      shShape.Select 
   End If 
Next shShape 

Adding Controls

Dim objButton As Variant 
Set objbutton = Worksheets("Sheet1").Buttons.Add(20,20,20,20) 

Dim objCheckBox As Variant 
Set objcheckbox = Activesheet.Checkboxes.Add(Left, Top, Width, Height) 

ActiveSheet.Shapes.AddFormControl 

Handling Events

The controls on the Forms toolbar can only respond to a single event, the click event.
The only exception is the Edit control which responds to a Change event.

Control Arrays

Introduction:

Programmers accustomed to Visual Studio, are frequently frustrated by the absence of control arrays when working in the Microsoft Office programming environment.
Experienced VBA developers too, are routinely exasperated by the need to create an event procedure for each member of a group of identical controls, when logic suggests that just one generic procedure could be shared by the group.
Although that MS Office does not support control arrays cannot remains a fact, a technique does exist that addresses the functionality advantages of VB control arrays.

Concept:

This article will focus on the use of a custom Class to achieve the benefits of a VB control array. Readers should be familiar with the concepts involved in the use of Class objects and how to create a Class using a Class Module.
Although some readers may find it unnecessary to grasp these concepts in order to practice the specific techniques demonstrated here, that approach will almost certainly cause problems where these techniques are applied in even slightly altered scenarios.
By way of example, two scenarios will be considered, each based within one of the two most popular applications in the MS Office Suite:
(1) creating a control array Class for spreadsheet controls in MS Excel
(2) creating a control array Class for form controls in MS Access
The choice of these applications is intended both to demonstrate the powerful set of options presented by this technique across all the MSO Suite, and also to raise awareness of the type of idiosyncrasies peculiar to each application within MSO.
Finally, it is worth remembering that although there are published workarounds for the scenarios provided here that do not involve the use of a control array, it's the concept itself which remains important, since it is this that may be transferred to another scenario which does not have a conventional workaround.

Excel Spreadsheet Control Arrays:

Assumptions: all controls referred to in relation to Excel Spreadsheets are MS Forms controls. That is, the Excel menu option 'View>Toolbars>Control Toolbox' is used to generate the required controls. This is because controls generated by the 'View>Toolbars>Forms' toolbar are not ActiveX objects and do not support the extended functionality of their counterparts.

Consider a spreadsheet containing a set of mutually exclusive Checkbox controls. When any control in the group is changed to 'True', each of the other checkboxes must be set to 'False'.

The obvious solution might be to create a generic procedure that is called by the 'Click' event of each control, passing the name of the client checkbox as an argument. The procedure would then

loop through the group of controls,
compare each name property against the string argument,
set the checkbox values to 'False' if a match was not found.

However, there are two critical penalties for using this approach:

A new event procedure must be written for each checkbox that is added to the group
If the name of any of the controls is changed, the procedure will cease to behave as intended until the code is altered to accommodate the change.

The alternative technique we are about to practice, fully addresses these significant maintainability issues.

To illustrate this scenario:

1. open a new instance of Excel,
2. create a fresh workbook
3. and insert three checkbox controls onto the active sheet.
4. Additionally, open the VB Editor window,
5. add a standard VB Module
6. and also add a Class Module,
7. then using the properties window, change the name of the class module to "clsCheckbox".

This Class will behave as a generic event "listener" for all the controls in the group of checkboxes.
This is achieved by declaring the object type (which we want the class to represent) using the keyword 'WithEvents' within the class module.

As demonstrated in that article, declare the class object by adding this declaration to the very top of the Class module, "clsCheckBox"

Public WithEvents chk As MSForms.CheckBox 

Note the explicit reference to the MSForms object library. This prevents the use of checkbox controls from another library, which may not implement the methods and properties that we intend to use.
For this simple example, we will make use of the checkbox's 'Click' event; however there are a number of other events that could be used for other purposes.
You can explore these using the drop-down box at the top-right of the Class Module code window.
Since code within the Class may change the values of the checkboxes, we must take care to prevent cascading events.
A cascading event occurs when an event procedure executes a command that causes the same event procedure to fire without pause, creating an infinite loop that may cause the application, or even the computer itself, to crash.
MSForms controls are not members of the Excel Application object, therefore setting the 'Application.EnableEvents' property to False will have no effect in this case.
Our work-around will be to make use of a public variable which can be evaluated prior to executing any commands within the checkbox Click event.
Move to the regular code module, 'Module1' and add these declarations:

Public blnHaltEvents As Boolean 
Public colCheckBox As New Collection 

Because Boolean variables default to False, we can be confident that our events will always execute fully unless we specify otherwise.
Observe also, the public Collection object. Collections are ideally suited to the purpose of this exercise, but you should be confident of how they behave and are implemented before continuing.
Since the Collection is a member of the VBA object library, you can refer to the help file for the topic in any of the MS Office applications.
This Collection will contain a "clsCheckBox" object for each checkbox control found on the worksheet, and is the means that enables the checkbox events to be monitored persistently.
Although the checkbox controls on the spreadsheet are MSForms ActiveX controls, Excel wraps each within an Excel object called an OLEObject.
This configuration means that our code must first refer to each OLEObject on the worksheet, before referencing the checkbox control that it contains.
All MSO applications which feature mixed text/object interfaces exhibit this architecture, e.g. Excel and Word, but not Access since Access is entirely form-based.
The following procedures illustrate how to manage this within Excel, however the hierarchy concept is the same within other mixed text/object interface MSO applications such as Word.

The first procedure, "Class_Init" initialises our Class module "clsCheckBox". That is, it:

searches the target spreadsheet for OLEObjects
tests each OLEObject to ensure that it is a CheckBox control
creates a new clsCheckBox object
appoints the clsCheckBox as the CheckBox control
adds the clsCheckBox object to the public Collection for later reference

Public Sub Class_Init() 
Dim oleO As Excel.OLEObject 
Dim cls As clsCheckbox 

    ' loop all the OLE Objects on the Worksheet
    For Each oleO In Sheet1.OLEObjects 
        
        ' test that only the required controls are included
        If TypeName(oleO.Object) = "CheckBox" Then 
        
            ' create a new object from our custom class
            Set cls = New clsCheckbox 
            ' assign the class to the OLE object found on the sheet
            Set cls.chk = oleO.Object 
            ' add the populated class to our custom Collection so that we can refer
            '   to it later
            colCheckBox.Add cls, cls.chk.Name 
        End If 
        
    Next oleO 
    
End Sub 

Public Sub Class_Terminate() 

    'loop through our custom collection of controls
    For Each ch In colCheckBox 
        'remove the object from the collection and release the associated memory
        colCheckBox.Remove ch.chk.Name 
        Set ch.chk = Nothing 
    Next ch 
End Sub 

The second procedure, "Class_Terminate" removes each object that was created by "Class_Init" from memory when the process is ended.
This is an important process that will prevent memory space from being otherwise "lost" until you computer is re-started.
Now return to "clsCheckBox" and add the following event procedure to the Class:

Private Sub chk_Click() 

    ' prevent cascading events caused by changing the value of each checkbox
    '   the 'EnableEvents' property of the Application cannot be used in this case
    If blnHaltEvents Then Exit Sub 
    blnHaltEvents = True 
    
    ' since an option much be selected at all times within an option group,
    '   prevent any checkbox from being manually set to False
    If Not Me.chk.Value Then 
        Me.chk.Value = True 
        ' skip the main actions
        GoTo Finish 
    End If 
    
    ' the name of the active control provides visible proof of the relationship
    Application.StatusBar = Me.chk.Name 
    
    ' loop all the checkbox objects in the previously assigned collection object
    For Each ch In colCheckBox 
        If Not ch.chk.Name = Me.chk.Name Then ch.chk.Value = False 
    Next ch 

' use a termiating subroutine to make any critical settings fail-safe
Finish: 
    blnHaltEvents = False 
    Exit Sub 

' good practice advocates the use of a proper error-handler in every procedure, however the
'   point here is simply to force execution of commands in the terminating subroutine under
'   all circumstances
Error_H: 
    Resume Finish 
    
End Sub 

This is the procedure that actually creates the effect of exclusive selection.
When any of the check box control are clicked, this event fires for that single control, and sets the value for all the other check boxes to False.

Notes

If this class was to be properly implemented for use with controls embedded on worksheets, you would initialise the class as the relevant worksheet was activated, using the 'SheetActivate' event of the 'ThisWorkbook' object.
This event would first test the 'colCheckBox' collection to check if it had been previously initialised, and if so it should call the 'Class_Terminate' procedure before reloading the collection using 'Class_Init'.
Observant readers will realise that the 'SheetActivate' event referred to above, also implements the multi-use event principle that this exercise is based upon!

Different Properties

Checkbox

CommandButton

OptionButton

TextBox