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.

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.
>

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.
> >

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.
> > >

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.

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=False
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.