How to save a menu bar

[Guest]

Hi, I made a menu bar, I want to save my customized menu bar in a disk an
copy to another computer, how???

The menu could be copied when I copy a file???

 

[Guest]

This is best accomplished with an Addin... In the addin include coee simlar
to this in ThisWorkbook

Private Sub Workbook_AddinInstall()
AddMyMenu
End Sub

Private Sub Workbook_AddinUninstall()
DeleteMyMenu

End Sub

I know that your code has been improving by leaps and bounds so I will not
go into the whole addin thing unless you want help... Best of Luck

HTH

 

[zackb]

Sorry Jim, gotta disagree with ya there.

I would NOT use AddinInstall/Uninstall, but rather use the
Workbook_Open/BeforeClose events. There is a huge difference between them.
The Install/Uninstall will only fire when you goto Tools | Addins, select
add-in and press Ok. The Open/BeforeClose procedures will fire everytime
you open/close Excel.

If you're looking for functionality that won't happen just on the initial
installation, use the Open/BeforeClose events of the add-ins workbook
module.

 

[Guest]

Interesting... I don't agree but interesting... What functionallity for a
menu bar requires initializing on open or on close? I am hard pressed to come
up with one. (at least for a generic set of utility type functions). However
the before close event fires before Excel closes. If you hit cancel then you
have already lost the menu bar but Excel is still open. This is kind of
annoying. Please fire back a response. I would love to know your reasoning...

 

[Guest]

tks, this was very helpfull

This is best accomplished with an Addin... In the addin include coee simlar
to this in ThisWorkbook

Private Sub Workbook_AddinInstall()
AddMyMenu
End Sub

Private Sub Workbook_AddinUninstall()
DeleteMyMenu

End Sub

I know that your code has been improving by leaps and bounds so I will not
go into the whole addin thing unless you want help... Best of Luck

HTH

 

[zackb]

Create an add-in. Do as you say and create the toolbar on AddinInstall,
then delete it on AddinUninstall. Then install the add-in, then close Excel
and reopen it. Does your toolbar reinstall itself? This is not the true
purpose of an add-in, imho. True, you get add-ins to close and will stay
gone if you press Cancel, but it's better than not having it at all.

Compare these two sets of codes ...

'Private Sub Workbook_AddinInstall()
' CreateMenu
'End Sub
'Private Sub Workbook_AddinUninstall()
' DeleteMenu
'End Sub
''--------------------------------------------------
'Private Sub Workbook_BeforeClose(Cancel As Boolean)
' DeleteMenu
'End Sub
'Private Sub Workbook_Open()
' CreateMenu
'End Sub

Uncomment them individually and run tests on them both.

Unless I missed something (which is highly possible ).

 

[zackb]

... btw, earlier posted code, where CreateMenu and DeleteMenu are seperate
procedures in a Standard Module that do as they imply. Sorry, forgot to
mention that.

 

[Guest]

I must be missing something. I am creating a menu bar. I have tried
installing, uninstalling and reinstalling the addin. I can not create a
situation where the menu does not get re-installed. The only thing that
changed by using your code was the uninstall before close and I lost my state
for the following line of code

ctlCBarControl.State = msoButtonDown

My state is always msoButtonUp when I restart Excel. I can send you my code
to see if I am doing anything terribly different from you...

If I run the code manually without using Tools -> addins I can get rid of
the menu bar while the addin is still installed but by just toggling the
install off and on I can get the menu bar back. And to make this happen I
need to run the code in the VBE so since it is a protected addin the user
can't do that anyway.

Puzzling?

 

[zackb]

Slightly puzzing. Maybe it's in our routines where the difference lies. I
still have the same results. Do you have any Class Modules running code?
Can you either post your routine or email me a sample workbook? If email,
addy is "firefytr at vbaexpress dot com".

 

[Guest]

I'll send you the addin... It creates a menu bar with a bunch of handy
utilities...

 

[Zack Barresse]

Jim,

First things first. Very nice add-in. Interesting coding style you
have there.

What I realized was that I was assuming a great many things, well, one in
particular. When I create add-ins I make them temporary. This behavior is
described by you, in that you exit the Excel Application and "Cancel out"
the add-in is removed (barring correct coding of routine deletion/removal)
but the App remains. Your add-in has the temporary status as False (as
opposed to mine being True). This means that in this same situation, your
add-in will not be removed. It also means that when the (Excel) Application
is loaded again after closure, the menu bar will already be there, even
though it shows being loading.

I don't know if I really see an advantage to having the add-in temporary
status set to False. But then again I can't say I really see a strong
advantage to having it set to True as well.

Anyway, needless to say in all my testing that was the last thing I would
have thought of and it had me going for a while too! And apparently there
is not a lot of difference between the Addin_Install/Uninstall and the
Workbook_Open/BeforeClose event; besides what you have pointed out, which
can be a pain to the user. So I'll have to say that I would go with the
Addin events rather than the Workbook events for the add-ins. (Guess
somebody with common sense is saying, "Doh!" about now..) I actually had to
step away from this test completely and have my friend look at MY add-in
(last place I was going to start looking) to tell me what I had
wrong/different in my testing scenario. It was a learning experience. So
for that, thank you very much!!

 

[Zack Barresse]

Wow, second message. Had a very long response typed and it errored out
sending. Will copy to clipboard on large responses next time!

(Condensed version)
Nice utility Jim. Great coding.
Synopsis of testing: You were right.
Difference? My menubars Temporary status was set to True, not False.
Recommendation(s)? Use the Addin_Install/Uninstall event.
I learned a LOT of new things today. Thank you very much Jim!

 

[Zack Barresse]

Okay, I'ma dork.

Wow, second message. Had a very long response typed and it errored out
sending. Will copy to clipboard on large responses next time!

(Condensed version)
Nice utility Jim. Great coding.
Synopsis of testing: You were right.
Difference? My menubars Temporary status was set to True, not False.
Recommendation(s)? Use the Addin_Install/Uninstall event.
I learned a LOT of new things today. Thank you very much Jim!

--
Regards,
Zack Barresse, aka firefytr


message