Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macros, Buttons & the XLB file.
Hey folks.
Looking for some help with a custom toolbar, I have three macros stored on one spreadsheet, its contained within the XLstart file as it should be. I've assigned each macro a custom button on a new toolbar. Apart from the fact that you can't seem to move an XLB file to another user's PC succcessfully it also seems that this particular user's instance of Excel isn't saving the toolbar preferences when excel is closed. (I'm told that the XLB file is updated each time excel is shut down.) I'm not a code expert, I'd prefer not to have to record the macros again. I suppose my questions are thus: A: Why would the XLB file not be getting updated on excel closure? and B: Why with the existing XLB file when any of the buttons are pressed do they try to open the spreadsheet containing the macros and not run them. (The sheet is already open, it being in the XLstart file.) Being as I am a somewhat simple person, I'd appreciate any simple answers you might have to offer. (As would our IT department as I'm considering throwing my computer out of a window :-) Thanks. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macros, Buttons & the XLB file.
Don't know about A, but when you add buttons in this way, they point at the
original source. If anything gets moved, It is better to add code to the workbook that builds the toolbars dynamically. -- HTH Bob Phillips (there's no email, no snail mail, but somewhere should be gmail in my addy) "pa_broon74" wrote in message oups.com... Hey folks. Looking for some help with a custom toolbar, I have three macros stored on one spreadsheet, its contained within the XLstart file as it should be. I've assigned each macro a custom button on a new toolbar. Apart from the fact that you can't seem to move an XLB file to another user's PC succcessfully it also seems that this particular user's instance of Excel isn't saving the toolbar preferences when excel is closed. (I'm told that the XLB file is updated each time excel is shut down.) I'm not a code expert, I'd prefer not to have to record the macros again. I suppose my questions are thus: A: Why would the XLB file not be getting updated on excel closure? and B: Why with the existing XLB file when any of the buttons are pressed do they try to open the spreadsheet containing the macros and not run them. (The sheet is already open, it being in the XLstart file.) Being as I am a somewhat simple person, I'd appreciate any simple answers you might have to offer. (As would our IT department as I'm considering throwing my computer out of a window :-) Thanks. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macros, Buttons & the XLB file.
First, I agree with what Bob wrote.
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) ====== My guesses (and only guesses) on the other stuff... A. The user may not have write permission to that folder. The *.xlb file could be marked readonly. Excel could be working fine and saving the *.xlb file. But the user could have other *.xlb files that open after the updated file gets open. The last *.xlb file that opens will be the one you see. But it may not be the one that gets updated when excel closes. I'd search for all the *.xlb files (look in hidden folders, too!) and move/delete them to a safe spot and then see what happens. B. Yep. If the workbook assigned to that button isn't open, then excel will try to open it. And if the user is prompted and allows macros to run (or has a "nice" security setting), then the macro will run. The bad news is that if that button points to a different location on the user's pc than on your pc, then there could be major trouble. That's why Bob recommends building the toolbar on the fly. pa_broon74 wrote: Hey folks. Looking for some help with a custom toolbar, I have three macros stored on one spreadsheet, its contained within the XLstart file as it should be. I've assigned each macro a custom button on a new toolbar. Apart from the fact that you can't seem to move an XLB file to another user's PC succcessfully it also seems that this particular user's instance of Excel isn't saving the toolbar preferences when excel is closed. (I'm told that the XLB file is updated each time excel is shut down.) I'm not a code expert, I'd prefer not to have to record the macros again. I suppose my questions are thus: A: Why would the XLB file not be getting updated on excel closure? and B: Why with the existing XLB file when any of the buttons are pressed do they try to open the spreadsheet containing the macros and not run them. (The sheet is already open, it being in the XLstart file.) Being as I am a somewhat simple person, I'd appreciate any simple answers you might have to offer. (As would our IT department as I'm considering throwing my computer out of a window :-) Thanks. -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macros, Buttons & the XLB file.
At the moment, I'm just creating the toolbar and buttons on each
user's PC (only three of them.) I suppose the XLB question is a bit moot since, even when the toolbar does appear the buttons don't work anyway. Thanks for the help. :-) Just had a thought, the buttons will point to C:\...myusername\app data \microsoft etc etc etc... For it to work on their PC, it'll need to point to their username folder in Documents and Settings... Mmm... I wonder if you can deallocate a button's attributes (take the macro away) then re-add... I've redone the buttons anyway but I hate to get beaten... :-| Thanks again. On 6 Feb, 13:30, Dave Peterson wrote: First, I agree with what Bob wrote. 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) ====== My guesses (and only guesses) on the other stuff... A. The user may not have write permission to that folder. The *.xlb file could be marked readonly. Excel could be working fine and saving the *.xlb file. But the user could have other *.xlb files that open after the updated file gets open. The last *.xlb file that opens will be the one you see. But it may not be the one that gets updated when excel closes. I'd search for all the *.xlb files (look in hidden folders, too!) and move/delete them to a safe spot and then see what happens. B. Yep. If the workbook assigned to that button isn't open, then excel will try to open it. And if the user is prompted and allows macros to run (or has a "nice" security setting), then the macro will run. The bad news is that if that button points to a different location on the user's pc than on your pc, then there could be major trouble. That's why Bob recommends building the toolbar on the fly. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macros, Buttons & the XLB file.
Yep. You can reassign the buttons to the new location.
But it seems to me that you could spend time doing something like that or you could spend the equivalent time to make the code that adds the buttons when the workbook opens. I'd spend time on the second option. pa_broon74 wrote: At the moment, I'm just creating the toolbar and buttons on each user's PC (only three of them.) I suppose the XLB question is a bit moot since, even when the toolbar does appear the buttons don't work anyway. Thanks for the help. :-) Just had a thought, the buttons will point to C:\...myusername\app data \microsoft etc etc etc... For it to work on their PC, it'll need to point to their username folder in Documents and Settings... Mmm... I wonder if you can deallocate a button's attributes (take the macro away) then re-add... I've redone the buttons anyway but I hate to get beaten... :-| Thanks again. On 6 Feb, 13:30, Dave Peterson wrote: First, I agree with what Bob wrote. 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) ====== My guesses (and only guesses) on the other stuff... A. The user may not have write permission to that folder. The *.xlb file could be marked readonly. Excel could be working fine and saving the *.xlb file. But the user could have other *.xlb files that open after the updated file gets open. The last *.xlb file that opens will be the one you see. But it may not be the one that gets updated when excel closes. I'd search for all the *.xlb files (look in hidden folders, too!) and move/delete them to a safe spot and then see what happens. B. Yep. If the workbook assigned to that button isn't open, then excel will try to open it. And if the user is prompted and allows macros to run (or has a "nice" security setting), then the macro will run. The bad news is that if that button points to a different location on the user's pc than on your pc, then there could be major trouble. That's why Bob recommends building the toolbar on the fly. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macros - copying macros from one computer to another | Excel Discussion (Misc queries) | |||
how do I email amacro? | Excel Worksheet Functions | |||
Macros AND Buttons | Excel Worksheet Functions | |||
save excel file from a table delimited file (.txt) using macros | New Users to Excel | |||
Weekly Transaction Processing | Excel Worksheet Functions |