ToolsMacrosSecurity menu not available! (2000 SR1a)

Topic

New Reply

Hi All,

I know I’ve seen this one before but since the SEARCH feature is still not working here, I guess I have to ask again. I’ve just reinstalled WIndows2000 and Office 2000 from scratch. I migrated my personal.xls file and my Excel.xlb file, etc. to make sure I didn’t lose my custom menus, etc. I keep getting asked about whether or not I wish to allow enable macros in my custom menu worksheets. I don’t seem to have the option to disable the warning, or in other words, my Tools/Macros/Security menu does not exist!!

Gee, while I’m here, I may as well toss this question out too. I have my Personal.xls worksheet loading automatically everytime I start Excel. However, when I go to launch one of my custom menu items (open a particular worksheet I use everyday), I get a warning about not being able to open two instances of the same file, namely, Personal.xls. I am not sure why Excel is trying to open it twice. I never had that problem before. Perhaps I copied the Personal.xls file into my XLStart folder and I didn’t need to. Hmmm. Any thoughts, while I go check that one out?

Drew

Reply | Quote

Replies

Check in which folders you have copies of Personal.xls. You should have only one in the startup folders used by Excel (the standard startup folder XLSTART and the optional alternate startup location you can specify in Tools | Options…)

Reply | Quote

Thanks HansV, but when I checked, there still seemed to be only the one copy of personal.xls around (I had removed the alternate versions I had). So, since that had no effect, I tried your next suggestion.

Drew

Reply | Quote

Thanks HansV, but when I checked, there still seemed to be only the one copy of personal.xls around (I had removed the alternate versions I had). So, since that had no effect, I tried your next suggestion.

Drew

Reply | Quote

Have you tried resetting the menu bar?
– Select Tools | Customize… or right click a toolbar, then select Customize…
– Activate the Toolbars tab.
– Select the Worksheet Menu Bar.
– Click Reset and confirm.
– Close the Customize dialog.
Is Tools | Macro | Security available now?

Reply | Quote

When I tried resetting the toolbar, I did indeed get back the Macro Security option and was able to change the security level. HOWEVER, by doing so, I had LOST all my custom menus!!! Aaarrrggghh!. I hadn’t really expected that. My custom toolbars were still there, but my menu with the 25 entries in it were blown away. Needless to say, it was a bittersweet victory. I got the security setting available but lost my custom work. Hmmmm. I’ll be a little more cautious next time. Of course, I suppose I could have made the change to my security setting and then reinstated a backup of my personal.xls, but I hadn’t thought of THAT!! Duh.

Thanks for you help. It is most appreciated.

Drew

Reply | Quote

The blowing away of your customisations is exactly why I normally advise to create a *separate* menu with your customisations, which can be attached to the workbook they belong to. That way, if you ever lose your xlb file (the fole that holds Excel’s toolbar and menu customisations), you can easily get your menu back by just loading your file.

If you *do* want to customise existing menues, make sure you do it with VBA code (and get rid of them once the relevant workbook closes). Like this:

Dim cControl as commandbarcontrol

Sub MakeMenu()
On Error Resume Next
RemoveMenu ‘Prevent duplicate menu’s
Set cControl = Application.CommandBars(1).FindControl(Id:=30007).Controls.Add(temporary:=True)
cControl.Caption = “&Your Menu”
cControl.OnAction = “YourMacro”
End Sub

Sub RemoveMenu()
On Error Resume Next
If cControl Is Nothing Then
Application.CommandBars(1).FindControl(Id:=30007).Controls(“Your Menu”).Delete
Else
cControl.Delete
Set cControl = Nothing
End If
End Sub

Reply | Quote

The blowing away of your customisations is exactly why I normally advise to create a *separate* menu with your customisations, which can be attached to the workbook they belong to. That way, if you ever lose your xlb file (the fole that holds Excel’s toolbar and menu customisations), you can easily get your menu back by just loading your file.

If you *do* want to customise existing menues, make sure you do it with VBA code (and get rid of them once the relevant workbook closes). Like this:

Dim cControl as commandbarcontrol

Sub MakeMenu()
On Error Resume Next
RemoveMenu ‘Prevent duplicate menu’s
Set cControl = Application.CommandBars(1).FindControl(Id:=30007).Controls.Add(temporary:=True)
cControl.Caption = “&Your Menu”
cControl.OnAction = “YourMacro”
End Sub

Sub RemoveMenu()
On Error Resume Next
If cControl Is Nothing Then
Application.CommandBars(1).FindControl(Id:=30007).Controls(“Your Menu”).Delete
Else
cControl.Delete
Set cControl = Nothing
End If
End Sub

Reply | Quote

When I tried resetting the toolbar, I did indeed get back the Macro Security option and was able to change the security level. HOWEVER, by doing so, I had LOST all my custom menus!!! Aaarrrggghh!. I hadn’t really expected that. My custom toolbars were still there, but my menu with the 25 entries in it were blown away. Needless to say, it was a bittersweet victory. I got the security setting available but lost my custom work. Hmmmm. I’ll be a little more cautious next time. Of course, I suppose I could have made the change to my security setting and then reinstated a backup of my personal.xls, but I hadn’t thought of THAT!! Duh.

Thanks for you help. It is most appreciated.

Drew

Reply | Quote

Have you tried resetting the menu bar?
– Select Tools | Customize… or right click a toolbar, then select Customize…
– Activate the Toolbars tab.
– Select the Worksheet Menu Bar.
– Click Reset and confirm.
– Close the Customize dialog.
Is Tools | Macro | Security available now?

Reply | Quote