Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
split post code (zip code) out of cell that includes full address | Excel Discussion (Misc queries) | |||
Shorten code to apply to all sheets except a few, instead of individually naming them, and later adding to code. | Excel Programming | |||
Protect Sheet with code, but then code will not Paste error. How do i get around this. Please read for explainations.... | Excel Programming | |||
Modification in the CODE to HIDE rows and columns that start with ZERO (code given) | Excel Programming | |||
Excel code convert to Access code - Concat & eliminate duplicates | Excel Programming |