How may I hide all the command bars without knowing their names nor index?
Tried with this:
Sub Macro1() ' For Each cbar In Application.CommandBars cbar.Visible = False Next End Sub
How may I hide all the command bars without knowing their names nor index?
Tried with this:
Sub Macro1() ' For Each cbar In Application.CommandBars cbar.Visible = False Next End Sub
Best Regards,
Carlos Paleo.
To every problem there is a solution, even if I dont know it, so this posting is provided "AS IS" with no warranties.
If Debugging is harder than writing a program and your code is as good as you can possibly make
it, then by definition you're not smart enough to debug it.
http://www.mugrs.org
CLOSE!! Try this...
Regards,For Each Cmd In CommandBars Cmd.Enabled = False Next
John
You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you
The major part of getting the right answer lies in asking the right question...
Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.
PS: As this affects all workbooks, to be on the safe side you should also have something like this
Private Sub Workbook_BeforeClose(Cancel As Boolean) For Each Cmd In CommandBars Cmd.Enabled = True Next End Sub
You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you
The major part of getting the right answer lies in asking the right question...
Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.
Hi John,
gee, worked great thanks.
Best Regards,
Carlos Paleo.
To every problem there is a solution, even if I dont know it, so this posting is provided "AS IS" with no warranties.
If Debugging is harder than writing a program and your code is as good as you can possibly make
it, then by definition you're not smart enough to debug it.
http://www.mugrs.org
Hi Carlos,
If you are giving the users the option of opening up other Excel books, you may also want to consider throwing something in the Workbook_Activate and Workbook_Deactivate events. That way your users can have their commandbars restored when they move to the other workbook, and removed when they come back.
Cheers,Private Sub Workbook_Activate() MsgBox "book1 active!" End Sub Private Sub Workbook_Deactivate() MsgBox "book1 deactivated!" End Sub
Ken Puls, CMA - Microsoft MVP (Excel)
I hate it when my computer does what I tell it to, and not what I want it to.
Learn how to use our KB tags! -||- Ken's Excel Website -||- Ken's Excel Forums -||- My Blog -||- Excel Training Calendar
This is a shameless plug for my new book "RibbonX - Customizing the Office 2007 Ribbon". Find out more about it here!
Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved!
Gee, great idea Ken, thanks.
But, as a matter of fact, now I need something more. Is there a way I may hide the rows and columns labels? And what about hidding the formulas bar, is it possible?
Best Regards,
Carlos Paleo.
To every problem there is a solution, even if I dont know it, so this posting is provided "AS IS" with no warranties.
If Debugging is harder than writing a program and your code is as good as you can possibly make
it, then by definition you're not smart enough to debug it.
http://www.mugrs.org
Hi Carlos,
Try this:
Application.DisplayFormulaBar = False ActiveWindow.DisplayHeadings = False
Ken Puls, CMA - Microsoft MVP (Excel)
I hate it when my computer does what I tell it to, and not what I want it to.
Learn how to use our KB tags! -||- Ken's Excel Website -||- Ken's Excel Forums -||- My Blog -||- Excel Training Calendar
This is a shameless plug for my new book "RibbonX - Customizing the Office 2007 Ribbon". Find out more about it here!
Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved!
Hi Ken, perfect, this time worked great.
This is the final code:
Private Sub Workbook_Activate() For Each cbars In Application.CommandBars cbars.Enabled = False Next Application.DisplayFormulaBar = False ActiveWindow.DisplayHeadings = False End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) For Each cbars In Application.CommandBars cbars.Enabled = True Next Application.DisplayFormulaBar = True ActiveWindow.DisplayHeadings = True End Sub Private Sub Workbook_Deactivate() For Each cbars In Application.CommandBars cbars.Enabled = True Next Application.DisplayFormulaBar = True ActiveWindow.DisplayHeadings = True End Sub Private Sub Workbook_Open() For Each cbars In Application.CommandBars cbars.Enabled = False Next Application.DisplayFormulaBar = False ActiveWindow.DisplayHeadings = False End Sub
Best Regards,
Carlos Paleo.
To every problem there is a solution, even if I dont know it, so this posting is provided "AS IS" with no warranties.
If Debugging is harder than writing a program and your code is as good as you can possibly make
it, then by definition you're not smart enough to debug it.
http://www.mugrs.org
Hey Carlos,
If you will have any more of this to do, you might want to think about just writing a normal Sub routine for this, then calling from your events. Something like ...
In a Standard Module:
In your ThisWorkbook Module:
Private Sub Workbook_BeforeClose(Cancel As Boolean) Call Turn_On End Sub Private Sub Workbook_Deactivate() Call Turn_On End Sub Private Sub Workbook_Activate() Call Turn_Off End Sub Private Sub Workbook_Open() Call Turn_Off End Sub[/vba] In a Standard Module: [vba]Sub Turn_Off() Dim cbars As CommandBar For Each cbars In Application.CommandBars cbars.Enabled = False Next Application.DisplayFormulaBar = False ActiveWindow.DisplayHeadings = False End Sub Sub Turn_On() Dim cbars As CommandBar For Each cbars In Application.CommandBars cbars.Enabled = True Next Application.DisplayFormulaBar = True ActiveWindow.DisplayHeadings = True End Sub
Regards, Zack Barresse
Check out the KB! :|: BOARD TAGS: WHAT ARE THEY AND HOW DO I USE THEM
What is a Microsoft MVP? | Free Microsoft Courses | My Book on Excel Tables
Gee, this solution is a lot better. Thanks Zack!
Best Regards,
Carlos Paleo.
To every problem there is a solution, even if I dont know it, so this posting is provided "AS IS" with no warranties.
If Debugging is harder than writing a program and your code is as good as you can possibly make
it, then by definition you're not smart enough to debug it.
http://www.mugrs.org
Okay, now this is my final code. With some changes.
In This Workbook Module
Private Sub Workbook_Activate() Call TurnOff End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) Call TurnOn End Sub Private Sub Workbook_Deactivate() Call TurnOn End Sub Private Sub Workbook_Open() Call TurnOff End Sub
In Standard Module
Sub TurnOff() For Each cbars In Application.CommandBars cbars.Enabled = False Next ActiveWindow.DisplayHeadings = False With Application .DisplayFormulaBar = False .Caption = "My Application" End With End Sub Sub TurnOn() For Each cbars In Application.CommandBars cbars.Enabled = True Next ActiveWindow.DisplayHeadings = True With Application .DisplayFormulaBar = True .Caption = "Microsoft Excel" End With End Sub
Best Regards,
Carlos Paleo.
To every problem there is a solution, even if I dont know it, so this posting is provided "AS IS" with no warranties.
If Debugging is harder than writing a program and your code is as good as you can possibly make
it, then by definition you're not smart enough to debug it.
http://www.mugrs.org