Worksheet with its own toolbar

[Francis Hookham]

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

 

[Paul B]

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

 

[Vergel Adriano]

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

 

[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

 

[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

 

[Dave Peterson]

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/2004/11/23/faceid-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]

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

 

[Francis Hookham]

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

 

[Dave Peterson]

If you put your images in a worksheet (hidden???), you can copy and paste.

Try googling with the *excel* newsgroups for .pasteface
and you'll see lots of samples.

Here's a thread that you can weed through:

http://snipurl.com/svs7
or
http://groups.google.co.uk/group/mi...nsubject:Excel"&rnum=1&hl=en#5da1e1503eb2cd14

 

[Francis Hookham]

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

 

[Dave Peterson]

Did you create a worksheet with 5 pictures named pic1, pic2, pic3, pic4, pic5 on
it?

For i = LBound(mac_names) To UBound(mac_names)
Worksheets("PictSheetNameHere").Pictures("pic" & i + 1).Copy
With .Controls.Add(Type:=msoControlButton)
.OnAction = "'" & ThisWorkbook.Name & "'!" & mac_names(i)
.Caption = cap_names(i)
.Style = msoButtonIconAndCaption
.PasteFace
.TooltipText = tip_text(i)
End With
Next i
End With

 

[Francis Hookham]

Thanks - yes one sheet has pic1...pic5 and I can see what to do now (I
think!)

I am most grateful

 

[Francis Hookham]

Fantastic - I wish I had stuck with it some years ago.

Thanks Dave and Paul

Francis Hookham

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
For anyone interested here it is - I found it had to be in its own Module
otherwise it did not work
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Option Explicit
Sub create_menubar()
'With thanks to Dave Peterson and Paul B in
'nsnews.microsoft.public.excel.programming
'for much help patience and pointing me in the right direction in May '07
'This first section relates to the 'Main toolbar'
Application.ScreenUpdating = False
Dim i As Long
Dim DoorMacros As Variant 'macro names
Dim DoorCaptions As Variant 'what's on button
Dim DoorTips As Variant 'tip which comes up on mouse-over
'...these are the macros to be called when the button is clicked
DoorMacros = Array("Preparation", _
"TransferSpecsToSched", _
"WindowSchedule", _
"WindowPages", _
"WindowSpecs", _
"WindowAddVert", _
"WindowAddHori", _
"WindowsVertical", _
"WindowsHorizontal", _
"MaxWindow")
'...these are the captions bside each icon in the button
'...they could be left out if icon alone is enough
DoorCaptions = Array("New job", _
"Spec>Sched", _
"Sched", _
"Pages", _
"Specs", _
"Opens V", _
"Open H", _
"Vert", _
"Hori", _
"Maxi")
'...these are the tips which appear when the mouse hovers over the button
DoorTips = Array("BEWARE - this clears everything and starts a new job",
_
"Transfers specifications to Schedule sheet heading
rows", _
"Makes active the Schedule sheet", _
"Makes active the Pages sheet", _
"Makes active the Specs sheet", _
"Opens another sheet vertically", _
"Opens another sheet horizontally", _
"Arranges sheets vertically", _
"Arranges sheets horizontally", _
"Maximises active sheet")
With Application.CommandBars.Add
'...name of toolbar .Name = "Main toolbar"
'...toolbar can open where wanted:-
' .Left = 200
' .Top = 200
.Protection = msoBarNoProtection
.Visible = True
' .Position = msoBarFloating
.Position = msoBarTop
' .Position = msoBarBottom
'...having set up most of the details the toolbar is displayed
For i = LBound(DoorMacros) To UBound(DoorMacros)
Worksheets("Store").Pictures("M" & i + 1).Copy
'...the 16x16 button images (icons) are brought in one by one
'...from the (hidden) sheet "Store"
With .Controls.Add(Type:=msoControlButton)
.OnAction = ThisWorkbook.Name & "!" & DoorMacros(i)
.Caption = DoorCaptions(i)
.Style = msoButtonIconAndCaption
.PasteFace
.TooltipText = DoorTips(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("Main toolbar").Delete
On Error GoTo 0
End Sub

 

[Dave Peterson]

Glad you got it working.

But I put each of your routines in a separate General Module (not behind a
worksheet, not behind thisWorkbook) and your code worked perfectly for me.

But I, too, would put all the code in a single general module -- since it's all
related to building/deleting that toolbar, it makes more organizational sense to
me.