Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide menus etc in Excel 2007
How can I convert the following to hide the menus and ribbon in Excel
2007? ********** Dim Wksht As Worksheet With Application .DisplayFormulaBar = False .CommandBars("Worksheet Menu Bar").Enabled = False .CommandBars("Standard").Visible = False .CommandBars("Formatting").Visible = False .CommandBars("Drawing").Visible = False End With ********** Thanks Sandy |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide menus etc in Excel 2007
See this page Sandy
http://www.rondebruin.nl/ribbon.htm See the xml examples (2) .DisplayFormulaBar = False This is the same in 2007 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Sandy" wrote in message ... How can I convert the following to hide the menus and ribbon in Excel 2007? ********** Dim Wksht As Worksheet With Application .DisplayFormulaBar = False .CommandBars("Worksheet Menu Bar").Enabled = False .CommandBars("Standard").Visible = False .CommandBars("Formatting").Visible = False .CommandBars("Drawing").Visible = False End With ********** Thanks Sandy |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide menus etc in Excel 2007
And, if you want to use a single routine to toggle the visibility of the
Ribbon and Formula bar between visible and not visible with each call (so you don't have to remember or check what state it is currently in), you can use this macro.... Sub ToggleRibbonAndFormulaBar() ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon""," & _ CStr(Not Application.DisplayFormulaBar) & ")" Application.DisplayFormulaBar = Not Application.DisplayFormulaBar End Sub Rick "Ron de Bruin" wrote in message ... See this page Sandy http://www.rondebruin.nl/ribbon.htm See the xml examples (2) .DisplayFormulaBar = False This is the same in 2007 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Sandy" wrote in message ... How can I convert the following to hide the menus and ribbon in Excel 2007? ********** Dim Wksht As Worksheet With Application .DisplayFormulaBar = False .CommandBars("Worksheet Menu Bar").Enabled = False .CommandBars("Standard").Visible = False .CommandBars("Formatting").Visible = False .CommandBars("Drawing").Visible = False End With ********** Thanks Sandy |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide menus etc in Excel 2007
Thanks Ron
Perfect - can I ask another......... I notice there is a 'new sheet' tab in 2007 - don't suppose you know how to hide that or at least disable it? Sandy "Ron de Bruin" wrote in message ... See this page Sandy http://www.rondebruin.nl/ribbon.htm See the xml examples (2) .DisplayFormulaBar = False This is the same in 2007 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Sandy" wrote in message ... How can I convert the following to hide the menus and ribbon in Excel 2007? ********** Dim Wksht As Worksheet With Application .DisplayFormulaBar = False .CommandBars("Worksheet Menu Bar").Enabled = False .CommandBars("Standard").Visible = False .CommandBars("Formatting").Visible = False .CommandBars("Drawing").Visible = False End With ********** Thanks Sandy |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide menus etc in Excel 2007
I think it is not possible but I not looked very good at it
Protect the workbook and it is not possible to insert new sheets (ReviewProtect workbook) For example Shift F11 will also insert a sheet so you must also disable this shortcut, protecting the workbook is easier. -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Sandy" wrote in message ... Thanks Ron Perfect - can I ask another......... I notice there is a 'new sheet' tab in 2007 - don't suppose you know how to hide that or at least disable it? Sandy "Ron de Bruin" wrote in message ... See this page Sandy http://www.rondebruin.nl/ribbon.htm See the xml examples (2) .DisplayFormulaBar = False This is the same in 2007 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Sandy" wrote in message ... How can I convert the following to hide the menus and ribbon in Excel 2007? ********** Dim Wksht As Worksheet With Application .DisplayFormulaBar = False .CommandBars("Worksheet Menu Bar").Enabled = False .CommandBars("Standard").Visible = False .CommandBars("Formatting").Visible = False .CommandBars("Drawing").Visible = False End With ********** Thanks Sandy |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide menus etc in Excel 2007
You could use the Workbook's NewSheet event to delete the newly added sheet
as soon as it is added. Let's give you a way to toggle this 'feature' on and off. Add a Module to your project (or use an existing Module if you have one) and put this declaration into it... Public BlockInsertSheet As Boolean Now, double click on the ThisWorkbook entry in the Project window and put the following code in the code window that appeared... Private Sub Workbook_NewSheet(ByVal Sh As Object) If Not BlockInsertSheet Then Exit Sub With Application .DisplayAlerts = False .ScreenUpdating = False End With Sh.Delete With Application .DisplayAlerts = True .ScreenUpdating = True End With End Sub If you set BlockInsertSheet to True anywhere within your project, the user will not be able to insert any new sheets. Unfortunately, the "Insert Worksheet Tab" is still visible, but it won't function (so you might want to consider putting a MessageBox at the end of the NewSheet event code telling the user that Sheet inserts are disabled). Neither will right-clicking a tab and selecting Insert from the popup menu. If you want, we can disable/enable the popup menu's Insert item to coincide with the disabling or enabling of inserting sheets. To do that, simply add this subroutine to the Module (do not change the Module's BlockInsertSheet declaration nor the NewSheet event code posted above)... Sub AllowInsertSheets(Optional Status As Boolean = True) BlockInsertSheet = Not Status Application.CommandBars("Ply").FindControl(, 945).Enabled = Status End Sub But now, you do NOT set the BlockInsertSheet variable directly; instead, you call the AllowInsertSheets subroutine with either a True (optional) or False (required) argument to allow or disallow the inserting of sheets (the subroutine will handle enabling or disabling the popup menu's Insert item). Rick "Sandy" wrote in message ... Thanks Ron Perfect - can I ask another......... I notice there is a 'new sheet' tab in 2007 - don't suppose you know how to hide that or at least disable it? Sandy "Ron de Bruin" wrote in message ... See this page Sandy http://www.rondebruin.nl/ribbon.htm See the xml examples (2) .DisplayFormulaBar = False This is the same in 2007 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Sandy" wrote in message ... How can I convert the following to hide the menus and ribbon in Excel 2007? ********** Dim Wksht As Worksheet With Application .DisplayFormulaBar = False .CommandBars("Worksheet Menu Bar").Enabled = False .CommandBars("Standard").Visible = False .CommandBars("Formatting").Visible = False .CommandBars("Drawing").Visible = False End With ********** Thanks Sandy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sub-menus of main menus for Excel 2007 | New Users to Excel | |||
Excel 2007 menus - how can I get rid of them? | New Users to Excel | |||
Excel 2007 Menus | Excel Programming | |||
Excel 2007 menus - XML Notepad 2007 | Excel Programming | |||
Excel 2007 menus - is this right? | Excel Programming |