Dim or Disable specific sub-menu items using VBA?

[Aaitaman Tamang]

NeaPa,

Following code in used for disable sub-menu items for specified user. The problem I am having is one of below user is not able see it (Mean disabled the sub-menu items) I want both of them to see sub-menu when they currently on database. Any there is anything wrong with this code which causing one user unable to use sub-menu items.

Dim blnShow As Boolean
 
    blnShow = Nz(StrComp(Me.MyName, "aman", vbTextCompare), True) = False
    With CommandBars("MyMenu").Controls(1)
        .Controls(1).Enabled = blnShow
        .Controls(2).Enabled = blnShow
        .Controls(3).Enabled = blnShow
    End With
 
    blnShow = Nz(StrComp(Me.MyName, "Ram", vbTextCompare), True) = False
        With CommandBars("MyMenu").Controls(1)
        .Controls(1).Enabled = blnShow
        .Controls(2).Enabled = blnShow
        .Controls(3).Enabled = blnShow
    End With

Prompt help appreciated
Thanks a lot

[MikeTheBike]

Hi

On the basis that strComp returns 0 (zero) when the match is found (seem the wrong way round to me - never used it before) then for two users as indicated I suggest this

Dim blnShow As Boolean
 
blnShow = Nz(StrComp(MyName, "aman", vbTextCompare), True) And Nz(StrComp("Ram", "aman", vbTextCompare), True)
 
With CommandBars("MyMenu").Controls(1)
    .Controls(1).Enabled = blnShow
    .Controls(2).Enabled = blnShow
    .Controls(3).Enabled = blnShow
End With

??

For more than two users I would have a lookup table in the back end and use DLookUp(), or more probably DCount() to deal with the Null case, to determine who cannot use the menu item.

MTB

[Aaitaman Tamang]

MTB,

Thanks a lot for sharing. I have already resolved this task, The following has been used instead of earlier one and working perfect

    Select Case Me.MyName
    Case "aman"
        With CommandBars("MyMenu").Controls(1)
            .Controls(1).Enabled = True
            .Controls(2).Enabled = False
            .Controls(3).Enabled = False
        End With
 
        With CommandBars("MyMenu").Controls(2)
            .Controls(1).Enabled = True
            .Controls(2).Enabled = True
 
        End With
 
    Case "Ram"
        With CommandBars("MyMenu").Controls(1)
            .Controls(1).Enabled = True
            .Controls(2).Enabled = True
            .Controls(3).Enabled = False
        End With
    End Select

Thanks a lot for again sharing

[NeoPa]

If you have two (multiple) users then use InStr() instead :

Dim blnShow As Boolean
 
blnShow = (InStr(1, ";aman;ram;", ";" & Me.MyName & ";", vbTextCompare) > 0)
With CommandBars("MyMenu").Controls(1)
    .Controls(1).Enabled = blnShow
    .Controls(2).Enabled = blnShow
    .Controls(3).Enabled = blnShow
End With

NB. Don't forget the separators (I used semi-colons (;) in this case) as otherwise you may find a user called "ama" is matched in error.