|
Hiding all visible toolbars by writing their numbers to an array
Good morning, all!
I'm trying to write a routine which will hide all the toolbars that are visible whenever a workbook is open, then be able to re-open all the toolbars that it hid, when the workbook is closed. I tried to accomplish this by writing the CommandBar numbers of all the open CommandBars to an array - this all works OK, but when I actually get to the third loop in my macro, and the code tries to close the first visble toolbar, I get "Method 'Visible' of object 'CommandBars' failed" on the line marked with three asterisks below. The first loop runs purely to allow me to redimension the array to make it contain just enough elements to store the numbers of the open toolbars. The second loop stores the numbers of the open CommandBars to the array. I'm going to need to keep these values static, so that I can unhide the appropriate CommandBars when I'm done, but can anyone help me out with why this line of code won't work, please. 3 hours struggling with this is quite enough - over to the experts! Thanks in advance Pete Sub HideVisibleCommandBars() Dim VCBCount As Integer Dim VCBArray() As Integer Dim LoopCounter As Integer Dim TotalBarCount As Integer 'Determine how many CommandBars are visible For LoopCounter = 1 To Application.CommandBars.Count TotalBarCount = TotalBarCount + 1 If Application.CommandBars(LoopCounter).Visible = True Then 'MsgBox (Application.CommandBars(LoopCounter).Name) VCBCount = VCBCount + 1 End If Next MsgBox ("There are " & TotalBarCount & " command bars of which " & VCBCount & " are visible!") 'Redimension the array based on how many visible CommandBars were detected ReDim VCBArray(VCBCount) As Integer 'Store CommandBar numbers of Visible CommandBars to array VCBCount = 0 For LoopCounter = 1 To Application.CommandBars.Count If Application.CommandBars(LoopCounter).Visible = True Then VCBCount = VCBCount + 1 VCBArray(VCBCount) = LoopCounter MsgBox (VCBArray(VCBCount)) End If Next 'Hide Visible CommandBars based on numbers stored in array For LoopCounter = 1 To VCBCount MsgBox ("Cycle " & LoopCounter & " of " & VCBCount & vbCrLf & _ "VCBArray Element " & LoopCounter & " contains " & VCBArray(LoopCounter)) Application.CommandBars(VCBArray(LoopCounter)).Vis ible = False '*** Next End Sub |
Hiding all visible toolbars by writing their numbers to an array
I think I solved part of the problem - the first visible command bar was the
worksheet menu bar (CommandBar ID 1), which can't be hidden in this way (apparently), If I changed my loop counter to start from 2, then all the other toolbars are successfully hidden. This leads me to 2 further questions, then: 1. How do I distinguish between toolbars and menu bars to (in this case) start my loop from 1 but ignore the Worksheet menu bar? 2. How to I preserve the commandbar IDs that I stored to my array so that I can later go back and use the array values to redisplay the toolbars that they refer to- is it something to do with STATIC..? Thanks again Pete "Peter Rooney" wrote: Good morning, all! I'm trying to write a routine which will hide all the toolbars that are visible whenever a workbook is open, then be able to re-open all the toolbars that it hid, when the workbook is closed. I tried to accomplish this by writing the CommandBar numbers of all the open CommandBars to an array - this all works OK, but when I actually get to the third loop in my macro, and the code tries to close the first visble toolbar, I get "Method 'Visible' of object 'CommandBars' failed" on the line marked with three asterisks below. The first loop runs purely to allow me to redimension the array to make it contain just enough elements to store the numbers of the open toolbars. The second loop stores the numbers of the open CommandBars to the array. I'm going to need to keep these values static, so that I can unhide the appropriate CommandBars when I'm done, but can anyone help me out with why this line of code won't work, please. 3 hours struggling with this is quite enough - over to the experts! Thanks in advance Pete Sub HideVisibleCommandBars() Dim VCBCount As Integer Dim VCBArray() As Integer Dim LoopCounter As Integer Dim TotalBarCount As Integer 'Determine how many CommandBars are visible For LoopCounter = 1 To Application.CommandBars.Count TotalBarCount = TotalBarCount + 1 If Application.CommandBars(LoopCounter).Visible = True Then 'MsgBox (Application.CommandBars(LoopCounter).Name) VCBCount = VCBCount + 1 End If Next MsgBox ("There are " & TotalBarCount & " command bars of which " & VCBCount & " are visible!") 'Redimension the array based on how many visible CommandBars were detected ReDim VCBArray(VCBCount) As Integer 'Store CommandBar numbers of Visible CommandBars to array VCBCount = 0 For LoopCounter = 1 To Application.CommandBars.Count If Application.CommandBars(LoopCounter).Visible = True Then VCBCount = VCBCount + 1 VCBArray(VCBCount) = LoopCounter MsgBox (VCBArray(VCBCount)) End If Next 'Hide Visible CommandBars based on numbers stored in array For LoopCounter = 1 To VCBCount MsgBox ("Cycle " & LoopCounter & " of " & VCBCount & vbCrLf & _ "VCBArray Element " & LoopCounter & " contains " & VCBArray(LoopCounter)) Application.CommandBars(VCBArray(LoopCounter)).Vis ible = False '*** Next End Sub |
Hiding all visible toolbars by writing their numbers to an array
Hi,
On opening th workbook include the following statement: UserToolBars(xlOff) On closing include the following statement: UserToolBars(xlOn) Put the following in a standard module 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 xlOn and xlOff are built in excel constants All toolbars except the main menu abr are hidden/restored. HTH Don "Peter Rooney" wrote in message ... Good morning, all! I'm trying to write a routine which will hide all the toolbars that are visible whenever a workbook is open, then be able to re-open all the toolbars that it hid, when the workbook is closed. I tried to accomplish this by writing the CommandBar numbers of all the open CommandBars to an array - this all works OK, but when I actually get to the third loop in my macro, and the code tries to close the first visble toolbar, I get "Method 'Visible' of object 'CommandBars' failed" on the line marked with three asterisks below. The first loop runs purely to allow me to redimension the array to make it contain just enough elements to store the numbers of the open toolbars. The second loop stores the numbers of the open CommandBars to the array. I'm going to need to keep these values static, so that I can unhide the appropriate CommandBars when I'm done, but can anyone help me out with why this line of code won't work, please. 3 hours struggling with this is quite enough - over to the experts! Thanks in advance Pete Sub HideVisibleCommandBars() Dim VCBCount As Integer Dim VCBArray() As Integer Dim LoopCounter As Integer Dim TotalBarCount As Integer 'Determine how many CommandBars are visible For LoopCounter = 1 To Application.CommandBars.Count TotalBarCount = TotalBarCount + 1 If Application.CommandBars(LoopCounter).Visible = True Then 'MsgBox (Application.CommandBars(LoopCounter).Name) VCBCount = VCBCount + 1 End If Next MsgBox ("There are " & TotalBarCount & " command bars of which " & VCBCount & " are visible!") 'Redimension the array based on how many visible CommandBars were detected ReDim VCBArray(VCBCount) As Integer 'Store CommandBar numbers of Visible CommandBars to array VCBCount = 0 For LoopCounter = 1 To Application.CommandBars.Count If Application.CommandBars(LoopCounter).Visible = True Then VCBCount = VCBCount + 1 VCBArray(VCBCount) = LoopCounter MsgBox (VCBArray(VCBCount)) End If Next 'Hide Visible CommandBars based on numbers stored in array For LoopCounter = 1 To VCBCount MsgBox ("Cycle " & LoopCounter & " of " & VCBCount & vbCrLf & _ "VCBArray Element " & LoopCounter & " contains " & VCBArray(LoopCounter)) Application.CommandBars(VCBArray(LoopCounter)).Vis ible = False '*** Next End Sub |
Hiding all visible toolbars by writing their numbers to an arr
Don,
This looks good, except that the toolbars go off when I CLOSE the workbook! Here are my Workbook_Open and Workbook_BeforeClose Private Sub Workbook_Open() UserToolBars (xlOff) End Sub Private Sub Workbook_BeforeClose(cancel As Boolean) UserToolBars (xlOn) End Sub The other code is in a normal module Any thoughts? Pete "Don Lloyd" wrote: Hi, On opening th workbook include the following statement: UserToolBars(xlOff) On closing include the following statement: UserToolBars(xlOn) Put the following in a standard module 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 xlOn and xlOff are built in excel constants All toolbars except the main menu abr are hidden/restored. HTH Don "Peter Rooney" wrote in message ... Good morning, all! I'm trying to write a routine which will hide all the toolbars that are visible whenever a workbook is open, then be able to re-open all the toolbars that it hid, when the workbook is closed. I tried to accomplish this by writing the CommandBar numbers of all the open CommandBars to an array - this all works OK, but when I actually get to the third loop in my macro, and the code tries to close the first visble toolbar, I get "Method 'Visible' of object 'CommandBars' failed" on the line marked with three asterisks below. The first loop runs purely to allow me to redimension the array to make it contain just enough elements to store the numbers of the open toolbars. The second loop stores the numbers of the open CommandBars to the array. I'm going to need to keep these values static, so that I can unhide the appropriate CommandBars when I'm done, but can anyone help me out with why this line of code won't work, please. 3 hours struggling with this is quite enough - over to the experts! Thanks in advance Pete Sub HideVisibleCommandBars() Dim VCBCount As Integer Dim VCBArray() As Integer Dim LoopCounter As Integer Dim TotalBarCount As Integer 'Determine how many CommandBars are visible For LoopCounter = 1 To Application.CommandBars.Count TotalBarCount = TotalBarCount + 1 If Application.CommandBars(LoopCounter).Visible = True Then 'MsgBox (Application.CommandBars(LoopCounter).Name) VCBCount = VCBCount + 1 End If Next MsgBox ("There are " & TotalBarCount & " command bars of which " & VCBCount & " are visible!") 'Redimension the array based on how many visible CommandBars were detected ReDim VCBArray(VCBCount) As Integer 'Store CommandBar numbers of Visible CommandBars to array VCBCount = 0 For LoopCounter = 1 To Application.CommandBars.Count If Application.CommandBars(LoopCounter).Visible = True Then VCBCount = VCBCount + 1 VCBArray(VCBCount) = LoopCounter MsgBox (VCBArray(VCBCount)) End If Next 'Hide Visible CommandBars based on numbers stored in array For LoopCounter = 1 To VCBCount MsgBox ("Cycle " & LoopCounter & " of " & VCBCount & vbCrLf & _ "VCBArray Element " & LoopCounter & " contains " & VCBArray(LoopCounter)) Application.CommandBars(VCBArray(LoopCounter)).Vis ible = False '*** Next End Sub |
Hiding all visible toolbars by writing their numbers to an arr
Peter,
You have Xlon/xlOff the wrong way round. Private Sub Workbook_Open() UserToolBars (xlOn) End Sub Private Sub Workbook_BeforeClose(cancel As Boolean) UserToolBars (xlOff) End Sub HTH "Peter Rooney" wrote: Don, This looks good, except that the toolbars go off when I CLOSE the workbook! Here are my Workbook_Open and Workbook_BeforeClose Private Sub Workbook_Open() UserToolBars (xlOff) End Sub Private Sub Workbook_BeforeClose(cancel As Boolean) UserToolBars (xlOn) End Sub The other code is in a normal module Any thoughts? Pete "Don Lloyd" wrote: Hi, On opening th workbook include the following statement: UserToolBars(xlOff) On closing include the following statement: UserToolBars(xlOn) Put the following in a standard module 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 xlOn and xlOff are built in excel constants All toolbars except the main menu abr are hidden/restored. HTH Don "Peter Rooney" wrote in message ... Good morning, all! I'm trying to write a routine which will hide all the toolbars that are visible whenever a workbook is open, then be able to re-open all the toolbars that it hid, when the workbook is closed. I tried to accomplish this by writing the CommandBar numbers of all the open CommandBars to an array - this all works OK, but when I actually get to the third loop in my macro, and the code tries to close the first visble toolbar, I get "Method 'Visible' of object 'CommandBars' failed" on the line marked with three asterisks below. The first loop runs purely to allow me to redimension the array to make it contain just enough elements to store the numbers of the open toolbars. The second loop stores the numbers of the open CommandBars to the array. I'm going to need to keep these values static, so that I can unhide the appropriate CommandBars when I'm done, but can anyone help me out with why this line of code won't work, please. 3 hours struggling with this is quite enough - over to the experts! Thanks in advance Pete Sub HideVisibleCommandBars() Dim VCBCount As Integer Dim VCBArray() As Integer Dim LoopCounter As Integer Dim TotalBarCount As Integer 'Determine how many CommandBars are visible For LoopCounter = 1 To Application.CommandBars.Count TotalBarCount = TotalBarCount + 1 If Application.CommandBars(LoopCounter).Visible = True Then 'MsgBox (Application.CommandBars(LoopCounter).Name) VCBCount = VCBCount + 1 End If Next MsgBox ("There are " & TotalBarCount & " command bars of which " & VCBCount & " are visible!") 'Redimension the array based on how many visible CommandBars were detected ReDim VCBArray(VCBCount) As Integer 'Store CommandBar numbers of Visible CommandBars to array VCBCount = 0 For LoopCounter = 1 To Application.CommandBars.Count If Application.CommandBars(LoopCounter).Visible = True Then VCBCount = VCBCount + 1 VCBArray(VCBCount) = LoopCounter MsgBox (VCBArray(VCBCount)) End If Next 'Hide Visible CommandBars based on numbers stored in array For LoopCounter = 1 To VCBCount MsgBox ("Cycle " & LoopCounter & " of " & VCBCount & vbCrLf & _ "VCBArray Element " & LoopCounter & " contains " & VCBArray(LoopCounter)) Application.CommandBars(VCBArray(LoopCounter)).Vis ible = False '*** Next End Sub |
Hiding all visible toolbars by writing their numbers to an arr
Doh,
You'd have thought I'd have tried that, wouldn't you..? :-) Thanks very much - all working fine, now! Pete "Toppers" wrote: Peter, You have Xlon/xlOff the wrong way round. Private Sub Workbook_Open() UserToolBars (xlOn) End Sub Private Sub Workbook_BeforeClose(cancel As Boolean) UserToolBars (xlOff) End Sub HTH "Peter Rooney" wrote: Don, This looks good, except that the toolbars go off when I CLOSE the workbook! Here are my Workbook_Open and Workbook_BeforeClose Private Sub Workbook_Open() UserToolBars (xlOff) End Sub Private Sub Workbook_BeforeClose(cancel As Boolean) UserToolBars (xlOn) End Sub The other code is in a normal module Any thoughts? Pete "Don Lloyd" wrote: Hi, On opening th workbook include the following statement: UserToolBars(xlOff) On closing include the following statement: UserToolBars(xlOn) Put the following in a standard module 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 xlOn and xlOff are built in excel constants All toolbars except the main menu abr are hidden/restored. HTH Don "Peter Rooney" wrote in message ... Good morning, all! I'm trying to write a routine which will hide all the toolbars that are visible whenever a workbook is open, then be able to re-open all the toolbars that it hid, when the workbook is closed. I tried to accomplish this by writing the CommandBar numbers of all the open CommandBars to an array - this all works OK, but when I actually get to the third loop in my macro, and the code tries to close the first visble toolbar, I get "Method 'Visible' of object 'CommandBars' failed" on the line marked with three asterisks below. The first loop runs purely to allow me to redimension the array to make it contain just enough elements to store the numbers of the open toolbars. The second loop stores the numbers of the open CommandBars to the array. I'm going to need to keep these values static, so that I can unhide the appropriate CommandBars when I'm done, but can anyone help me out with why this line of code won't work, please. 3 hours struggling with this is quite enough - over to the experts! Thanks in advance Pete Sub HideVisibleCommandBars() Dim VCBCount As Integer Dim VCBArray() As Integer Dim LoopCounter As Integer Dim TotalBarCount As Integer 'Determine how many CommandBars are visible For LoopCounter = 1 To Application.CommandBars.Count TotalBarCount = TotalBarCount + 1 If Application.CommandBars(LoopCounter).Visible = True Then 'MsgBox (Application.CommandBars(LoopCounter).Name) VCBCount = VCBCount + 1 End If Next MsgBox ("There are " & TotalBarCount & " command bars of which " & VCBCount & " are visible!") 'Redimension the array based on how many visible CommandBars were detected ReDim VCBArray(VCBCount) As Integer 'Store CommandBar numbers of Visible CommandBars to array VCBCount = 0 For LoopCounter = 1 To Application.CommandBars.Count If Application.CommandBars(LoopCounter).Visible = True Then VCBCount = VCBCount + 1 VCBArray(VCBCount) = LoopCounter MsgBox (VCBArray(VCBCount)) End If Next 'Hide Visible CommandBars based on numbers stored in array For LoopCounter = 1 To VCBCount MsgBox ("Cycle " & LoopCounter & " of " & VCBCount & vbCrLf & _ "VCBArray Element " & LoopCounter & " contains " & VCBArray(LoopCounter)) Application.CommandBars(VCBArray(LoopCounter)).Vis ible = False '*** Next End Sub |
Hiding all visible toolbars by writing their numbers to an arr
Don,
Yep, got my xlo/xloff the wrong way round! Your userbar.type was a good one I didn't know about previously, too! All working fine now "Don Lloyd" wrote: Hi, On opening th workbook include the following statement: UserToolBars(xlOff) On closing include the following statement: UserToolBars(xlOn) Put the following in a standard module 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 xlOn and xlOff are built in excel constants All toolbars except the main menu abr are hidden/restored. HTH Don "Peter Rooney" wrote in message ... Good morning, all! I'm trying to write a routine which will hide all the toolbars that are visible whenever a workbook is open, then be able to re-open all the toolbars that it hid, when the workbook is closed. I tried to accomplish this by writing the CommandBar numbers of all the open CommandBars to an array - this all works OK, but when I actually get to the third loop in my macro, and the code tries to close the first visble toolbar, I get "Method 'Visible' of object 'CommandBars' failed" on the line marked with three asterisks below. The first loop runs purely to allow me to redimension the array to make it contain just enough elements to store the numbers of the open toolbars. The second loop stores the numbers of the open CommandBars to the array. I'm going to need to keep these values static, so that I can unhide the appropriate CommandBars when I'm done, but can anyone help me out with why this line of code won't work, please. 3 hours struggling with this is quite enough - over to the experts! Thanks in advance Pete Sub HideVisibleCommandBars() Dim VCBCount As Integer Dim VCBArray() As Integer Dim LoopCounter As Integer Dim TotalBarCount As Integer 'Determine how many CommandBars are visible For LoopCounter = 1 To Application.CommandBars.Count TotalBarCount = TotalBarCount + 1 If Application.CommandBars(LoopCounter).Visible = True Then 'MsgBox (Application.CommandBars(LoopCounter).Name) VCBCount = VCBCount + 1 End If Next MsgBox ("There are " & TotalBarCount & " command bars of which " & VCBCount & " are visible!") 'Redimension the array based on how many visible CommandBars were detected ReDim VCBArray(VCBCount) As Integer 'Store CommandBar numbers of Visible CommandBars to array VCBCount = 0 For LoopCounter = 1 To Application.CommandBars.Count If Application.CommandBars(LoopCounter).Visible = True Then VCBCount = VCBCount + 1 VCBArray(VCBCount) = LoopCounter MsgBox (VCBArray(VCBCount)) End If Next 'Hide Visible CommandBars based on numbers stored in array For LoopCounter = 1 To VCBCount MsgBox ("Cycle " & LoopCounter & " of " & VCBCount & vbCrLf & _ "VCBArray Element " & LoopCounter & " contains " & VCBArray(LoopCounter)) Application.CommandBars(VCBArray(LoopCounter)).Vis ible = False '*** Next End Sub |
| All times are GMT +1. The time now is 02:55 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com