Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Enable toolbars at close
I created a macro rich file that when it is opened all the
active toolbars that the user has on there screen are turned off. What I want to do is be able to make them active again once the user closes the spreadsheet. This is the method I am using right now. ............................ Option Explicit Public ToolbarArray(20) As String Sub HideAllToolbars() Dim TBVar As Variant Dim Counter As Integer Counter = 1 For Each TBVar In Application.Toolbars If TBVar.Visible = True Then ToolbarArray(Counter) = TBVar.Name TBVar.Visible = False Counter = Counter + 1 End If Next End Sub Sub RestoreToolbars() Dim TBVar As Variant For Each TBVar In ToolbarArray If (TBVar < "") Then Application.Toolbars(TBVar).Visible = True End If Next End Sub ........................... This works fine if the user only opens and closes the program with running any of the macros in the spreadsheet, but once any macro is performed the memory of what toolbars are shut off is wiped out and the restore Toolbar sub does nothing when initiated. Is there a way around this? If I have to I am willing to recall each toolbar indivisually, but I am not sure how to do that? Any help is appreciated. Pete |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Enable toolbars at close
Application.Toolbars?
Try Application.Commandbars -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Pete" wrote in message ... I created a macro rich file that when it is opened all the active toolbars that the user has on there screen are turned off. What I want to do is be able to make them active again once the user closes the spreadsheet. This is the method I am using right now. ........................... Option Explicit Public ToolbarArray(20) As String Sub HideAllToolbars() Dim TBVar As Variant Dim Counter As Integer Counter = 1 For Each TBVar In Application.Toolbars If TBVar.Visible = True Then ToolbarArray(Counter) = TBVar.Name TBVar.Visible = False Counter = Counter + 1 End If Next End Sub Sub RestoreToolbars() Dim TBVar As Variant For Each TBVar In ToolbarArray If (TBVar < "") Then Application.Toolbars(TBVar).Visible = True End If Next End Sub .......................... This works fine if the user only opens and closes the program with running any of the macros in the spreadsheet, but once any macro is performed the memory of what toolbars are shut off is wiped out and the restore Toolbar sub does nothing when initiated. Is there a way around this? If I have to I am willing to recall each toolbar indivisually, but I am not sure how to do that? Any help is appreciated. Pete |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Enable toolbars at close
Bob I don't follow your instruction. I also have a similar
situation where I want to disable certain toolbars when my spreadsheet opens. (ie. Standard, formatting, reviewing, visual basic....) How do you turn these off and on via code at will? Please advise. James -----Original Message----- Application.Toolbars? Try Application.Commandbars -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Pete" wrote in message ... I created a macro rich file that when it is opened all the active toolbars that the user has on there screen are turned off. What I want to do is be able to make them active again once the user closes the spreadsheet. This is the method I am using right now. ........................... Option Explicit Public ToolbarArray(20) As String Sub HideAllToolbars() Dim TBVar As Variant Dim Counter As Integer Counter = 1 For Each TBVar In Application.Toolbars If TBVar.Visible = True Then ToolbarArray(Counter) = TBVar.Name TBVar.Visible = False Counter = Counter + 1 End If Next End Sub Sub RestoreToolbars() Dim TBVar As Variant For Each TBVar In ToolbarArray If (TBVar < "") Then Application.Toolbars(TBVar).Visible = True End If Next End Sub .......................... This works fine if the user only opens and closes the program with running any of the macros in the spreadsheet, but once any macro is performed the memory of what toolbars are shut off is wiped out and the restore Toolbar sub does nothing when initiated. Is there a way around this? If I have to I am willing to recall each toolbar indivisually, but I am not sure how to do that? Any help is appreciated. Pete . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Enable toolbars at close
CommandBars("Formatting").Visible = False
to get a list rw = 1 for each cbar in Application.Commandbars cells(rw,1).Value = cbar.name rw = rw + 1 Next For the formula and/or statusbar Application.DisplayFormulaBar = False Application.DisplayStatusBar = False Application.DisplayFormulaBar = True Application.DisplayStatusBar = True "James" wrote in message ... Bob I don't follow your instruction. I also have a similar situation where I want to disable certain toolbars when my spreadsheet opens. (ie. Standard, formatting, reviewing, visual basic....) How do you turn these off and on via code at will? Please advise. James -----Original Message----- Application.Toolbars? Try Application.Commandbars -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Pete" wrote in message ... I created a macro rich file that when it is opened all the active toolbars that the user has on there screen are turned off. What I want to do is be able to make them active again once the user closes the spreadsheet. This is the method I am using right now. ........................... Option Explicit Public ToolbarArray(20) As String Sub HideAllToolbars() Dim TBVar As Variant Dim Counter As Integer Counter = 1 For Each TBVar In Application.Toolbars If TBVar.Visible = True Then ToolbarArray(Counter) = TBVar.Name TBVar.Visible = False Counter = Counter + 1 End If Next End Sub Sub RestoreToolbars() Dim TBVar As Variant For Each TBVar In ToolbarArray If (TBVar < "") Then Application.Toolbars(TBVar).Visible = True End If Next End Sub .......................... This works fine if the user only opens and closes the program with running any of the macros in the spreadsheet, but once any macro is performed the memory of what toolbars are shut off is wiped out and the restore Toolbar sub does nothing when initiated. Is there a way around this? If I have to I am willing to recall each toolbar indivisually, but I am not sure how to do that? Any help is appreciated. Pete . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Enable toolbars at close
Try This:
Sub DIS_ENAB_CB() Dim CB As CommandBar For Each CB In Application.CommandBars CB.Visible 'Enable = True 'False Next CB End Sub -----Original Message----- Bob I don't follow your instruction. I also have a similar situation where I want to disable certain toolbars when my spreadsheet opens. (ie. Standard, formatting, reviewing, visual basic....) How do you turn these off and on via code at will? Please advise. James -----Original Message----- Application.Toolbars? Try Application.Commandbars -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Pete" wrote in message .. . I created a macro rich file that when it is opened all the active toolbars that the user has on there screen are turned off. What I want to do is be able to make them active again once the user closes the spreadsheet. This is the method I am using right now. ........................... Option Explicit Public ToolbarArray(20) As String Sub HideAllToolbars() Dim TBVar As Variant Dim Counter As Integer Counter = 1 For Each TBVar In Application.Toolbars If TBVar.Visible = True Then ToolbarArray(Counter) = TBVar.Name TBVar.Visible = False Counter = Counter + 1 End If Next End Sub Sub RestoreToolbars() Dim TBVar As Variant For Each TBVar In ToolbarArray If (TBVar < "") Then Application.Toolbars(TBVar).Visible = True End If Next End Sub .......................... This works fine if the user only opens and closes the program with running any of the macros in the spreadsheet, but once any macro is performed the memory of what toolbars are shut off is wiped out and the restore Toolbar sub does nothing when initiated. Is there a way around this? If I have to I am willing to recall each toolbar indivisually, but I am not sure how to do that? Any help is appreciated. Pete . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
HOW TO DO ENABLE ENABLE THE QUERY PARAMETER BUTTON? | Excel Worksheet Functions | |||
Excel shoud not close all active books when clicking close button | Excel Discussion (Misc queries) | |||
close (hide) toolbars | Excel Discussion (Misc queries) | |||
Vba enable close button | Excel Programming | |||
Enable Close button | Excel Programming |