Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programmatically Disabling Excel Toolbars for one session only?
Hello,
How can i programmatically disable (not display) the excel toolbars for a single instance of excel such that the original toolbars configuration is reappears on subsequent invocations of excel. ie. For Each bar In Application.CommandBars If bar.Visible Then bar.Visible=False Next Problem is when i do the above, all subsequent invocations of excel will have no toolbars displayed. Only way i can think of reverting back to original toolbars configuration is to save which toolbars are visible, and then set them back to true when the workbook closes but this seems very clumsy. is there a more efficient approach? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programmatically Disabling Excel Toolbars for one session only?
That is generally the approach, but you need to reset on exit. Assuming you
have this in a particular workbook, put the code in the Workbook_Open event, and reset in the BeforeClose event. Option Explicit Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim oCB As CommandBar For Each oCB In Application.CommandBars oCB.Enabled = True Next oCB End Sub Private Sub Workbook_Open() Dim oCB As CommandBar For Each oCB In Application.CommandBars oCB.Enabled = False Next oCB End Sub 'This is workbook event code. 'To input this code, right click on the Excel icon on the worksheet '(or next to the File menu if you maximise your workbooks), 'select View Code from the menu, and paste the code -- HTH RP (remove nothere from the email address if mailing direct) "BW" wrote in message ... Hello, How can i programmatically disable (not display) the excel toolbars for a single instance of excel such that the original toolbars configuration is reappears on subsequent invocations of excel. ie. For Each bar In Application.CommandBars If bar.Visible Then bar.Visible=False Next Problem is when i do the above, all subsequent invocations of excel will have no toolbars displayed. Only way i can think of reverting back to original toolbars configuration is to save which toolbars are visible, and then set them back to true when the workbook closes but this seems very clumsy. is there a more efficient approach? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programmatically Disabling Excel Toolbars for one session only
Hi, BW
You also need to take care that you don't disable the Excel MenuBar when you're disabling the Toolbars. One nice way that someone showed me how to do it was: Private Sub Workbook_Open() Application.DisplayFullScreen = True 'Put this commands before UserToolBars(xlon)/(xloff) 'otherwise the toolbar generated by turning full screen on/off 'causes a conflict. UserToolBars (xlOn) 'set toolbar state ON & turn toolbars OFF End Sub Private Sub Workbook_BeforeClose(cancel As Boolean) Application.DisplayFullScreen = False UserToolBars (xlOff) 'set toolbar state OFF & turn toolbars ON End Sub Sub UserToolBars(State) Static UserToolBars As New Collection Dim UserBar If State = xlOn Then For Each UserBar In Application.CommandBars If UserBar.Type < 1 And UserBar.Visible Then UserToolBars.Add UserBar UserBar.Visible = False End If Next UserBar Else For Each UserBar In UserToolBars UserBar.Visible = True Next UserBar End If End Sub In this case, the first two macros go in your ThisWorkbook code page, the third can go in a normal code module. Hope this helps Pete "Bob Phillips" wrote: That is generally the approach, but you need to reset on exit. Assuming you have this in a particular workbook, put the code in the Workbook_Open event, and reset in the BeforeClose event. Option Explicit Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim oCB As CommandBar For Each oCB In Application.CommandBars oCB.Enabled = True Next oCB End Sub Private Sub Workbook_Open() Dim oCB As CommandBar For Each oCB In Application.CommandBars oCB.Enabled = False Next oCB End Sub 'This is workbook event code. 'To input this code, right click on the Excel icon on the worksheet '(or next to the File menu if you maximise your workbooks), 'select View Code from the menu, and paste the code -- HTH RP (remove nothere from the email address if mailing direct) "BW" wrote in message ... Hello, How can i programmatically disable (not display) the excel toolbars for a single instance of excel such that the original toolbars configuration is reappears on subsequent invocations of excel. ie. For Each bar In Application.CommandBars If bar.Visible Then bar.Visible=False Next Problem is when i do the above, all subsequent invocations of excel will have no toolbars displayed. Only way i can think of reverting back to original toolbars configuration is to save which toolbars are visible, and then set them back to true when the workbook closes but this seems very clumsy. is there a more efficient approach? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programmatically Disabling Excel Toolbars for one session only
Thanks gentlemen.. It worked..
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Startup and template locations in Citrix TS session and local session | Setting up and Configuration of Excel | |||
Each New Excel to open in another session | Excel Worksheet Functions | |||
Can't alt-tab between Excel workbooks in one session. Help! | Excel Discussion (Misc queries) | |||
Locking(?) an excel session | Excel Programming | |||
Creating/Destroying Toolbars Programmatically | Excel Programming |