A workbook I am working on would benefit from having its own custom toolbar
with custom buttons running macros in the workbook. The toolbar to open and
close with the workbook.



Guidance please.



Francis Hookham

Francis, Have a look at the code here by David Peterson and see if that will
help you http://tinyurl.com/3c8qtu

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

"Francis Hookham" wrote in message
...
A workbook I am working on would benefit from having its own custom
toolbar
with custom buttons running macros in the workbook. The toolbar to open
and
close with the workbook.



Guidance please.



Francis Hookham

Fantastic - thank you for pointing me in the right direction.
It took a good bit of sorting out because I wanted the toolbar (two
actually) to run from the workbook itself (not from PERSONAL.xls) so it
could be used on another computer.
Relative positions of the

Option Explicit
Sub create_menubar()
xxx
xxx
End Sub

and the Dims set for the rest of the Module seemed to be a problem so I put
the Option Explicit Sub in another Module. Then all was plain sailing.

Never being satisfied there are two further questions on the same subject:

1 Can the toolbar and/or button colours be changed for easier
identification.
2 Better still could there be a button image instead of the caption, also
for easier identification, as with Custom toolbars? Wishful thinking I
expect.

What I already have is great - thank you.

Francis Hookham

"Paul B" wrote in message
...
Francis, Have a look at the code here by David Peterson and see if that
will
help you http://tinyurl.com/3c8qtu

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

"Francis Hookham" wrote in message
...
A workbook I am working on would benefit from having its own custom
toolbar
with custom buttons running macros in the workbook. The toolbar to open
and
close with the workbook.



Guidance please.



Francis Hookham

Oh! Another question!

The code includes
.Left = 200
.Top = 150
to position the toolbar

Is there a way of opening to toolbar positioned at the botom of the window,
rather than floating. I tried draging the toolbar while recording but
nothing was recorded.

Thanks

Francis Hookham


"Paul B" wrote in message
...
Francis, Have a look at the code here by David Peterson and see if that
will
help you http://tinyurl.com/3c8qtu

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

"Francis Hookham" wrote in message
...
A workbook I am working on would benefit from having its own custom
toolbar
with custom buttons running macros in the workbook. The toolbar to open
and
close with the workbook.



Guidance please.



Francis Hookham

Debra Dalgleish's site has a more current version:

Drop the .left and .top stuff and change this:
..Position = msoBarFloating
to
..Position = msoBarBottom

I use this kind of toolbar for lots of things--but mostly for workbook specific
stuff. I wanted to run macros in a start, stop, start, stop... fashion.

So I used a faceid for the buttons that looked like numbers--1, 2, 3, ... (It
fails when I exceed too many buttons (9 or 10???). (That's what: ".FaceId = 71
+ iCtr" does.)

But you could create another array that holds the button faces that you want and
use that.

Excel has lots of built in icons that you can use--if you know the numbers!

So if you're looking for some of the built-in icons:

Doug Clancy's:
http://www.dicks-blog.com/archives/2...-viewer-addin/

John Walkenbach's:
http://j-walk.com/ss/excel/tips/tip67.htm

Jim Rech's:
http://www.oaltd.co.uk/MVP/MVPPage.asp#JimRech



Francis Hookham wrote:

Oh! Another question!

The code includes
.Left = 200
.Top = 150
to position the toolbar

Is there a way of opening to toolbar positioned at the botom of the window,
rather than floating. I tried draging the toolbar while recording but
nothing was recorded.

Thanks

Francis Hookham

"Paul B" wrote in message
...
Francis, Have a look at the code here by David Peterson and see if that
will
help you http://tinyurl.com/3c8qtu

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

"Francis Hookham" wrote in message
...
A workbook I am working on would benefit from having its own custom
toolbar
with custom buttons running macros in the workbook. The toolbar to open
and
close with the workbook.



Guidance please.



Francis Hookham





--

Dave Peterson

Many thanks Dave - all great stuff which will enhance what I have done so
far - even at first glance I can see the links are going to be very useful.

Francis Hookham


"Dave Peterson" wrote in message
...
Debra Dalgleish's site has a more current version:

Drop the .left and .top stuff and change this:
.Position = msoBarFloating
to
.Position = msoBarBottom

I use this kind of toolbar for lots of things--but mostly for workbook
specific
stuff. I wanted to run macros in a start, stop, start, stop... fashion.

So I used a faceid for the buttons that looked like numbers--1, 2, 3, ...
(It
fails when I exceed too many buttons (9 or 10???). (That's what:
".FaceId = 71
+ iCtr" does.)

But you could create another array that holds the button faces that you
want and
use that.

Excel has lots of built in icons that you can use--if you know the
numbers!

So if you're looking for some of the built-in icons:

Doug Clancy's:
http://www.dicks-blog.com/archives/2...-viewer-addin/

John Walkenbach's:
http://j-walk.com/ss/excel/tips/tip67.htm

Jim Rech's:
http://www.oaltd.co.uk/MVP/MVPPage.asp#JimRech



Francis Hookham wrote:

Oh! Another question!

The code includes
.Left = 200
.Top = 150
to position the toolbar

Is there a way of opening to toolbar positioned at the botom of the
window,
rather than floating. I tried draging the toolbar while recording but
nothing was recorded.

Thanks

Francis Hookham

"Paul B" wrote in message
...
Francis, Have a look at the code here by David Peterson and see if that
will
help you http://tinyurl.com/3c8qtu

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from
it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

"Francis Hookham" wrote in message
...
A workbook I am working on would benefit from having its own custom
toolbar
with custom buttons running macros in the workbook. The toolbar to
open
and
close with the workbook.



Guidance please.



Francis Hookham





--

Dave Peterson

Paul or Dave - just one more problem regarding the toolbar within the
workbook. It is working well but I should like to do away with the icon and
caption produced by the following
.Style = msoButtonIconAndCaption
.FaceId = 71 + i

Changing to
.Style = msoButtonIcon
gets the built-in ButtonIcon without the Caption but I should like to use my
own 16x16 bitmap pictures pasted into one of the sheets of the workbook. How
can I refer to them so they are picked up by
.FaceId = i

I don't know how to name them concecutively so they can be brought in by
'i'.

I do hope you can help,

Francis Hookham


For i = LBound(DoorMacros) To UBound(DoorMacros)
With .Controls.Add(Type:=msoControlButton)
.OnAction = ThisWorkbook.Name & "!" & DoorMacros(i)
.Caption = DoorCaptions(i)
' .Style = msoButtonIconAndCaption
.Style = msoButtonIcon
.FaceId = 71 + i
.TooltipText = DoorTips(i)
End With
Next i

Paul or Dave

Sorry - I'm still stuck, try as I may trying to understand the examples you
pointed me to. I simply cannot see how to get my toolbar icons into the
spreadsheet specific toolbar - the code below works fine and produces a
toolbar with 1 to 5 as the icons and "Hori" to "Specs" as the captions.

I should like to use the five 16 x 16 Paint images pasted into a hidden
sheet "Store" within the workbook and each renamed "Pic1" to "Pic5" as the
respective toolbar icons. I'm not sure yet if I shall keep the captions but
they are easy to leave out.

Please show me how to replace
.FaceId = 71 + i
with "Pic1" to "Pic5"

Thank you,

Francis Hookham


Option Explicit
Sub create_menubar()
Dim i As Long
Dim PrepMacros As Variant 'macro names
Dim PrepCaptions As Variant 'what's on button
Dim PrepTips As Variant 'tip which comes up on mouse-over
Dim PrepPix As Variant 'toolbar icon
Call remove_menubar
PrepMacros = Array("WindowsHorizontal", _
"WindowsVertical", _
"WindowPages", _
"WindowSchedule", _
"WindowSpecs")
PrepCaptions = Array("Hori", _
"Vert", _
"Pages", _
"Sched", _
"Specs")
PrepTips = Array("Arranges windows horizontally", _
"Arranges windows vertically", _
"Pages", _
"Sched", _
"Specs")
With Application.CommandBars.Add
.Name = "Prep buttons"
.Protection = msoBarNoProtection
.Visible = True
.Position = msoBarBottom
For i = LBound(PrepMacros) To UBound(PrepMacros)
With .Controls.Add(Type:=msoControlButton)
.OnAction = ThisWorkbook.Name & PrepMacros(i)
.Caption = PrepCaptions(i)
.Style = msoButtonIconAndCaption
.FaceId = 71 + i
.TooltipText = PrepTips(i)
End With
Next i
End With
End Sub

Sub auto_open()
create_menubar
End Sub

Sub auto_close()
remove_menubar
End Sub

Sub remove_menubar()
On Error Resume Next
Application.CommandBars("Prep buttons").Delete
Application.CommandBars("Door buttons").Delete
On Error GoTo 0
End Sub

Francis,

try something like this in the ThisWorkbook module:

When the workbook activates, it will create a toolbar with 2 buttons copied
from the standard toolbar. Replace that part with ones the runs your macro.
On workbook de-activate, it deletes the toolbar.

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


"Francis Hookham" wrote:

A workbook I am working on would benefit from having its own custom toolbar
with custom buttons running macros in the workbook. The toolbar to open and
close with the workbook.



Guidance please.



Francis Hookham