Toolbar macros, Personal.xls and the .xlb file

[pmatziaris]

Several of my toolbar buttons are associated with macros. Whenever I
update the toolbars I copy my .xlb file to the other 2 or 3 machines I
work on. However my Personal.xls file is not located in the same
folder on each machine. Consequently after copying the new .xlb file
across to the other machines, I have to re-point each button to the
correct macro. Is there an easier way of doing this - eg. with some
specific macro?

 

[Dave Peterson]

Your life will become much simpler if you include code to create the toolbar
when the workbook is opened and include code to destroy the toolbar when the
workbook is closed.

For additions to the worksheet menu bar, I really like the way John Walkenbach
does it in his menumaker workbook:
http://j-walk.com/ss/excel/tips/tip53.htm

Here's how I do it when I want a toolbar:
http://www.contextures.com/xlToolbar02.html
(from Debra Dalgleish's site)

 

[pmatziaris]

Dear, Dave first of all, i want to thank you about your reply.
I try to create a toolbar following the instructions of the webpage
"http://www.contextures.com/xlToolbar02.html" but i have two problems:
1) i can't change (and keep changed) the icons of toolbar' s buttons
which contains my macros
2) in the same toolbar i want to include severals buttons of standards
toolbars of excel together with my custom buttons(which contains
macros).

Can you tell me please, how can i solve my above problems?

 

[Dave Peterson]

I usually use this routine to build a set of macros that should be run in a
particular order--so I like those numeric icons.

But there are lots of button faces built into excel.

Jim Rech has a utility called BtnFaces.zip
at
http://www.oaltd.co.uk/MVP/Default.htm
that you can use to pick out the id number.

John Green as his version at the same site that's called CBList.zip.

=====
This kind of routine adds an icon from a built in menu:

Option Explicit
Sub testme()

On Error Resume Next
Application.CommandBars("cell").Controls("Format Painter").Delete
On Error GoTo 0

With CommandBars("mytoolbarname")
With .Controls.Add(msoControlButton, temporary:=True)
.Caption = "Format Painter"
.FaceId = Application.CommandBars("standard") _
.FindControl(ID:=108).FaceId
.OnAction = ThisWorkbook.Name & "!formatpainter"
End With
End With

End Sub
Sub FormatPainter()
Application.CommandBars("standard").FindControl(ID:=108).Execute
End Sub

=========
And you can find the id number by using a line like:
MsgBox Application.CommandBars("standard").Controls("format painter").ID

 

[David McRitchie]

What Dave referenced is to purposely create the toolbar when the macro with Open is invoked and purposely removes the toolbar when
finished. Useful with a set of macros that you distribute to others.
http://www.contextures.com/xlToolbar02.html

If you just want to permanently keep your toolbars then you can create them
manually.
Toolbars, Custom Buttons and Menus
(Working with MS Excel Toolbars, Custom Buttons and Menus)
http://www.mvps.org/dmcritchie/excel/toolbars.htm

You might also take a look at John Walkenbach's Menu Maker tip 53
http://www.j-walk.com/ss/excel/tips/tip53.htm
where you layout the menus on a spreadsheet,
you can make the menus permanent by deactivating the macro that
takes it down.


--

 

[pmatziaris]

First of all I want to thank all that they tried with their e-mails to
help me. Also I want to stress in all that I know a few things about
the VBA and cannot modify the routines that you send to me also what
for some simple perhaps reason they do not work in my PC. My initial
(and unique) problem is that I do not want each time where I copy the
excel.xlb in other PC or in other User to change manualy the path in
which it is found the personal.xls for each button of my toolbars
separately. Finally, I can't occupy which way is useful in toolbars the
"John Walkenbach's Menu Maker tip 53" that does show how can somebody
create custom menus?

P.s. : I apologize for the bad knowledge of English language. At least,
I hope you understand the meaning of my written.

 

[Dave Peterson]

I think if you want customized options, you're going to have to be willing to
experiment a little and try to make it work.

The alternative is to live with the standards that you find in those addins.

 

[David McRitchie]

Hi ------,
You should be aware that you should recreate your toolbars
for *each* machine, not copy them among machines.

If you are going to transport your toolbars among your own
machines, try to make sure things are the same:
1) Same version of Excel, or you will probably get a bloated XLB file,
like maybe 10 times the size over a period of time.
2) Same pathname to your personal.xls, Excel remembers even if
you don't.
3) Only download and upload the xlb file when Excel is down,
Backup of an application should always be done with the application
down unless the backup is provided for in the application itself.
4) Above all don't put your XLB file on someone else's machine.
Just like you would not put your browser bookmarks on someone
else's machine.

Keep in mind that Excel saves the XLB file every time that Excel is
closed. In fact that allows you to do an emergency recovery if
you mess up your toolbars as long as you haven't closed or terminated
Excel.
http://www.mvps.org/dmcritchie/excel/toolbar.htm#emergency

Also keep in mind that Excel cannot have two workbooks with the same name
open at the same time, and that applies to your personal.xls file, this is
probably the source of most of your problems. Though with #4 you could
have a lot more at risk than a tool bar.

I would suggest that you run the macro found on my barhopper.htm page so that
can at least have a map of your toolbar layouts and icons, and can print it for reference.
Be aware that your own icons all have the same icon number through Excel 2002.
Barhopper -- fixup for Restored Toolbars, and -- Listing of Menu Items
http://www.mvps.org/dmcritchie/excel/barhopper.htm
http://www.mvps.org/dmcritchie/excel/code/barhopping.txt (debug version)
http://www.mvps.org/dmcritchie/excel/code/barhopper_ws.txt (worksheet version)

Also do a screen print of you toolbar layout, print it and store it in a filing cabinet.
I was glad to have had that a layout of my desktop on several occasions.

If you don't like the way something works and you have the code as with most
help in these newsgroups where macros are concerned and with many
of the add-ins, you can change it (experiment). If your modification works
you have just become a programmer (or the new buzzword "Developer").
somewhat for addins.

If language is a major concern there are several language newsgroups
for instance there is a German newsgroup:
news://msnews.microsoft.com/microsoft.public.de.excel
other international groups:
http://office.microsoft.com/en-au/assistance/ha010873021033.aspx
Though I think you will get a wider range of experience, and help around
the clock (around the world) more in the English language newsgroups.