How do you assign macro's to new menu with buttons on Auto_open?
 

Hi all,

I have some workbooks that all contain the same code give or take
few minor changes, and the all use the same menu bar i created and al
use the same macros for the buttons on this menu bar, my problem i
when i open the workbook on another networked machine i have t
re-assign macros and i pretty much have to do this every time, th
workbook is on a network drive that can be accessed from any PC.

Can anyone help with this?

Thanks,

P.S I dont want to have to create the menubar in vba because the image
of the buttons have been edite

--
Message posted from http://www.ExcelForum.com

How do you assign macro's to new menu with buttons on Auto_open?
 

download translateIT from my d/l page.

the addin itself may not be of interest..
but you can look at & copy the code for the toolbar icons and
(multilingual) menubar creation.
(includes setup of transparent custom icons for xl97 thru xl2003)

although undocumented and 'fairly' complex, imho it well structured
and could/ should? :) be educational.


addin is unprotected, enjoy!


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


Simon Lloyd wrote:

Hi all,

I have some workbooks that all contain the same code give or take a
few minor changes, and the all use the same menu bar i created and all
use the same macros for the buttons on this menu bar, my problem is
when i open the workbook on another networked machine i have to
re-assign macros and i pretty much have to do this every time, the
workbook is on a network drive that can be accessed from any PC.

Can anyone help with this?

Thanks,

P.S I dont want to have to create the menubar in vba because the
images
of the buttons have been edited


---
Message posted from http://www.ExcelForum.com/

How do you assign macro's to new menu with buttons on Auto_open?
 

I have looked at your code Keepitcool but have no idea how to separat
what i need, in fact it seems quite complex.....is there an easie
way?

Simo

--
Message posted from http://www.ExcelForum.com

How do you assign macro's to new menu with buttons on Auto_open?
 

Simon

i would:
centralize all code in 1 book/addin, stored in networklocation

rather than having 10 books with 1 macro called update,
you'd have 1 book with 1 macro called update.

adapt the macro to do different things for different workbooks.
i think you'll find that in the end it will be far easier to maintain.

but that aside:

try adaping following to your situation:

Sub ReAssignAction()
Dim bar As CommandBar
Dim ctl As CommandBarControl
Dim str As String

Set bar = CommandBars("custom 1")
For Each ctl In bar.Controls
str = ctl.OnAction
str = Mid(str, InStrRev(str, "!"))
ctl.action = ThisWorkbook.FullName & str
Next
End Sub


HTH :)




keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


Simon Lloyd wrote:

I have looked at your code Keepitcool but have no idea how to separate
what i need, in fact it seems quite complex.....is there an easier
way?

Simon


---
Message posted from http://www.ExcelForum.com/

How do you assign macro's to new menu with buttons on Auto_open?
 

typo..
ctl.ONaction = ThisWorkbook.FullName & str

keepITcool

How do you assign macro's to new menu with buttons on Auto_open?
 

KeepItCool,

Thanks again for the advice, i don't think i can have one workbook wit
all macro's in because not everyone who will be able to access thes
workbooks (which are in the same folder) will have the same acces
rights (these are set by the network Administrator as lots of machine
are in general use rather than private) so it may cause an error whe
caling on something more than they have been allowed!

Also the code you have kindly supplied......could you add some comment
so i know whats happening as the code is being executed?

if you would like i can send you one of my smaller workbooks (aroun
500Kb) so you can see my code and new menubar.

Simo

--
Message posted from http://www.ExcelForum.com

How do you assign macro's to new menu with buttons on Auto_open?
 

Simon.

Comments? it's 3 lines of code??!!

get the onaction string
instrrev (not available in xl97) finds the position of the last
occurance of the "!"

mid takes the string from that position to the end
(which should result in the string pointing to the procedure)
append thisworkbook.name to the beginning and assign the string to the
original onaction.

In general:

select a word you dont recognize.
Press F1 and help will tell you.

If you want to see what happens when it executes:
Make sure the "Locals" pane is visible.
Then select the line inside the loop and press f9
(this is called a breakpoint , line will be red)
run the procedure, it will stop on the red line.

Now look in the locals pane to the value of your variables.
Further studying: Help topics on debugging


Re not centralizing code:
I dont know your situation. Cant judge.
But:
I would still make all efforts to centralize code in 1 or 2 addins.
I would NEVER have a fixed toolbar and reassign the procedures.
I would ALWAYS build my menus on the fly.

Re mail:
nope. not interested.




keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


Simon Lloyd wrote:

KeepItCool,

Thanks again for the advice, i don't think i can have one workbook
with
all macro's in because not everyone who will be able to access these
workbooks (which are in the same folder) will have the same access
rights (these are set by the network Administrator as lots of machines
are in general use rather than private) so it may cause an error when
caling on something more than they have been allowed!

Also the code you have kindly supplied......could you add some
comments
so i know whats happening as the code is being executed?

if you would like i can send you one of my smaller workbooks (around
500Kb) so you can see my code and new menubar.

Simon


---
Message posted from http://www.ExcelForum.com/