Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do you assign macro's to new menu with buttons on Auto_open?
typo..
ctl.ONaction = ThisWorkbook.FullName & str keepITcool |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Assign different buttons by Macro | Excel Discussion (Misc queries) | |||
Assign auto_open to a button | Excel Discussion (Misc queries) | |||
How do you assign macro's to new menu with buttons on Auto_open? | Excel Programming | |||
Why do i have to re-assign macro's all the time | Excel Programming | |||
Assigning Macro's to control buttons. | Excel Programming |