hiding macro toolbar

viper

Active Member
Joined
Feb 15, 2002
Messages
382
I know I've seen the code on here before but I cannot find it now. I am looking for a way to turn off the users macro in their tools menu when my spreadsheet is opened. How is this done?

Thanks,
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
I don't think you can.
You can hide the code and stop access of the code if a Macro bombs out by going to vba editor ( alt F11) selecting Tools/properties
select protection tab and enter a password.

when you next load up the model users will be able to run macros but will not be able to alter anything.
 
Upvote 0
Thanks,

I don't know how it's done, but on my work computer, we don't have access to macros at all, I'm assuming that you can make macros unavailable to users when users starts up but maybe not just individual workbooks. But you're right. I did password my project, but I just want to make double sure my macros are protected from manipulating sources.

Thanks again,
viper
 
Upvote 0
On 2002-02-16 10:39, keithkemble wrote:
I don't think you can.
You can hide the code and stop access of the code if a Macro bombs out by going to vba editor ( alt F11) selecting Tools/properties
select protection tab and enter a password.

You can actually disable these controls
like so........

Dim CB As CommandBar
Dim C As CommandBarControl
Dim ID

Sub Disable_RightClick_ToolBar()
ID = 30017
For Each CB In Application.CommandBars
Set C = CB.FindControl(ID:=ID, recursive:=True)
If Not C Is Nothing Then C.Enabled = False
Next
Application.CommandBars("toolbar list").Enabled = False
Application.CommandBars("Visual Basic").Enabled = False
End Sub

Sub Enable_RightClick_ToolBar()

ID = 30017
For Each CB In Application.CommandBars
Set C = CB.FindControl(ID:=ID, recursive:=True)
If Not C Is Nothing Then C.Enabled = True
Next

Application.CommandBars("Toolbar List").Enabled = True
Application.CommandBars("Visual Basic").Enabled = True

End Sub



or something along these lines.....don't think I captured all of it...


Ivan
 
Upvote 0
Right click on the excel icon next to FILE on the toolbar choose VIEW CODE and paste the following code into the workbook module.

Private Sub Workbook_Open()
Application.CommandBars("Tools").Controls(11).Enabled = False
End Sub

Private Sub Workbook_Deactivate()
Application.CommandBars("Tools").Controls(11).Enabled = True
End Sub

This will gray out the Macro choice on the Tools menu as the workbook is opened, and will return it after the workbook is closed.

This will not prevent all access to the macro, but is kind of like a padlock...Keeps honest people honest, but doesn't really stop anyone with a bit of dedication :)

Rick
 
Upvote 0
Rick,
Thanks. But a question? Does it only work on other computers? I pasted it as you stated and then saved and reopened but didn't gray out my macros.
 
Upvote 0
Interesting because it works fine here. Did you save the changes to the workbook when you saved it? I pasted the two macros into a new workbook, closed the workbook and answered YES to the save changes prompt, and then reopened the workbook. The macro choice on the tool menu is grayed out on mine.

Rick
 
Upvote 0
I just tried it on a second copy of the file and it didn't work either. But thanks, I will work with it and get it working. At least I know it can be done and that makes this project a little bit safer.

Again thanks,
 
Upvote 0
On 2002-02-17 21:33, viper wrote:
I just tried it on a second copy of the file and it didn't work either. But thanks, I will work with it and get it working. At least I know it can be done and that makes this project a little bit safer.

Again thanks,

The only thing I can think of is possibly we are using different versions of Excel. I am using Excel 2000 on Win98. Hope you get it sorted out.

Rick
 
Upvote 0
On 2002-02-17 21:39, RWinfield wrote:
On 2002-02-17 21:33, viper wrote:
I just tried it on a second copy of the file and it didn't work either. But thanks, I will work with it and get it working. At least I know it can be done and that makes this project a little bit safer.

Again thanks,

The only thing I can think of is possibly we are using different versions of Excel. I am using Excel 2000 on Win98. Hope you get it sorted out.

Rick

Your code uses and index = 11 = 11th item
on the tool list....if there are any other
items on this list that are NOT std then
it will grey out the 11 item weather that is
the macro button or not......thats why I
used the command ID number in my code posted earlier.


Ivan
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,913
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top