Acc:97 Disable menu bar and tool bar based on user, using VBA 4

[JustLJ]

Topic: Disable menu bar and tool bar based on user, using VBA
-> Using Access 97 <-

Greetings.

With the help of this forum, I've been able to distinguish a user, and open the appropriate form and then suppress the database access window in our shared application.

Now, I need to turn off the menu bar and the tool bar for some of the users. The problem is, they can get so some pre-built query screens and report generating screens that others can get to as well. So I can't turn off the menu bar or tool bar based on the form.

What I am looking for is a way to turn off the menu bar and the tool bar once the user it identified as being one of the inquiry folks, prior to the first screen opening and staying that way until they exit the application. So, is there a way to do it at the application level?

I tried the [DoCmd.ShowToolbar &quot;menu bar&quot;, acToolbarNo] action, and it works on the menu for the first screen, but not after that.

I saw some code from Maquis, which sets some properties, but that requires a close/reopen process that our network will not allow.

I saw some tips about commandbars.visible = false, but that doesn't work for me.

I've seen lots of comments in the forums from people saying they have the toolbars off and want to do something more… how did you do it???

Again, I don't want them to have a menu bar or tool bar at all, and they will share some screens with other folks, so I can't turn off these items at the form-level.

Thanks for your time and thoughts on this!
LJ

 

[jebry]

Hi LJ!

Actually, if you take away the menu bar and toolbar completely, some of your users may panic thinking that they broke the app. Granting that panic actually improves the thought processes of some people, it is still best to avoid it for the general populace. I would recommend building your own custom tool bar and menu bar then, in the open events for the forms in question, set the toolbar and menubar properties to your custom bars. Depending on the user of course.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[JustLJ]

Thanks Jeff, but it is a requirement that they not have access to anything (past history...). They are used to not having toolbars from Excel apps -- but the Excel coding to turn those off doesn't work in Access.

If there was a way to dynamically use the custom toolbar as they go from screen to screen, that could be helpful. But again, they will have the ability to go to some shared report screens (150 reports, so we don't want to have multiple copies of the screen) and the same with specialized queries.

Maybe there is a way to &quot;pop-up&quot; those other screens and in that process turn off the toolbars?

Thanks for any and all thoughts!

LJ

 

[SgtJarrow]

Jebry is right in the custom toolbar thing.....the easiest way to do this is to create a BLANK toolbar and use this as your custom tool bar. You will have effectively turned off the toolbar, but not have to go through all kinds of coding. Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need!

Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer
robert.l.johnson.iii@citigroup.com

 

[jebry]

Hi again!

Here is some code I wrote to do what you are asking:

If Forms!frmParameters!SecurityLevel = &quot;Student&quot; Then
Me.ShortcutMenu = False
Me.Toolbar = &quot;StudentToolbar&quot;
Me.MenuBar = &quot;StudentMenubar&quot;
End If

My frmParameters is where I store information about the user and some other variables that need to remain static even after the app is closed(I store them in a table). As you can see I have defined my own toolbars for students and I run this code in the form open procedure of every form they can get to(actually, I have it in every form just in case of an error when updating the FE).

hth
Jeff Bridgham
bridgham@purdue.edu

 

[VBslammer]

I would make a limited Menu bar to replace the default bar (hold down CTRL while you drag the menu items to your custom menu bar, and make sure to set it's type to 'Menu Bar') with the File, Edit and Help items.

I played with this code and got it to work on Access 2000. It works by using a startup form (could be a custom login form or a splash screen) and after changing the menu items it gets hidden rather than closed so when the user shuts down the application it can restore the menus before it unloads.

'@-------------- Startup Form -----------------@
'	     (hide instead of close)

Option Compare Database
Option Explicit

' variable to hold class info.
Private mclsMenus As New clsMenuHandler

Private Sub cmdOK_Click()
  Me.Visible = False
End Sub

Private Sub Form_Load()
  ' this will need to reflect the code you use
  ' to check the current user's ID...
  If Application.CurrentUser <> &quot;Admin&quot; Then
    ' This call assigns your custom main menu
    ' bar so the default bar can be hidden.
    Call ProcessMenus(&quot;MyMenuBar&quot;)
  End If
End Sub

Private Sub Form_Unload(Cancel As Integer)
  ' This call restores the original settings.
  Call ProcessMenus(&quot;&quot;, True)
End Sub

Public Sub ProcessMenus(ByVal strCustomMenu As String, _
                        Optional ByVal blnRestore As Boolean = False)
On Error GoTo ErrHandler
  Dim cbr As CommandBar
  
  If blnRestore Then
    If mclsMenus.TotalBars > 0 Then
      mclsMenus.RestoreMenus
    End If
  Else
    ' triggers error if invalid menu bar.
    Set cbr = Application.CommandBars(strCustomMenu)
    
    mclsMenus.CustomMenu = strCustomMenu
    mclsMenus.HideMenus
  End If

ExitHere:
  Exit Sub
ErrHandler:
  Debug.Print Err & &quot;-&quot; & Err.Description
  Resume ExitHere
End Sub  


'@----------  CLASS: clsMenuHandler  ---------------@

Option Compare Database
Option Explicit

Private mcolBarInfo As Collection
Private mstrCustomMenu As String

Public Property Get CustomMenu() As String
  CustomMenu = mstrCustomMenu
End Property

Public Property Let CustomMenu(ByVal strCustomMenu As String)
  mstrCustomMenu = strCustomMenu
End Property

Public Property Get TotalBars() As Integer
  TotalBars = mcolBarInfo.Count
End Property

Public Property Get Item(ByVal varIndex As Variant) As clsMenuInfo
  On Error Resume Next
  Set Item = mcolBarInfo(varIndex)
End Property

Private Sub Class_Initialize()
  Set mcolBarInfo = New Collection
End Sub

Private Sub Class_Terminate()
  Set mcolBarInfo = Nothing
End Sub

'********************
'*  §lamKeys §oftware 2000® (mailto: slamkeys@ev1.net)
'*
'*  @CREATED  :   11/7/2002 4:58:48 AM
'*  @NOTES    :
'*  @MODIFIED :
'********************
Public Sub HideMenus()
On Error GoTo ErrHandler
  Dim cbr As CommandBar
  Dim myCbr As CommandBar
  Dim mi As clsMenuInfo
  Dim intProt As MsoBarProtection
  
  If Len(CustomMenu) = 0 Then Exit Sub
  If Me.TotalBars > 0 Then Exit Sub
  
  ' need to set this or can't hide default 'Menu Bar' menu.
  Application.MenuBar = CustomMenu
  
  Set myCbr = CommandBars(CustomMenu)
  
  With myCbr
    .Visible = True
    .Position = msoBarTop
    .RowIndex = msoBarRowFirst + 1
    .Left = 0
    .Protection = msoBarNoCustomize Or msoBarNoChangeVisible
  End With
  
  On Error Resume Next
 
  For Each cbr In Application.CommandBars
    If (cbr.Name <> myCbr.Name) And (cbr.Type <> msoBarTypePopup) Then
      
      Set mi = New clsMenuInfo
      
      mi.BarProtection = cbr.Protection
      
      If Err.Number = 0 Then
      
        With mi
          .BarName = cbr.Name
          .BarEnabled = cbr.Enabled
          .BarPosition = cbr.Position
          .BarProtection = cbr.Protection
          .BarVisible = cbr.Visible
        End With
        
        mcolBarInfo.Add mi, mi.BarName
        
        cbr.Protection = msoBarNoProtection
        
        If cbr.Visible = True Then
          cbr.Visible = False
        End If
        cbr.Enabled = False
        cbr.Protection = msoBarNoCustomize Or msoBarNoChangeVisible
      Else
        Err.Clear
      End If
      
    End If
  Next cbr

ExitHere:
  On Error Resume Next
  Set cbr = Nothing
  Set myCbr = Nothing
  Set mi = Nothing
  Exit Sub
ErrHandler:
  Debug.Print &quot;Error: &quot; & AccessError(Err)
  Resume Next
End Sub

'********************
'*  §lamKeys §oftware 2000® (mailto: slamkeys@ev1.net)
'*
'*  @CREATED  :   11/7/2002 4:58:25 AM
'*  @NOTES    :
'*  @MODIFIED :
'********************
Public Sub RestoreMenus()
On Error GoTo ErrHandler
  Dim cbr As CommandBar
  Dim myCbr As CommandBar
  Dim mi As clsMenuInfo
  Dim i As Integer
  
  If Len(CustomMenu) = 0 Then Exit Sub
  If Me.TotalBars = 0 Then Exit Sub
  
  ' Restore default Menu Bar.
  Application.MenuBar = &quot; &quot;
  
  Set myCbr = CommandBars(CustomMenu)
  
  With myCbr
    .Protection = msoBarNoProtection
    .Visible = False
  End With
  
  On Error Resume Next
 
  For i = mcolBarInfo.Count To 1 Step -1
    
    Set mi = mcolBarInfo(i)
    Set cbr = CommandBars(mi.BarName)
    
    cbr.Position = mi.BarPosition
    cbr.Enabled = mi.BarEnabled
    cbr.Protection = mi.BarProtection
    cbr.Visible = mi.BarVisible
    
    mcolBarInfo.Remove i
      
  Next i
  
ExitHere:
  On Error Resume Next
  Set cbr = Nothing
  Set myCbr = Nothing
  Set mi = Nothing
  Exit Sub
ErrHandler:
  Debug.Print &quot;Error: &quot; & AccessError(Err)
  Resume Next
End Sub


'@------------  CLASS: clsMenuInfo  ----------------@

Option Compare Database
Option Explicit

Private bi As BarInfo

Private Type BarInfo
  Name As String
  Visible As Boolean
  Enabled As Boolean
  Position As MsoBarPosition
  Protection As MsoBarProtection
End Type

Public Property Get BarName() As String
  BarName = bi.Name
End Property

Public Property Let BarName(ByVal strBarName As String)
  bi.Name = strBarName
End Property

Public Property Get BarVisible() As Boolean
  BarVisible = bi.Visible
End Property

Public Property Let BarVisible(ByVal blnBarVisible As Boolean)
  bi.Visible = blnBarVisible
End Property

Public Property Get BarEnabled() As Boolean
  BarEnabled = bi.Enabled
End Property

Public Property Let BarEnabled(ByVal blnBarEnabled As Boolean)
  bi.Enabled = blnBarEnabled
End Property

Public Property Get BarPosition() As MsoBarPosition
  BarPosition = bi.Position
End Property

Public Property Let BarPosition(ByVal intBarPos As MsoBarPosition)
  bi.Position = intBarPos
End Property

Public Property Get BarProtection() As MsoBarProtection
  BarProtection = bi.Protection
End Property

Public Property Let BarProtection(ByVal intProt As MsoBarProtection)
  bi.Protection = intProt
End Property

'@-------------------------------------------------@

You can access the Commandbar properties in the mclsMenus variable like this:

mclsMenus.Item("Clipboard").BarEnabled = True
Debug.Print mclsMenus.Item(5).BarName

VBSlammer

Unemployed in Houston, Texas

 

[JustLJ]

Wow! I'll have to give these ideas a workout. I'll post back how things go.
Thanks a bunch!
LJ

 

[JustLJ]

Robert said:
<<mstrmage1768 (Programmer) Nov 7, 2002
Jebry is right in the custom toolbar thing.....the easiest way to do this is to create a BLANK toolbar and use this as your custom tool bar. You will have effectively turned off the toolbar, but not have to go through all kinds of coding. >>

Robert (or others...) Please tell me how to create a blank toolbar and blank menu. I like the idea of placing a custom item, but they aren't supposed to have anything at all.

When I try to add a custom menu, the least I seem to get is if I use &quot;new menu&quot;, but even that allows them to resize and exit. When I try to add a custom toolbar, the toolbar is still there, albeit with the blank grey spacer.
I'm trying to get to nothing shown.

Thanks for your tips!

 

[SgtJarrow]

Me, I went....View -> Toolbars -> Customize. I selected New, entered a name, and clicked Ok. I then added that menu to the form. It is blank except for the move bars...but you can turn those off in the startup options.... Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need!

Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer
robert.l.johnson.iii@citigroup.com

 

[JustLJ]

Slammer --
Getting an error on this line, both in Access 97 and Access 2000 (different machine); the line is:
>> Public Property Get Item(ByVal varIndex As Variant) As clsMenuInfo << and the error is &quot;User-defined type not defined&quot;.

Any thoughts?

Thanks.
LJ

 

[VBslammer]

Did you create the 2 class modules and name them 'clsMenuHandler' and 'clsMenuInfo?' The return type of the Property Get is 'clsMenuInfo' so that needs to be the name of the class module you create for all the code below the CLASS: clsMenuInfo line.

If you made the class modules correctly and still get the error, you might try declaring a module-level variable of type 'clsMenuInfo' in the 'clsMenuHandler' class:

Private m_mi As clsMenuInfo

The code disables all toolbars and menus, except for a custom main menu and the shortcut menus.

If you want to disable shortcut menus as well, add this to the HideMenus() proc:

CommandBars("Shortcut Menus").Enabled = False

And again in the RestoreMenus() proc:

CommandBars("Shortcut Menus").Enabled = True

If you end up losing all your toolbars during testing and can't get them back, you can restore them using this:

Sub ResetMenus()
On Error Resume Next
  Dim cbr As CommandBar
  For Each cbr In Application.CommandBars
    cbr.Protection = msoBarNoProtection
    cbr.Reset
  Next cbr
End Sub

VBSlammer

Unemployed in Houston, Texas

 

[JustLJ]

Hey Slammer;
I copied the code you posted, so I don't know if that created a class or not. I'm not deep enough into the coding world to understand classes and such.

Thanks for taking time to reply.
LJ

 

[VBslammer]

All you have to do is add 2 class modules and name them 'clsMenuInfo' and 'clsMenuHandler' - then paste the appropriate code into each class module.

This certainly isn't a beginner-level solution, but you asked if it could be done!

Good Luck, VBSlammer

Unemployed in Houston, Texas

 

[JustLJ]

Thanks Slammer, I'll do some research to figure out where that goes.
Thanks for your time!
LJ

 

[CVigil]

JustLJ, I think that the part you don't know is how to add a *class module*, as opposed to adding a (normal) module. It's probably such a basic thing to VBSlammer that he or she didn't think about your needing to be told -- and you'll see why in a minute .

I use Access 97, and can't vouch for Access 2000's menus, but I'll bet this item didn't change.

Go to the main database window, where you can see the tabs for Forms, Tables, and all the other datbase objects. It doesn't matter which tab you have on top. Go to the 'Insert' menu, and choose 'Class Module'. Paste the code given you into that. And make sure to name that module as indicated. You'll see the new class modules in the Modules tab of the datbase window, if you ever need to get to them again; their icons are slightly different from those of (normal) modules, too. The New button on the tab doesn't give you the option of choosing Class Module, but the Insert menu does, as does the drop-down list on the Insert button of the toolbar that is active when you are editing a module (of either type). So never fear! &quot;Yes, Virginia, there is a class module!&quot;

This paragraph is simply education:
--------------------------------------
Class modules and (normal) modules are essentially the same, and essentially different. You use the same kinds of code in class modules as you do in (normal) modules (plus some), but class modules give you *objects* you can use and create instances of. The code attached to a form is in a class module; and the class name is the name of the form. That is what lets you have multiple instances of a single form, through VB. A class module's Subs and Functions become methods and properties of the class. A TextBox is a class. A Label is a class. In a sense, &quot;class&quot; is like &quot;object&quot;, and so &quot;class modules&quot; could be called &quot;object modules&quot;.

&quot;Sometimes it's the simple steps...&quot; -- C Vigil =)
(Before becoming a member, I also signed on several posts as
&quot;JustPassingThru&quot; and &quot;QuickieBoy&quot; -- as in &quot;Giving Quick Answers&quot

 

[JustLJ]

Thank you C Vigil! I found that very information in help after searching for it after I replied to VBS.

Thank you very much for taking the time to explain in the post. Perhaps it will help the next person in need!

LJ

 

[CVigil]

You are totally welcome! And you're right -- future readers are an important reason for what we're doing here, too, and an important consideration as we ask our questions and reply to others'! That's *one* reason I get so detailed and long-winded-sometimes when I'm replying in the forums ;-> .
-- C Vigil =)
(Before becoming a member, I also signed on several posts as
&quot;JustPassingThru&quot; and &quot;QuickieBoy&quot; -- as in &quot;Giving Quick Answers&quot