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

Expand|Select|Wrap|Line Numbers

 
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:

Expand|Select|Wrap|Line Numbers

 
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

Hide/Display SubMenu Buttons

Similar Microsoft Access / VBA bytes

/bytes/development

/bytes/it

/bytes/about