hiding macro toolbar

[viper]

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,

[keithkemble]

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.

[viper]

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

[Ivan F Moala]

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

[RWinfield]

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

[viper]

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.

[RWinfield]

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

[viper]

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,

[RWinfield]

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

[Ivan F Moala]

On 2002-02-17 21:39, RWinfield wrote:

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