Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: hiding macro toolbar

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Jefferson City, Missouri
    Posts
    382
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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,

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Leicestershire, U K
    Posts
    160
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Jefferson City, Missouri
    Posts
    382
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  5. #5
    New Member
    Join Date
    Feb 2002
    Location
    Washington State
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Location
    Jefferson City, Missouri
    Posts
    382
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  7. #7
    New Member
    Join Date
    Feb 2002
    Location
    Washington State
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  8. #8
    Board Regular
    Join Date
    Feb 2002
    Location
    Jefferson City, Missouri
    Posts
    382
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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,

  9. #9
    New Member
    Join Date
    Feb 2002
    Location
    Washington State
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  10. #10
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •