Help with code please

[michael.beckinsale]

Hi All,

Can anybody tell me whats wrong with this code / why it does not work.

The idea is that administrators will have access to a set of macros
listed in a menu but will not be able to access the menu list without
a password. It works without the input box, but l would like to have
the password entered and then the menu drop down list displayed. All
help gratefully appreciated.

Private Sub Workbook_Open()
Dim michaelsmenu As CommandBarControl
Set michaelsmenu = Application.CommandBars("Worksheet Menu
Bar").Controls.Add(Type:=msoControlPopup, Before:=8)
michaelsmenu.Caption = "&Michael"
michaelsmenu.BeginGroup = False
michaelsmenu.Visible = True
michaelsmenu.OnAction = "Pwd"


Sub Pwd()

Dim MyPwd As String

MyPwd = InputBox("Please enter your password (case sensitive)",
"Password required")
If MyPwd <> "Michael9855" Then
MsgBox ("Sorry thats incorrect")
Exit Sub
End If

'This code works if l disable the input box!!!!!
Dim mymenu1 As CommandBarControl
Set mymenu1 = Application.CommandBars("Worksheet Menu
Bar").Controls("&Michael").Controls.Add(Type:=msoControlButton)
mymenu1.Visible = True
mymenu1.Style = msoButtonCaption
mymenu1.Caption = "Shade Alternate Rows"
mymenu1.OnAction = "Shade_Alternate_Rows"

End Sub

Regards

Michael

 

[Bob Phillips]

It worked fine for me but I had to add a bit to get at the new menu item


Public Sub Pwd
Dim MyPwd As String

MyPwd = InputBox("Please enter your password (case sensitive)",
"Password required")
If MyPwd <> "Michael9855" Then
MsgBox "Sorry thats incorrect", vbCritical, "Invalid password"
Exit Sub
End If

'This code works if l disable the input box!!!!!
Dim mymenu1 As CommandBarControl
Set mymenu1 =
Application.CommandBars(1).Controls("&Michael").Controls.Add(Type:=msoControlButton)
mymenu1.Visible = True
mymenu1.Style = msoButtonCaption
mymenu1.Caption = "Shade Alternate Rows"
mymenu1.OnAction = "Shade_Alternate_Rows"
Application.CommandBars.ActionControl.OnAction = ""

End Sub



--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[michael.beckinsale]

Hi Bob,

Thanks for that. Not sure that l really understand it but it works!

To complete this bit of the project l need to refine the code a little
bit more, as follows:

1) Is it possible to add some more code that will leave the menu
'expanded' (eg as if the user has clicked on the menu and he list of
options is revealed?

2) Once a choice has been made to run 1 of the macro options, or if
the user chooses to exit, the menu should be reset so that when it is
clicked on again it will require the password to be re-entered

I would appreciate any help you could give me to achieve this.

Regards

Michael