Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Commandbar on thisworkbook only
When creating a commandbar with the code below it is only a temporarily
commandbar and that is great, however it is only temp in that sense that you have to close the Excel application down and reopen before the commandbar removed. Is there away to have the commandbar removed as soon as you close the worksheet that has the macro and command? Dim oCb As CommandBar Dim oCtl As CommandBarButton Set oCb = Application.CommandBars("Worksheet Menu Bar") With oCb Set oCtl = .Controls.Add( _ Type:=msoControlButton, _ temporary:=True) oCtl.Caption = "myButton" oCtl.Style = msoButtonCaption oCtl.OnAction = "myMacro" End With Thank you in advance for you help. Rene |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Commandbar on thisworkbook only
The answer is yes. However, if you search this discussion for "GB Toolbar"
you will see a thread that has 28+ messages. Look towards the end of the thread for a more up-to-date version of the code. You should be able to snip out the Tool_Bar_Delete (Or similiarly named) section to perform what you want. You will also need to add some code to the ThisWorkbook_Close section to force destruction of the toolbar. The "final" version posted there (by me) allows for multiple toolbars to be created, one or more for each worksheet, or of course none to be present on a worksheet. There is an upper programmed limit of 10, but by removing one and inserting another that upper limit could be "exceeded." (I have not implemented this option in my code yet however.) "Rene Petersen" wrote: When creating a commandbar with the code below it is only a temporarily commandbar and that is great, however it is only temp in that sense that you have to close the Excel application down and reopen before the commandbar removed. Is there away to have the commandbar removed as soon as you close the worksheet that has the macro and command? Dim oCb As CommandBar Dim oCtl As CommandBarButton Set oCb = Application.CommandBars("Worksheet Menu Bar") With oCb Set oCtl = .Controls.Add( _ Type:=msoControlButton, _ temporary:=True) oCtl.Caption = "myButton" oCtl.Style = msoButtonCaption oCtl.OnAction = "myMacro" End With Thank you in advance for you help. Rene |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Commandbar on thisworkbook only
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.CommandBars("Worksheet Menu Bar") _ .Controls("myButton").Delete 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 Bob Phillips (remove nothere from email address if mailing direct) "Rene Petersen" wrote in message ... When creating a commandbar with the code below it is only a temporarily commandbar and that is great, however it is only temp in that sense that you have to close the Excel application down and reopen before the commandbar removed. Is there away to have the commandbar removed as soon as you close the worksheet that has the macro and command? Dim oCb As CommandBar Dim oCtl As CommandBarButton Set oCb = Application.CommandBars("Worksheet Menu Bar") With oCb Set oCtl = .Controls.Add( _ Type:=msoControlButton, _ temporary:=True) oCtl.Caption = "myButton" oCtl.Style = msoButtonCaption oCtl.OnAction = "myMacro" End With Thank you in advance for you help. Rene |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Commandbar on thisworkbook only
Actually, this option would remove the button, but not the commandbar.
Obviously to remove the commandbar, just remove from the below code the ..Controls("myButton") portion, and the commandbar will be deleted. it will error if the user has also deleted it from the View-Toolbars section, so might want some error controls. Just a thought. "Bob Phillips" wrote: Private Sub Workbook_BeforeClose(Cancel As Boolean) Application.CommandBars("Worksheet Menu Bar") _ .Controls("myButton").Delete 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 Bob Phillips (remove nothere from email address if mailing direct) "Rene Petersen" wrote in message ... When creating a commandbar with the code below it is only a temporarily commandbar and that is great, however it is only temp in that sense that you have to close the Excel application down and reopen before the commandbar removed. Is there away to have the commandbar removed as soon as you close the worksheet that has the macro and command? Dim oCb As CommandBar Dim oCtl As CommandBarButton Set oCb = Application.CommandBars("Worksheet Menu Bar") With oCb Set oCtl = .Controls.Add( _ Type:=msoControlButton, _ temporary:=True) oCtl.Caption = "myButton" oCtl.Style = msoButtonCaption oCtl.OnAction = "myMacro" End With Thank you in advance for you help. Rene |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Commandbar on thisworkbook only
Well I ran the create code and it only created a button, so I gave him code
just to remove that button. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "GB" wrote in message ... Actually, this option would remove the button, but not the commandbar. Obviously to remove the commandbar, just remove from the below code the .Controls("myButton") portion, and the commandbar will be deleted. it will error if the user has also deleted it from the View-Toolbars section, so might want some error controls. Just a thought. "Bob Phillips" wrote: Private Sub Workbook_BeforeClose(Cancel As Boolean) Application.CommandBars("Worksheet Menu Bar") _ .Controls("myButton").Delete 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 Bob Phillips (remove nothere from email address if mailing direct) "Rene Petersen" wrote in message ... When creating a commandbar with the code below it is only a temporarily commandbar and that is great, however it is only temp in that sense that you have to close the Excel application down and reopen before the commandbar removed. Is there away to have the commandbar removed as soon as you close the worksheet that has the macro and command? Dim oCb As CommandBar Dim oCtl As CommandBarButton Set oCb = Application.CommandBars("Worksheet Menu Bar") With oCb Set oCtl = .Controls.Add( _ Type:=msoControlButton, _ temporary:=True) oCtl.Caption = "myButton" oCtl.Style = msoButtonCaption oCtl.OnAction = "myMacro" End With Thank you in advance for you help. Rene |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Commandbar on thisworkbook only
I can respect that.
It was my understanding that a Commandbar (toolbar) was also created. In my initial reply, I couldn't remember what code would delete the commandbar, but knew that I had programmed it somewhere that was readable. You were able to straight up answer the question. I think that Rene should be good to go, we just need to await some sort of indication of such. :) "Bob Phillips" wrote: Well I ran the create code and it only created a button, so I gave him code just to remove that button. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "GB" wrote in message ... Actually, this option would remove the button, but not the commandbar. Obviously to remove the commandbar, just remove from the below code the .Controls("myButton") portion, and the commandbar will be deleted. it will error if the user has also deleted it from the View-Toolbars section, so might want some error controls. Just a thought. "Bob Phillips" wrote: Private Sub Workbook_BeforeClose(Cancel As Boolean) Application.CommandBars("Worksheet Menu Bar") _ .Controls("myButton").Delete 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 Bob Phillips (remove nothere from email address if mailing direct) "Rene Petersen" wrote in message ... When creating a commandbar with the code below it is only a temporarily commandbar and that is great, however it is only temp in that sense that you have to close the Excel application down and reopen before the commandbar removed. Is there away to have the commandbar removed as soon as you close the worksheet that has the macro and command? Dim oCb As CommandBar Dim oCtl As CommandBarButton Set oCb = Application.CommandBars("Worksheet Menu Bar") With oCb Set oCtl = .Controls.Add( _ Type:=msoControlButton, _ temporary:=True) oCtl.Caption = "myButton" oCtl.Style = msoButtonCaption oCtl.OnAction = "myMacro" End With Thank you in advance for you help. Rene |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Commandbar on thisworkbook only
Okay, I also wrote a little code to show all of the names of the commandbars
that are "currently" in Excel. The name used by Rene for the toolbar that is getting the button, is a default toolbar. It would, therefore, be unwise to delete that commandbar. The confusion (on my part) stemmed from the discussion that a commandbar was created. It wasn't or normally isn't created, it was just used. So your code of deleting the icon would be the appropriate method to use to restore this particular commandbar back to it's original state. If a new commandbar had been created, then it would have been necessary to delete the whole commandbar, not just the icon(s) created on it. "GB" wrote: I can respect that. It was my understanding that a Commandbar (toolbar) was also created. In my initial reply, I couldn't remember what code would delete the commandbar, but knew that I had programmed it somewhere that was readable. You were able to straight up answer the question. I think that Rene should be good to go, we just need to await some sort of indication of such. :) "Bob Phillips" wrote: Well I ran the create code and it only created a button, so I gave him code just to remove that button. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "GB" wrote in message ... Actually, this option would remove the button, but not the commandbar. Obviously to remove the commandbar, just remove from the below code the .Controls("myButton") portion, and the commandbar will be deleted. it will error if the user has also deleted it from the View-Toolbars section, so might want some error controls. Just a thought. "Bob Phillips" wrote: Private Sub Workbook_BeforeClose(Cancel As Boolean) Application.CommandBars("Worksheet Menu Bar") _ .Controls("myButton").Delete 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 Bob Phillips (remove nothere from email address if mailing direct) "Rene Petersen" wrote in message ... When creating a commandbar with the code below it is only a temporarily commandbar and that is great, however it is only temp in that sense that you have to close the Excel application down and reopen before the commandbar removed. Is there away to have the commandbar removed as soon as you close the worksheet that has the macro and command? Dim oCb As CommandBar Dim oCtl As CommandBarButton Set oCb = Application.CommandBars("Worksheet Menu Bar") With oCb Set oCtl = .Controls.Add( _ Type:=msoControlButton, _ temporary:=True) oCtl.Caption = "myButton" oCtl.Style = msoButtonCaption oCtl.OnAction = "myMacro" End With Thank you in advance for you help. Rene |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CommandBar | Excel Programming | |||
Help with ThisWorkbook | Excel Programming | |||
Help with ThisWorkbook | Excel Programming | |||
Help with ThisWorkbook | Excel Programming | |||
ThisWorkbook | Excel Programming |