Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
custom toolbars to exclusive files.
In Excel 2003,
I was wonder if it was possible to make a custom toolbar to only open in one file. Basically, the toolbar is exclusive to the one file. I don't want to just hide it. I want the toolbar only to work on that one file and not even exist on other files. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
custom toolbars to exclusive files.
Peter,
You can create the toolbar everytime the workbook activates and delete it on deactivate event. Somewhat like this Private Sub Workbook_Activate() Dim cmdbar As CommandBar Set cmdbar = Application.CommandBars.Add("MyCommandBar") cmdbar.Visible = True cmdbar.Position = msoBarTop With Application.CommandBars("Standard") .Controls(1).Copy bar:=cmdbar .Controls(2).Copy bar:=cmdbar End With End Sub Private Sub Workbook_Deactivate() On Error Resume Next Application.CommandBars("MyCommandBar").Delete End Sub -- Hope that helps. Vergel Adriano "Peter Lee" wrote: In Excel 2003, I was wonder if it was possible to make a custom toolbar to only open in one file. Basically, the toolbar is exclusive to the one file. I don't want to just hide it. I want the toolbar only to work on that one file and not even exist on other files. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
custom toolbars to exclusive files.
Hi Vergel,
Your response was helpful for me but unfortunately it didn't work when I tried it. I'm using Excel 2003. I created the toolbar also named "MyCommandBar", then in Visual Basic Editor pasted your code in the "ThisWorkbook" object. When I closed then reopened the file, my toolbar had changed and the buttons I had on there were removed, and replaced by the "New" and "Open" icons. Even when I fixed up the icons on my toolbar again to the buttons that I want, then close and open the file, the toolbar reappears with the "New" and "Open" icons and doesn't seem to save my changes. Any suggestions with this please? "Vergel Adriano" wrote: Peter, You can create the toolbar everytime the workbook activates and delete it on deactivate event. Somewhat like this Private Sub Workbook_Activate() Dim cmdbar As CommandBar Set cmdbar = Application.CommandBars.Add("MyCommandBar") cmdbar.Visible = True cmdbar.Position = msoBarTop With Application.CommandBars("Standard") .Controls(1).Copy bar:=cmdbar .Controls(2).Copy bar:=cmdbar End With End Sub Private Sub Workbook_Deactivate() On Error Resume Next Application.CommandBars("MyCommandBar").Delete End Sub -- Hope that helps. Vergel Adriano "Peter Lee" wrote: In Excel 2003, I was wonder if it was possible to make a custom toolbar to only open in one file. Basically, the toolbar is exclusive to the one file. I don't want to just hide it. I want the toolbar only to work on that one file and not even exist on other files. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
custom toolbars to exclusive files.
Hi Nuzza,
The example that I provided was just to demo how to create a toolbar when a workbook is activated and delete that same toolbar when the workbook is deactivated. For that purpose, the code creates a toolbar with the first two buttons copied from the standard toolbar. Here is the Workbook_Activate code with some comments so you can fit it in your own code. Private Sub Workbook_Activate() Dim cmdbar As CommandBar 'create the toolbar Set cmdbar = Application.CommandBars.Add("MyCommandBar") 'make it visible and docked on top cmdbar.Visible = True cmdbar.Position = msoBarTop 'copy the first two buttons in the Standard toolbar to my toolbar 'replace this part with your own code to create your buttons. With Application.CommandBars("Standard") .Controls(1).Copy bar:=cmdbar .Controls(2).Copy bar:=cmdbar End With End Sub -- Hope that helps. Vergel Adriano "Nuzza" wrote: Hi Vergel, Your response was helpful for me but unfortunately it didn't work when I tried it. I'm using Excel 2003. I created the toolbar also named "MyCommandBar", then in Visual Basic Editor pasted your code in the "ThisWorkbook" object. When I closed then reopened the file, my toolbar had changed and the buttons I had on there were removed, and replaced by the "New" and "Open" icons. Even when I fixed up the icons on my toolbar again to the buttons that I want, then close and open the file, the toolbar reappears with the "New" and "Open" icons and doesn't seem to save my changes. Any suggestions with this please? "Vergel Adriano" wrote: Peter, You can create the toolbar everytime the workbook activates and delete it on deactivate event. Somewhat like this Private Sub Workbook_Activate() Dim cmdbar As CommandBar Set cmdbar = Application.CommandBars.Add("MyCommandBar") cmdbar.Visible = True cmdbar.Position = msoBarTop With Application.CommandBars("Standard") .Controls(1).Copy bar:=cmdbar .Controls(2).Copy bar:=cmdbar End With End Sub Private Sub Workbook_Deactivate() On Error Resume Next Application.CommandBars("MyCommandBar").Delete End Sub -- Hope that helps. Vergel Adriano "Peter Lee" wrote: In Excel 2003, I was wonder if it was possible to make a custom toolbar to only open in one file. Basically, the toolbar is exclusive to the one file. I don't want to just hide it. I want the toolbar only to work on that one file and not even exist on other files. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
custom toolbars to exclusive files.
Thanks, this works better.
So would this work if another user accesses the file? Is there a way that I can store the toolbar in the spreadsheet only, and not have to reference to another toolbar? Basically, to go back to the original question of this thread, I want this toolbar to open only in this one file and closed when the file is closed. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
custom toolbars to exclusive files.
Hi Nuzza,
Yes, I believe it should work if another user opens the file. You don't really store the toolbar in the spreadsheet. But you can put code on the spreadsheet that creates and/or displays the toolbar. The OP was looking for a way to make a toolbar available only to one specific workbook. If the user closes the workbook or switches to another one, he should not see the toolbar. The OP didn't want to just hide the toolbar. It should not exist unless the user is on the specific workbook. Thus, my suggestion was to create the toolbar when the workbook is activated and delete the toolbar on workbook_deactivate. In your case, if you don't want the toolbar being deleted, you can just hide it on the workbook_deactivate. For example: Private Sub Workbook_Deactivate() On Error Resume Next Application.CommandBars("MyCommandBar").Visible=Fa lse End Sub -- Hope that helps. Vergel Adriano "Nuzza" wrote: Thanks, this works better. So would this work if another user accesses the file? Is there a way that I can store the toolbar in the spreadsheet only, and not have to reference to another toolbar? Basically, to go back to the original question of this thread, I want this toolbar to open only in this one file and closed when the file is closed. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Custom Toolbars..? | Excel Discussion (Misc queries) | |||
Custom Toolbars | Excel Programming | |||
Custom Toolbars Again!!! | Excel Programming | |||
Custom toolbars for specific files | Excel Programming | |||
Custom ToolBars | Excel Programming |