Help with code please
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, Befo=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:=msoC ontrolButton) mymenu1.Visible = True mymenu1.Style = msoButtonCaption mymenu1.Caption = "Shade Alternate Rows" mymenu1.OnAction = "Shade_Alternate_Rows" End Sub Regards Michael |
Help with code please
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").Co ntrols.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" wrote in message ... 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, Befo=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:=msoC ontrolButton) mymenu1.Visible = True mymenu1.Style = msoButtonCaption mymenu1.Caption = "Shade Alternate Rows" mymenu1.OnAction = "Shade_Alternate_Rows" End Sub Regards Michael |
Help with code please
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 |
All times are GMT +1. The time now is 03:00 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com