Create a form navigation bar using classes.

[Darren Bartrup]

I'm hoping to get this working.

My Excel project has a number of forms each of which can be opened from a number of buttons at the top of each form (i.e. a navigation bar - clicking a button opens the target form and closes the current form).

At present each form has code similar to that below for each button:

Private Sub btnGoToOutlookStructure_Click()
    Me.Hide
    Unload Me
    frmEmailSetUp.Show
End Sub

What I'd like to have is a class that creates the buttons, automatically disables the button that refers to the current form and does everything for me.

I've got the code that creates the bar and enables/disables the button but I can't get the Click event to fire.
Usually when adding a single command button I'd add the Click event to a collection within the Declarations section of the form and it will work - this adds an instance of the class for each button.

What I'd like to do now is create all buttons within a single instance of the class and capture the click event for each button - I'd then use the value held in Tag to decide what to do after the click.

Here's where I'm up to:

The form has this code to create an instance of the class, pass the frame (Me.fmeNavigationBar) to the class and capture the click event:

Public colNavigationBar As Collection

Private Sub UserForm_Initialize()

    Dim NavigationBar As cls_NavigationBar
    
    Set NavigationBar = New cls_NavigationBar
    NavigationBar.AddNavigationBar Me.fmeNavigationBar
    Set colNavigationBar = New Collection
    Set colNavigationBar = NavigationBar.EventCollection
    
End Sub

The class code creates and positions the four buttons I need (I'm planning on adding the buttons in the form using something like NavigationBar.AddButton "MyCaption", "TargetFormName" but haven't got that far yet):

Public WithEvents btnNavigate As MSForms.CommandButton

Private fme As Frame
Private btnControl As Control
Private colEvents As Collection

Private Const BTN_WIDTH As Long = 72, BTN_HEIGHT As Long = 24, BTN_SPACING As Long = 6
Private Const BTN_ENABLED As Long = &H800080, BTN_DISABLED As Long = &HFF80FF
Private Const BTN_TEXT As Long = &HFFFFFF

Public Property Get EventCollection()

    Set EventCollection = colEvents

End Property

Public Sub AddNavigationBar(FrameReference As Object)

    Dim ContainerForm As Object
    Dim sCaption(1 To 4) As String
    Dim sTargetForm(1 To 4) As String
    Dim x As Long
    
    sCaption(1) = "Set Up"
    sCaption(2) = "Sort Inbox"
    sCaption(3) = "Update Team"
    sCaption(4) = "Allocate Emails"
    
    sTargetForm(1) = "frmEmailSetUp"
    sTargetForm(2) = "frmSortInbox"
    sTargetForm(3) = "frmTeamSetUp"
    sTargetForm(4) = "frmAllocateEmails"
    
    Set fme = FrameReference
    Set ContainerForm = fme.Parent
    Set colEvents = New Collection
    
    For x = 1 To 4
        Set btnControl = fme.Controls.Add("Forms.CommandButton.1", "btnNavigator_" & x)
        With btnControl
            .Left = (x * BTN_SPACING) + ((x - 1) * BTN_WIDTH)
            .Top = BTN_SPACING
            .Height = BTN_HEIGHT
            .Caption = sCaption(x)
            .Tag = sTargetForm(x)
            .Font.Bold = True
            .ForeColor = BTN_TEXT
            If ContainerForm.Name = .Tag Then
                .BackColor = BTN_DISABLED
                .Locked = True
            Else
                .BackColor = BTN_ENABLED
                .Locked = False
            End If
        End With
        Set btnNavigate = btnControl
        colEvents.Add btnNavigate
    Next x
    
    fme.Caption = ""
    fme.Height = BTN_HEIGHT + (3 * BTN_SPACING)
    fme.Width = (x * BTN_SPACING) + ((x - 1) * BTN_WIDTH) + BTN_SPACING
    
End Sub

Private Sub btnNavigate_Click()
    MsgBox "Clicked" 'This doesn't happen.
End Sub

Can I do this all within a single class, or would I need to create another class to deal with the click event and create an instance of that within my NavigatorBar class (I hope not as I'd like to have it all contained within a single class).

Any help is greatly appreciated.

 

[Darren Bartrup]

Figured it out.
This code will create a button for each form named in the clsNavigationBar. The click event will close the current form and open the target form, disabling the button for the new form.
You just need to place a frame in each form and add the code at the bottom of the post to each form.

Create a class called clsNavigationButton to hold the button event code:

Private WithEvents btnNavigate As MSForms.CommandButton
Private TargetFrame As Frame
Private btnControl As Control

Private Const BTN_WIDTH As Long = 72, BTN_HEIGHT As Long = 24, BTN_SPACING As Long = 6
Private Const BTN_ENABLED As Long = &H800080, BTN_DISABLED As Long = &HFF80FF
Private Const BTN_TEXT_ENABLED As Long = &HFFFFFF, BTN_TEXT_DISABLED As Long = &H80000012

Private Sub btnNavigate_Click()
    With VBA.UserForms
        Unload TargetFrame.Parent
        .Add(btnNavigate.Tag).Show
    End With
End Sub

Public Property Set ContainerFrame(FrameReference As Frame)
    Set TargetFrame = FrameReference
End Property

Public Property Get ButtonWidth() As Long
    ButtonWidth = BTN_WIDTH
End Property

Public Property Get ButtonHeight() As Long
    ButtonHeight = BTN_HEIGHT
End Property

Public Property Get ButtonSpacing() As Long
    ButtonSpacing = BTN_SPACING
End Property

Public Sub AddNewButton(ButtonText As String, ButtonTarget As String)

    Dim ButtonNumber As Long
    
    ButtonNumber = CountOfControls(TargetFrame, "btnNavigation_") + 1
    Set btnControl = TargetFrame.Controls.Add("Forms.CommandButton.1", "btnNavigation_" & ButtonNumber)
    With btnControl
        .Tag = ButtonTarget
        .Left = (ButtonNumber * BTN_SPACING) + ((ButtonNumber - 1) * BTN_WIDTH)
        .Top = BTN_SPACING
        .Height = BTN_HEIGHT
        .Caption = ButtonText
        If ButtonTarget = TargetFrame.Parent.Name Then
            .BackColor = BTN_DISABLED
            .ForeColor = BTN_TEXT_DISABLED
            .Enabled = False
        Else
            .BackColor = BTN_ENABLED
            .ForeColor = BTN_TEXT_ENABLED
            .Enabled = True
        End If
        Set btnNavigate = btnControl
    End With

End Sub

Private Function CountOfControls(fme As Frame, sName As String) As Long

    Dim x As Long
    Dim ctrl As Object

    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    'Count how many times a control with that name appears and add 1. '
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    On Error GoTo ERROR_HANDLER

    For Each ctrl In fme.Controls
        If Len(ctrl.Name) > Len(sName) Then
            If Left(ctrl.Name, InStr(ctrl.Name, "_")) = sName Then
                x = x + 1
            End If
        End If
    Next ctrl
    
    CountOfControls = x

    On Error GoTo 0
    Exit Function

ERROR_HANDLER:
    Select Case Err.Number
        
        Case Else
            MsgBox "Error " & Err.Number & vbCr & _
                " (" & Err.Description & ") in procedure CountOfControls."
            Err.Clear
            Application.EnableEvents = True
    End Select
    
End Function

Next create a class called clsNavigationBar. This will need updating depending on the number of buttons needed, the names of the forms and the button text:

Private colNavigationButton As Collection
Private TargetFrame As Frame


Public Function BuildNavigationBar(FrameReference As Frame) As Collection


    Dim NavigationButton As clsNavigationButton
    Dim x As Long
    Dim ButtonText(1 To 4) As String
    Dim ButtonTarget(1 To 4) As String
    Dim FrameWidth As Long
    Dim FrameHeight As Long
    
    ButtonText(1) = "Set Up Email"
    ButtonText(2) = "Process New Emails"
    ButtonText(3) = "UserForm1"
    ButtonText(4) = "UserForm2"
    ButtonTarget(1) = "frmEmailSetUp"
    ButtonTarget(2) = "frmProcessEmails"
    ButtonTarget(3) = "UserForm1"
    ButtonTarget(4) = "UserForm2"
    
    Set TargetFrame = FrameReference
    Set colNavigationButton = New Collection
    
    For x = 1 To 4
        Set NavigationButton = New clsNavigationButton
        With NavigationButton
            Set .ContainerFrame = TargetFrame
            .AddNewButton ButtonText(x), ButtonTarget(x)
            FrameWidth = FrameWidth + .ButtonWidth + .ButtonSpacing
            colNavigationButton.Add NavigationButton
        End With
    Next x
    
    With NavigationButton
        FrameWidth = FrameWidth + (.ButtonSpacing * 2)
        FrameHeight = .ButtonHeight + (3 * .ButtonSpacing)
    End With
    
    With TargetFrame
        .Left = 12
        .Top = 12
        .Width = FrameWidth
        .Height = FrameHeight
    End With
    
    Set BuildNavigationBar = colNavigationButton
    
End Function

Finally create forms to match the array in clsNavigationBar, add a frame to each (called Frame1 in the code) and add this code to the frame:

Public colNavigationBar As Collection


Private Sub UserForm_Initialize()


    Set colNavigationBar = New Collection


    Dim NavigationBar As clsNavigationBar
    Set NavigationBar = New clsNavigationBar
    Set colNavigationBar = NavigationBar.BuildNavigationBar(Me.Frame1)
 
End Sub