Hide/Display SubMenu Buttons

[Denburt]

This code is for a Toggle Button layout on a form, with this code you can set a number of toggle buttons visible and have multiple submenus that will stay hidden when not in use. My main menu is set up with this code and the way I set it up the first button is my search toggle button no submenu (this prevents a submenu from displaying when the form is first opened) TabIndex set to 0 (I like using the keyboard) the second toggle button is called Forms, the forms toggle button has submenu buttons for the forms I want displayed. The TabIndex can be a bit mundane at times trying to keep things in order but the nice thing is that in Design view of this form you can put the buttons any where you want and they will position themselves when they are called. I like having the tab index structured like so, the search button would be 0 Forms button would be 1 the first submenu button would be 2 the next submenu = 3 continue until all submenu buttons have their index then back to the menus next main button.

I placed the following in a module I keep for a majority of my form code. You may want to rename the bold statement to your TabIndex=0 control

Option Compare Database
Option Explicit
 
Public myDetVal As Integer
Public XVal As Integer
Public myX As Integer
Public myY As Integer
 
Public Sub FocusYN(frm As Form, Optional strCtlName As String)
Dim ctl As Control
Dim GrpInt As Integer, i As Integer, cnt As Integer, myInt As Integer, inInt As Integer
Dim GrpNme() As Variant, GrpOrd() As Variant
Dim FndIt As Boolean
On Error GoTo FocusYNErr
If strCtlName <> "Detail" Then
    If frm(strCtlName).Tag <> "" Then
        GrpInt = Right(frm(strCtlName).Tag, 1)
        frm(strCtlName).Value = 1
    Else
        frm(strCtlName).Value = 0
    End If
    frm(strCtlName).SetFocus
    myDetVal = 0
Else
    frm!Search.SetFocus
End If
 
ReDim Preserve GrpOrd(myInt)
For Each ctl In frm.Controls
    If ctl.ControlType = 122 Then 'also known as ControlType acToggleButton
        If strCtlName = "Detail" Then frm(ctl.Name).Value = 0
        If GrpInt = Right(frm(ctl.Name).Tag, 1) And Left(frm(ctl.Name).Tag, 1) <> "V" Then
            If ctl.Name <> strCtlName Then
                inInt = InStr(frm(ctl.Name).Tag, ";")
                If Left(frm(ctl.Name).Tag, inInt - 1) - 1 > myInt Then
                    myInt = Left(frm(ctl.Name).Tag, inInt - 1) - 1
                    ReDim Preserve GrpOrd(myInt)
                End If
                GrpOrd(Left(frm(ctl.Name).Tag, inInt - 1) - 1) = ctl.Name
            End If
        Else
            If ctl.Name <> strCtlName And frm(ctl.Name).Value <> 0 Then
                frm(ctl.Name).Value = 0
            End If
            If frm(ctl.Name).Tag <> "" And Not frm(ctl.Name).Tag Like "V*" Then
                frm(ctl.Name).Visible = False
            End If
        End If
    End If
Next
 
For i = 0 To UBound(GrpOrd)
    Set ctl = frm(GrpOrd(i))
    If ctl.ControlType = 122 And strCtlName <> "DETAIL" Then 'also known as ControlType acToggleButton
     Select Case i
        Case 0
            ctl.Top = frm(strCtlName).Top
            ctl.Left = frm(strCtlName).Left + frm(strCtlName).Width
        Case 1 To 6
            ctl.Top = frm(GrpOrd(i - 1)).Top + frm(GrpOrd(i - 1)).Height
            ctl.Left = frm(strCtlName).Left + frm(strCtlName).Width
        Case 7
            ctl.Top = frm(strCtlName).Top
            ctl.Left = ((frm(strCtlName).Left + frm(strCtlName).Width) + frm(strCtlName).Width)
        Case 8 To 13
            ctl.Top = frm(GrpOrd(i - 1)).Top + frm(GrpOrd(i - 1)).Height
            ctl.Left = ((frm(strCtlName).Left + frm(strCtlName).Width) + frm(strCtlName).Width)
        Case 14
            ctl.Top = frm(strCtlName).Top
            ctl.Left = (((frm(strCtlName).Left + frm(strCtlName).Width) + frm(strCtlName).Width) + frm(strCtlName).Width)
        Case 15 To 20
            ctl.Top = frm(GrpOrd(i - 1)).Top + frm(GrpOrd(i - 1)).Height
            ctl.Left = (((frm(strCtlName).Left + frm(strCtlName).Width) + frm(strCtlName).Width) + frm(strCtlName).Width)
        Case Else
            MsgBox "Check your controls or your code there appears to be more buttons than 3 rows of 7 in each!"
    End Select
    frm(GrpOrd(i)).Visible = True
    End If
SkipIt:
Next
Set ctl = Nothing
 
FocusYNExit:
Exit Sub
FocusYNErr:
If Err.Number = 2100 Then
    MsgBox "Your form is too small to show all of the buttons!"
    Exit Sub
ElseIf Err.Number <> 0 Then
    MsgBox Err.Number & "   " & Err.Description
End If
End Sub
 

To Effectively show & hide the buttons you will need to place a value in the tag properties of the main button that will control the buttons to be shown or hidden. For Example 1 main button on my menu form is called "Forms" it has a tag property = VGrp1 (V for always visible and I used the letters Grp to relay which group it belongs too). The submenu buttons of this Forms button should have a tag property starting with a number for the order you want them displayed starting at the top working your way down then to the right and separated by a semi colon they should be numbered starting with 1 in the order that you want them listed such as the “Add Invoice” button has a tag property = 1;Grp1 the next button following below has a tag property = 2;Grp1 (Button 2 of the same group)! The Menu next main button would have a tag of VGpr2 and the submenu buttons would be tagged with 1;Grp2,2;Grp2 etc. up to 21 buttons 3 rows of 7.

Now for the rest of the code in the forms module:

Option Compare Database
Option Explicit
 
Private Sub Form_Load()
myDetVal = 0
XVal = 1
myX = 1
Detail_MouseMove 1, 0, 1, 1
End Sub
 
Private Sub Detail_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
If myDetVal = 0 Then
    If XVal = 0 Then
        myX = X
        myY = Y
        XVal = 1
    Else
        If myY > 0 Then
            If myY - Y > 100 Or myY - Y < -100 Then
                FocusYN Me, "Detail"
                myDetVal = 1
            End If
        End If
        If myX > 0 Then
            If myX - X > 100 Or myX - X < -100 Then
                FocusYN Me, "Detail"
                myDetVal = 1
            End If
        End If
    End If
End If
End Sub
Private Sub Search_GotFocus()
    Detail_MouseMove 1, 0, 1, 1
End Sub
 
Private Sub Search_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
Detail_MouseMove 1, 0, 1, 1
End Sub
Private Sub Toggle1_GotFocus()
    If Me!Toggle1 = 0 Then
        FocusYN Me, "Toggle1"
    End If
End Sub
 
Private Sub Toggle1_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
If Me!Toggle1 = 0 Then
    Me!Toggle1.SetFocus
    FocusYN Me, "Toggle1"
End If
End Sub
Private Sub Toggle3_GotFocus()
    If Me!Toggle3 = 0 Then
        FocusYN Me, "Toggle3"
    End If
End Sub
 
Private Sub Toggle3_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
If Me!Toggle3 = 0 Then
    Me!Toggle3.SetFocus
    FocusYN Me, "Toggle3"
End If
End Sub