Custom toolbar problem

[JeffT]

Hi

I've read a few posts on this subject and suspect I have the same problem. I would like confirmation and / or suggestions of possible solutions.

I've made a custom toolbar using tools / Customise / Toolbars, Added the buttons changed the images and assigned Macros from 'This workbook'.

All works fine unless I change the filename. I've found the Macro is renamed on saving Filename.xls'!Macroname. If I move the file the Macro reference starts from C: ..... to the old location.

I had Previously written a VBA macro which worked but could only use the built in faces.

First is their anyway to make the current toolbar Macro references relative?

Will the code at the bottom of this post work? I copied from a previous post but haven't had time to do the Lemon test.

Alternatively how do I assign the images I created on the new toolbar to my old toolbar? I've saved them in a hidden sheet in the workbook (though the sizes were enlarged slightly) so that I could paste them into a help file. If I can get this to work my project will be done!

Thanks for all your help so far.

Jeff T



Option Explicit
Sub MyMacro()
Dim FName AsString
FName = ThisWorkbook.FullName
'Your code here...
Application.DisplayAlerts = False
ThisWorkbook.SaveAs Filename:=FName
Application.DisplayAlerts = True
End Sub

[Zack Barresse]

blah
I took the liberty of adding them to your post. :)

[Killian]

Hi Jeff,

First things first, DisplayAlerts = true/false determines whether Excel messages are displayed. If set to false, the messege (e.g. Do you want to save changes?") is processed with the default answer, so I don't think this is what you're looking for.

I always find the best way to deal with toolbars is to build them yourself programatically. That way, you can have direct control over them from the code-base (workbook or addin) that you are using, and their behaviour. It also makes using the custom faces you have very simple.
I've attached a workbook (XL2003) that does this but basically, the principle is this:

Use your Workbook Open and close events call routines that build/delete your custom toolbars.
Your BuildToolbar routine adds a new toolbar to the commandbars collection then adds each button. You can then set the properties for each button, specifically the OnAction property which is the routine name that runs when you click it (this will refer to a routine in that particular project) and then you can refer to your button picture on your hidden worksheet (give each a name to keep things simple), copy it and use the buttons PasteFace method.
Your DeleteToolbar routine runs through all the toolbars in the commandbars collection and deletes yours.

I know its extra code when your projects nearly finished but I think that once you've done it this way you'll never look back!

Hope this helps
K:-)

[JeffT]

Killian

Thanks for that I think that'll do it. As I said I'd already written the code for the toolbar but couldn't figure out how to get the images to stay on the faces. Then I found you could make toolbars easily from the customise / toolbars method.

I've still got the code and have saved the images, so with your extra lines inserted for each button it should all work.

Thanks

Jeff T

Now I've just got to remember how to mark this as solved?

[rsilberfarb]

I have executed the code referenced in this post, and it all worked great.

My only question is, can I change the location and size of the toolbar. When I open the file it places the toolbar right in the center (horizontally) and covers up some important stuff on the form I am creating. I would ultimately like it to be at the top of the screen, just to the right of my form and bigger



ps. Killian-This is all still related to my last project. The format for the input file changed, so I need to change the end process. I stoped doing a mail merege into word, and am just using excel now. Thanks to all of your help last time, I was able to do it all by myself-Thanks Again!!!!

[Killian]

Hi Ross, glad its working out!

Commandbars have Left and Top properties for positioning and also Position (check Excel VBA for the constants... msoFloating etc) so you can set these when you build the toolbar.
If you want your project to "remember" where the user had the toolbar last (and whether it was visible or not) you can write this info to the register then read it back after you build the toolbar

[Cyberdude]

Killian, as usual you have great advice, but I didn't follow one of your last remarks:

you can write this info to the register then read it back after you build the toolbar
Did you mean "registry" instead of "register"?? If not, please explain "register". :dunno

[rsilberfarb]

Killian,

As I expected, you came thru again.

Thanks!!

[Killian]

np, Ross

@Cyberdude
I meant registry, I just sometimes get the whole word/letter/spelling thing wrong and there's no 'compile' command on this text editor...