Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet with its own toolbar
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet with its own toolbar
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet with its own toolbar
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet with its own toolbar
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet with its own toolbar
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet with its own toolbar
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet with its own toolbar
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet with its own toolbar
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet with its own toolbar
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/mic...e1503eb2 cd14 Francis Hookham wrote: 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet with its own toolbar
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet with its own toolbar
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 wrote: 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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet with its own toolbar
Fantastic - I wish I had stuck with it some years ago.
Thanks Dave and Paul Francis Hookham xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xx For anyone interested here it is - I found it had to be in its own Module otherwise it did not work xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xx 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", _ "SpecSched", _ "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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet with its own toolbar
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. Francis Hookham wrote: Fantastic - I wish I had stuck with it some years ago. Thanks Dave and Paul Francis Hookham xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xx For anyone interested here it is - I found it had to be in its own Module otherwise it did not work xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xx 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", _ "SpecSched", _ "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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Keep Custom Toolbar with Worksheet | Excel Programming | |||
How do I put a toolbar button into a worksheet? | Excel Worksheet Functions | |||
Worksheet Menu Toolbar | Excel Programming | |||
How do I move my worksheet from under the toolbar? | Excel Discussion (Misc queries) | |||
toolbar macro for esp. worksheet | Excel Programming |