Access toolbars

[vbgladiator]

Hey,

Does anyone know how to disable the toolbars in an Access application?

Setting it to nothing for each form, sets the default toolbar.

I want the user to have the Menu Bar only, no toolbar on all the forms.

I have the custom menus, but can't get rid of the toolbars.

[alex_read]

Yep, Tools Menu > Startup & you've got checkboxes to allow these menus.
The user can get round this if they hold shift down while opening the database though. If you need the code to hide them instead, let me know.

[vbgladiator]

Thanks. I have the code to disallow the bypass key.

Thanks again.

[kleinma]

if you disable holding shift when opening a DB.. then how do you go back and edit it?

[vbgladiator]

I have a Debug property that allows the .mdb file to be edited. But only the .mdb. When the client gets the .mde, the program checks and restarts if any properties are changed.

[alex_read]

Wow I've just been asked about a 6 year old post Here you go Malcolm. I've posted the code here incase others find it of use. Have tested with Access 2003 (2007 has the ribbon, so although this doesn't error it doesn't perform the same routine as you can imagine). Please note, whilst I have commented this, I haven't put any error handling in so you will need to perform this step.

Option Compare Database
Option Explicit

' Define show/hide options to expose from the below, allowing the exposure of only
' a single method to perform the toolbar display toggle.
Public Enum ToolbarDisplayOption
    ShowToolbars = 0
    HideToolbars = 1
End Enum

' These 2 keep track of the toolbars which the
' below code hides so they can be restored later.
Private m_toolbarsShownCount As Integer
Private m_toolbarsOriginallyShown() As String


Public Sub ToggleToolbarDisplay(ByVal displayOption As ToolbarDisplayOption)
' **** Method to hide or show all the toolbars in the current Access application. ****
    Dim curEvalCommandBar As Integer
    
    ' Evaluate whether the operation to perform is to hide or show the toolbars.
    Select Case displayOption
    Case ToolbarDisplayOption.HideToolbars
        Dim commandBarCount As Integer
        commandBarCount = Application.CommandBars.Count
        m_toolbarsShownCount = -1
        
        ' If we are hiding the toolbars, then loop through each one in turn.
        For curEvalCommandBar = commandBarCount To 1 Step -1
            ' Is the toolbar shown on screen now and isn't the menu bar?
            If (Application.CommandBars(curEvalCommandBar).Visible = True) _
            And (LCase(Trim(Application.CommandBars(curEvalCommandBar).Name)) <> "menu bar") Then
                ' If so, add the name of the toolbar to the module-level string array of
                ' hidden toolbars to be restored, and increment the count of these toolbars
                ' to later restore by setting another module-level variable also.
                m_toolbarsShownCount = m_toolbarsShownCount + 1
                ReDim Preserve m_toolbarsOriginallyShown(m_toolbarsShownCount)
                m_toolbarsOriginallyShown(m_toolbarsShownCount) = _
                Application.CommandBars(curEvalCommandBar).Name
                
                ' Finally, hide the currently evaluated toolbar, then evaluate the next ...
                Application.CommandBars(curEvalCommandBar).Visible = False
            End If
        Next curEvalCommandBar
        
    ' If we are showing the toolbars again ...
    Case ToolbarDisplayOption.ShowToolbars
        ' Firstly persorm a sanity check - that we have any to show again. If so, iterate
        ' through those to be restored (the names of which are held within the privately
        ' declared m_toolbarsOriginallyShown string array).
        If (UBound(m_toolbarsOriginallyShown) >= 0) Then
            For curEvalCommandBar = m_toolbarsShownCount To 0 Step -1
                If Not Trim(m_toolbarsOriginallyShown(m_toolbarsShownCount)) = "" Then
                    ' Show the toolbar again, then decrement the private array element count
                    ' and number of toolbars to show (the 2 module-level variables).
                    Application.CommandBars(m_toolbarsOriginallyShown(curEvalCommandBar)).Visible = True
                    ReDim Preserve m_toolbarsOriginallyShown(curEvalCommandBar)
                    m_toolbarsShownCount = curEvalCommandBar
                End If
            Next curEvalCommandBar
        End If
    End Select
End Sub

[RobDog888]

Thread Moved

There are many ways to implement security which will prevent the use of the Shift key too.

See my FAQ item for more.
http://www.vbforums.com/showthread.php?t=402072

and preventing the Shift key.
http://www.vbforums.com/showthread.php?t=402071

[alex_read]

Sorry Rob, wasn't really paying any attention to the forum this was originally posted in there or I'd have ased for it to be moved. Another nice tutorial there, didn't know you had that one up.

[RobDog888]

Np, as it was an old thread probably before the VBA forum was created.

Thanks and I have more in my OD FAQ (link in my signature) but I need to write some updates for it soon.

[malcolmk]

Thanks a bunch, I now got the thing doing what I want it to, now if I could only switch on / off individual menu items.....
I really have to get down and study access vba and its great that there are places like this forum to turn to for a wealth of knowledge. Sometimes looking at the ms site and reading the explanations is like wading through treacle. The simpler the sample the better thats what I say.
Thanks again Alex.

[alex_read]

Access exposes each toolbar as something called a commandbar (why they didn't use the word toolbar I don't know). Each commandbar is contained within a collection, which is known as commandbars in Access VBA.

In tha above code, the commandbars collection is looped through, and each individual commandbar is evaluated (in that case, to see if it's visible on screen).

In the same fashion, a commandbar is a grouping, or more professionally, a collection, of controls - toolbar buttons to you and me in this instance. If you alter the above sample like so, you can iterate through each of the individual commandbar's controls collection. In this below code, the text of each button encountered is simply output to the debug immediate window, but you can modify this as you need:

If (Application.CommandBars(curEvalCommandBar).Visible = True) _
            And (LCase(Trim(Application.CommandBars(curEvalCommandBar).Name)) <> "menu bar") Then
                Debug.Print "**" & Application.CommandBars(curEvalCommandBar).Name
                Dim i As Integer
                For i = 1 To Application.CommandBars(curEvalCommandBar).Controls.Count
                    Debug.Print "  " & Application.CommandBars(curEvalCommandBar).Controls(i).Caption
                Next i
                Debug.Print vbCrLf

[RobDog888]

You would want to identify the commandbar ID# (the number is static throughout all versions of Access) so you can access the commandbar directly for hiding or disabling etc.

Check the first code example where I show how to obtain the number.
http://www.vbforums.com/showthread.php?t=402045

[alex_read]

Cool thanks for confirming that!!!

I wasn't sure myself which is why I entered the names directly (which oddly still works although the intellisense only provides the explaination for the ID referencing - thought what the hell and tried it anyway there & guess it's an overloaded method).

I noticed when writing the above that whichever way you show/hide the Access toolbars, they always appear back in the same order by default so was wondering about this but it's cool to have it confirmed. Learnt something else new today.