McGimpsey & Associates : Excel : Easier Time Entry

This site will look better if you upgrade to a browser that supports web standards.

Easier Time Entry - Toolbar code

To create the toolbar in the JEM_TimeEntry.xla add-in, a toolbar is created using a routine called by the Workbook_Open event macro (in the ThisWorkbook module):

    PrivateSub Workbook_Open()
        JEM_TE__BuildToolbar
    End Sub

The toolbar is given a unique name and declared to be temporary, so that it doesn't persist after the add-in is closed (we'll add suspenders by deleting it explicitly). One control button is added, and given caption of "Time Entry". A Tag property is added to allow finding the control later in the Application_WorkbookDeactivate event macro. The OnAction property is set to our autocorrect toggle code. The Parameter property is set to the default "." value. A Tooltip is added, and the button is set to display in the "up" position. Lastly, the toolbar is made visible.:

    Public Sub JEM__TE__BuildBar()
        With CommandBars.Add(Name:="JEM__TE__Bar", temporary:=True)
            With .Controls.Add
                .Caption = "Time Entry"
                .Style = msoButtonCaption
                .Tag = "JEM__TE__Button"
                .OnAction = "JEM__TE__SetAutoCorrect"
                .Parameter = "."
                .TooltipText = "Create Time Entry autocorrect"
                .State = msoButtonUp
            End With
            .Visible = True
        End With
    End Sub

When the add-in is unloaded, we don't want the toolbar hanging around, so we explicitly destroy it by calling JEM__TE__DestroyToolbar (in a regular code module) :

    Public Sub JEM__TE__DestroyBar()
        On Error Resume Next
        Application.CommandBars("JEM__TE__Bar").Delete
        On Error GoTo 0
    End Sub

from the ThisWorkbook module:

    PrivateSub Workbook_BeforeClose(Cancel As Boolean)
        JEM__TE__DestroyToolbar
    End Sub

Easier Time Entry - App_WorkbookDeactivate code

To ensure that the autocorrect is removed whenever the user switches workbooks (or closes the active workbook), an event handler is entered to monitor for the Application_WorkbookDeactivate event. This is done by inserting a class module, naming it JEM_TextEntryClass, and inserting the following event code. The Public WithEvents declaration declares a variable that we can use to hook into Application events. The _WorkbookDeactivate code first looks for the toolbar button. It sets the button's Parameter property to ":" (so that the SetAutoCorrect routine will remove the autocorrect), then fires the button's OnAction sub as if the button had been pressed:

    Public WithEvents JEM__TE__Application As Application    
    
    Private Sub JEM__TE__Application_WorkbookDeactivate(ByVal Wb As Excel.Workbook)
        On Error Resume Next
        With Application.CommandBars.FindControl(Tag:="JEM__TE__Button")
            .Parameter = ":"
            .Execute
        End With
        On Error GoTo 0
    End Sub

To hook this event macro into the Application events, we need to add more code to the ThisWorkbook declaration and the Workbook_Open event:

    Dim JEM__TE__Class As New JEM__TimeEntryClass


    Private Sub Workbook_Open()
        JEM__TE__BuildBar
        Set JEM__TE__Class.JEM__TE__Application = Application
    End Sub

This page last updated Saturday, 28 February 2004

McGimpsey & Associates

Home

General XL Tips

XL Formulae

Using VBA with XL

XL VBA Functions

XL VBA Macros

Mac XL

Easier Time Entry - Toolbar code

Easier Time Entry - App_WorkbookDeactivate code

QuickLinks