Setting OnAction Property Fails
in [Programming]
|
From: Tom Ogilvy on 31 Aug 2005 13:46 why do you need custom toolbars if no one is there to use them? -- Regards, Tom Ogilvy "Josh Sale" <jsale(a)tril dot cod> wrote in message news:u9hY8fkrFHA.260(a)TK2MSFTNGP11.phx.gbl... > Thanks for the suggestion. > > Here's our latest theory ... one of my colleagues noticed that this problem > seemed to be related to running these processes on the server in a Remote > Desktop session. Last night we ran over 1,000 messages through this > mechanism from the server's local console with no failures. > > We're in the process of retesting this theory now. > > josh > > > > > "Jim Thomlinson" <jamest(a)tcgiRe-Move-This.com> wrote in message > news:0EAEDEE5-0C9A-475C-BB10-F1F82E6DBAAC(a)microsoft.com... > > The install/unistall events are raised when the Tools-> Addins -> check or > > uncheck is done. In this way you are not creating the toolbar every time > > excel is opened. I was wondering if you might have a conflict if Two > > copies > > of excel are opened almost simultaneously and the xla file is by the other > > instance to create the toolbars. Like I said I don't have trouble with > > multiple instances so this is a bit of a shot in the dark. > > -- > > HTH... > > > > Jim Thomlinson > > > > > > "Josh Sale" wrote: > > > >> Correct, I create the commandbars in the add-in's Workbook_Open event > >> handler and delete them in its Workbook_BeforeClose handler. The new > >> commandbars are created as temporary (4th argument to .Add is True) so > >> that > >> they disappear in the event the code doesn't shutdown normally. I do > >> this > >> because some of our users have multiple shortcuts that let them launch > >> Excel > >> with or without this add-in installed. If they launch without the add-in > >> I > >> don't want them to ever see my commandbars. > >> > >> So a solution that requires that the commandbars be permanent doesn't > >> sound > >> very good to me. > >> > >> I'm a little fuzzy when the AddinInstall/AddinUninstall events are raised > >> as > >> opposed to Workbook_Open/Workbook_BeforeClose. > >> > >> josh > >> > >> > >> > >> "Jim Thomlinson" <jamest(a)tcgiRe-Move-This.com> wrote in message > >> news:01704FE1-1FE0-4083-B4C0-7F7F64434351(a)microsoft.com... > >> > It sound like you are creating and destroying the toolbar at the > >> > workbook > >> > open events. I assume you are destroying the toolbar when you are done. > >> > Instead of using this even try using the AddinInstall event something > >> > like > >> > this... This way your toolbar does not need to be loaded each time and > >> > perhaps your onaction will not have the conflict. > >> > > >> > Private Sub Workbook_AddinInstall() > >> > Call CreateMyToolbar > >> > End Sub > >> > > >> > Private Sub Workbook_AddinUninstall() > >> > Call DestroyMyToolbar > >> > End Sub > >> > -- > >> > HTH... > >> > > >> > Jim Thomlinson > >> > > >> > > >> > "Josh Sale" wrote: > >> > > >> >> No ... a single copy of the add-in in each instance of Excel. > >> >> > >> >> At startup, the add-in creates a few toolbars and puts a number of > >> >> buttons > >> >> on each one. So there is a period where maybe 20 or 30 toolbar > >> >> buttons, > >> >> shortcut menu's, etc have their OnAction property set. And this is > >> >> where > >> >> it > >> >> blows it brains out. > >> >> > >> >> josh > >> >> > >> >> > >> >> > >> >> "Jim Thomlinson" <jamest(a)tcgiRe-Move-This.com> wrote in message > >> >> news:F1B4E722-B033-4AC3-B4F1-5879473937CC(a)microsoft.com... > >> >> > Multiple instances of excel running should not (to the best of my > >> >> > knowledge) > >> >> > cause any difficulty. Each one resides in it's own world and does > >> >> > not > >> >> > interact with other instances. In any one instance of Excel do you > >> >> > have > >> >> > the > >> >> > addin running more than once (I might be a little slow on the uptake > >> >> > but I > >> >> > could not determine that from your post)? > >> >> > -- > >> >> > HTH... > >> >> > > >> >> > Jim Thomlinson > >> >> > > >> >> > > >> >> > "Josh Sale" wrote: > >> >> > > >> >> >> Jim, > >> >> >> > >> >> >> We're actually running multiple instances of Excel and the add-in > >> >> >> intentionally. We have a non-Excel process runs on a server and > >> >> >> that > >> >> >> reads > >> >> >> messages off of a queue. Depending on the content of each message > >> >> >> this > >> >> >> process spawns off a copy of Excel with command line arguments that > >> >> >> are > >> >> >> based on the message content. > >> >> >> > >> >> >> So we want to make this work. > >> >> >> > >> >> >> The add-in isn't added by code. > >> >> >> > >> >> >> josh > >> >> >> > >> >> >> > >> >> >> > >> >> >> "Jim Thomlinson" <jamest(a)tcgiRe-Move-This.com> wrote in message > >> >> >> news:E8DB4F74-2391-4B78-A941-A873F980D20F(a)microsoft.com... > >> >> >> > My question is how are you getting two instances of the addin > >> >> >> > running > >> >> >> > on > >> >> >> > one > >> >> >> > machine concurrently? Solve that and the problems go away... Is > >> >> >> > the > >> >> >> > addin > >> >> >> > installed through code? > >> >> >> > -- > >> >> >> > HTH... > >> >> >> > > >> >> >> > Jim Thomlinson > >> >> >> > > >> >> >> > > >> >> >> > "Josh Sale" wrote: > >> >> >> > > >> >> >> >> I have an add-in that works just fine when there is a single > >> >> >> >> instance > >> >> >> >> of > >> >> >> >> it > >> >> >> >> running on a particular machine. > >> >> >> >> > >> >> >> >> However, if two copies get launched at essentially the same time > >> >> >> >> on > >> >> >> >> the > >> >> >> >> same > >> >> >> >> machine, I get the following error: > >> >> >> >> > >> >> >> >> Method 'OnAction' of object '_CommandBarButton' failed. > >> >> >> >> > >> >> >> >> The runtime error number is 80004005. The failing line of code > >> >> >> >> looks > >> >> >> >> like > >> >> >> >> this: > >> >> >> >> > >> >> >> >> Dim cmdButton As CommandBarButton > >> >> >> >> > >> >> >> >> cmdButton.OnAction = "SomeMacroName" > >> >> >> >> > >> >> >> >> Again, the assignment statement and the resulting toolbar button > >> >> >> >> work > >> >> >> >> just > >> >> >> >> fine when a single instance of Excel and the add-in is running > >> >> >> >> ... > >> >> >> >> the > >> >> >> >> error > >> >> >> >> only happens when two copies are started at the same time. > >> >> >> >> > >> >> >> >> Any thoughts? > >> >> >> >> > >> >> >> >> Do multiple copies of Excel share something that's causing this > >> >> >> >> problem? > >> >> >> >> > >> >> >> >> BTW, am running XL2003. > >> >> >> >> > >> >> >> >> TIA, > >> >> >> >> > >> >> >> >> josh > >> >> >> >> > >> >> >> >> > >> >> >> >> > >> >> >> > >> >> >> > >> >> >> > >> >> > >> >> > >> >> > >> > >> > >> > >
From: "Josh Sale" <jsale@tril dot on 31 Aug 2005 14:20 Well that is the million dollar question! Clearly in general they aren't needed. Unfortunately the add-in contains code that depending on what the user is doing, makes certain comandbars visible or invisible as appropriate. The code which does this assumes that these commandbars exist and will abort if they don't and this code could be stumbled into by the batch activity. I've created an experimental version of the code that creates all of the commandbars but skips the creation of all of the commandbuttons on those bars when operating in batch mode and that seems to work around the immediate problem. But then we run into a different not obviously related problem that I'm still investigating. Tom, you seem to know everything about Excel. Have you ever heard of this kind of problem? Any thoughts about it? Another theory I had was that there was some kind of contention over the user's .xlb file. I don't know if the .xlb file is updated for temporary commandbars or not. I asked for another test to be run where each process ran under its own Windows ID (and would thus have their own .xlb file) and was told that the problem still occurred ... but I'm not sure I trust that test result yet. josh "Tom Ogilvy" <twogilvy(a)msn.com> wrote in message news:%234zCvPlrFHA.3476(a)TK2MSFTNGP10.phx.gbl... > why do you need custom toolbars if no one is there to use them? > > -- > Regards, > Tom Ogilvy > > > "Josh Sale" <jsale(a)tril dot cod> wrote in message > news:u9hY8fkrFHA.260(a)TK2MSFTNGP11.phx.gbl... >> Thanks for the suggestion. >> >> Here's our latest theory ... one of my colleagues noticed that this > problem >> seemed to be related to running these processes on the server in a Remote >> Desktop session. Last night we ran over 1,000 messages through this >> mechanism from the server's local console with no failures. >> >> We're in the process of retesting this theory now. >> >> josh >> >> >> >> >> "Jim Thomlinson" <jamest(a)tcgiRe-Move-This.com> wrote in message >> news:0EAEDEE5-0C9A-475C-BB10-F1F82E6DBAAC(a)microsoft.com... >> > The install/unistall events are raised when the Tools-> Addins -> check > or >> > uncheck is done. In this way you are not creating the toolbar every >> > time >> > excel is opened. I was wondering if you might have a conflict if Two >> > copies >> > of excel are opened almost simultaneously and the xla file is by the > other >> > instance to create the toolbars. Like I said I don't have trouble with >> > multiple instances so this is a bit of a shot in the dark. >> > -- >> > HTH... >> > >> > Jim Thomlinson >> > >> > >> > "Josh Sale" wrote: >> > >> >> Correct, I create the commandbars in the add-in's Workbook_Open event >> >> handler and delete them in its Workbook_BeforeClose handler. The new >> >> commandbars are created as temporary (4th argument to .Add is True) so >> >> that >> >> they disappear in the event the code doesn't shutdown normally. I do >> >> this >> >> because some of our users have multiple shortcuts that let them launch >> >> Excel >> >> with or without this add-in installed. If they launch without the > add-in >> >> I >> >> don't want them to ever see my commandbars. >> >> >> >> So a solution that requires that the commandbars be permanent doesn't >> >> sound >> >> very good to me. >> >> >> >> I'm a little fuzzy when the AddinInstall/AddinUninstall events are > raised >> >> as >> >> opposed to Workbook_Open/Workbook_BeforeClose. >> >> >> >> josh >> >> >> >> >> >> >> >> "Jim Thomlinson" <jamest(a)tcgiRe-Move-This.com> wrote in message >> >> news:01704FE1-1FE0-4083-B4C0-7F7F64434351(a)microsoft.com... >> >> > It sound like you are creating and destroying the toolbar at the >> >> > workbook >> >> > open events. I assume you are destroying the toolbar when you are > done. >> >> > Instead of using this even try using the AddinInstall event >> >> > something >> >> > like >> >> > this... This way your toolbar does not need to be loaded each time > and >> >> > perhaps your onaction will not have the conflict. >> >> > >> >> > Private Sub Workbook_AddinInstall() >> >> > Call CreateMyToolbar >> >> > End Sub >> >> > >> >> > Private Sub Workbook_AddinUninstall() >> >> > Call DestroyMyToolbar >> >> > End Sub >> >> > -- >> >> > HTH... >> >> > >> >> > Jim Thomlinson >> >> > >> >> > >> >> > "Josh Sale" wrote: >> >> > >> >> >> No ... a single copy of the add-in in each instance of Excel. >> >> >> >> >> >> At startup, the add-in creates a few toolbars and puts a number of >> >> >> buttons >> >> >> on each one. So there is a period where maybe 20 or 30 toolbar >> >> >> buttons, >> >> >> shortcut menu's, etc have their OnAction property set. And this is >> >> >> where >> >> >> it >> >> >> blows it brains out. >> >> >> >> >> >> josh >> >> >> >> >> >> >> >> >> >> >> >> "Jim Thomlinson" <jamest(a)tcgiRe-Move-This.com> wrote in message >> >> >> news:F1B4E722-B033-4AC3-B4F1-5879473937CC(a)microsoft.com... >> >> >> > Multiple instances of excel running should not (to the best of my >> >> >> > knowledge) >> >> >> > cause any difficulty. Each one resides in it's own world and does >> >> >> > not >> >> >> > interact with other instances. In any one instance of Excel do >> >> >> > you >> >> >> > have >> >> >> > the >> >> >> > addin running more than once (I might be a little slow on the > uptake >> >> >> > but I >> >> >> > could not determine that from your post)? >> >> >> > -- >> >> >> > HTH... >> >> >> > >> >> >> > Jim Thomlinson >> >> >> > >> >> >> > >> >> >> > "Josh Sale" wrote: >> >> >> > >> >> >> >> Jim, >> >> >> >> >> >> >> >> We're actually running multiple instances of Excel and the >> >> >> >> add-in >> >> >> >> intentionally. We have a non-Excel process runs on a server >> >> >> >> and >> >> >> >> that >> >> >> >> reads >> >> >> >> messages off of a queue. Depending on the content of each > message >> >> >> >> this >> >> >> >> process spawns off a copy of Excel with command line arguments > that >> >> >> >> are >> >> >> >> based on the message content. >> >> >> >> >> >> >> >> So we want to make this work. >> >> >> >> >> >> >> >> The add-in isn't added by code. >> >> >> >> >> >> >> >> josh >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> "Jim Thomlinson" <jamest(a)tcgiRe-Move-This.com> wrote in message >> >> >> >> news:E8DB4F74-2391-4B78-A941-A873F980D20F(a)microsoft.com... >> >> >> >> > My question is how are you getting two instances of the addin >> >> >> >> > running >> >> >> >> > on >> >> >> >> > one >> >> >> >> > machine concurrently? Solve that and the problems go away... >> >> >> >> > Is >> >> >> >> > the >> >> >> >> > addin >> >> >> >> > installed through code? >> >> >> >> > -- >> >> >> >> > HTH... >> >> >> >> > >> >> >> >> > Jim Thomlinson >> >> >> >> > >> >> >> >> > >> >> >> >> > "Josh Sale" wrote: >> >> >> >> > >> >> >> >> >> I have an add-in that works just fine when there is a single >> >> >> >> >> instance >> >> >> >> >> of >> >> >> >> >> it >> >> >> >> >> running on a particular machine. >> >> >> >> >> >> >> >> >> >> However, if two copies get launched at essentially the same > time >> >> >> >> >> on >> >> >> >> >> the >> >> >> >> >> same >> >> >> >> >> machine, I get the following error: >> >> >> >> >> >> >> >> >> >> Method 'OnAction' of object '_CommandBarButton' failed. >> >> >> >> >> >> >> >> >> >> The runtime error number is 80004005. The failing line of > code >> >> >> >> >> looks >> >> >> >> >> like >> >> >> >> >> this: >> >> >> >> >> >> >> >> >> >> Dim cmdButton As CommandBarButton >> >> >> >> >> >> >> >> >> >> cmdButton.OnAction = "SomeMacroName" >> >> >> >> >> >> >> >> >> >> Again, the assignment statement and the resulting toolbar > button >> >> >> >> >> work >> >> >> >> >> just >> >> >> >> >> fine when a single instance of Excel and the add-in is >> >> >> >> >> running >> >> >> >> >> ... >> >> >> >> >> the >> >> >> >> >> error >> >> >> >> >> only happens when two copies are started at the same time. >> >> >> >> >> >> >> >> >> >> Any thoughts? >> >> >> >> >> >> >> >> >> >> Do multiple copies of Excel share something that's causing > this >> >> >> >> >> problem? >> >> >> >> >> >> >> >> >> >> BTW, am running XL2003. >> >> >> >> >> >> >> >> >> >> TIA, >> >> >> >> >> >> >> >> >> >> josh >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> > >
From: Tom Ogilvy on 31 Aug 2005 15:34 I would never claim to know everything and that has been proven many times :) I really had a similar question when I was reading this. How do changes to commandbars in separate instances of excel in the same user id affect the xlb file. I suspect the file isn't updated until the workbook is closed or excel is exited, but I don't actually know. Under the above impression, I wouldn't see temp toolbars updating the file. I guess a way to test would be to create some temp toolbars, then crash excel (maybe a permanent one also). I suspect when reopened, they would be gone. -- Regards, Tom Ogilvy "Josh Sale" <jsale(a)tril dot cod> wrote in message news:uG3ulilrFHA.2272(a)TK2MSFTNGP11.phx.gbl... > Well that is the million dollar question! > > Clearly in general they aren't needed. > > Unfortunately the add-in contains code that depending on what the user is > doing, makes certain comandbars visible or invisible as appropriate. The > code which does this assumes that these commandbars exist and will abort if > they don't and this code could be stumbled into by the batch activity. > > I've created an experimental version of the code that creates all of the > commandbars but skips the creation of all of the commandbuttons on those > bars when operating in batch mode and that seems to work around the > immediate problem. But then we run into a different not obviously related > problem that I'm still investigating. > > Tom, you seem to know everything about Excel. Have you ever heard of this > kind of problem? Any thoughts about it? > > Another theory I had was that there was some kind of contention over the > user's .xlb file. I don't know if the .xlb file is updated for temporary > commandbars or not. I asked for another test to be run where each process > ran under its own Windows ID (and would thus have their own .xlb file) and > was told that the problem still occurred ... but I'm not sure I trust that > test result yet. > > josh > > > > > > "Tom Ogilvy" <twogilvy(a)msn.com> wrote in message > news:%234zCvPlrFHA.3476(a)TK2MSFTNGP10.phx.gbl... > > why do you need custom toolbars if no one is there to use them? > > > > -- > > Regards, > > Tom Ogilvy > > > > > > "Josh Sale" <jsale(a)tril dot cod> wrote in message > > news:u9hY8fkrFHA.260(a)TK2MSFTNGP11.phx.gbl... > >> Thanks for the suggestion. > >> > >> Here's our latest theory ... one of my colleagues noticed that this > > problem > >> seemed to be related to running these processes on the server in a Remote > >> Desktop session. Last night we ran over 1,000 messages through this > >> mechanism from the server's local console with no failures. > >> > >> We're in the process of retesting this theory now. > >> > >> josh > >> > >> > >> > >> > >> "Jim Thomlinson" <jamest(a)tcgiRe-Move-This.com> wrote in message > >> news:0EAEDEE5-0C9A-475C-BB10-F1F82E6DBAAC(a)microsoft.com... > >> > The install/unistall events are raised when the Tools-> Addins -> check > > or > >> > uncheck is done. In this way you are not creating the toolbar every > >> > time > >> > excel is opened. I was wondering if you might have a conflict if Two > >> > copies > >> > of excel are opened almost simultaneously and the xla file is by the > > other > >> > instance to create the toolbars. Like I said I don't have trouble with > >> > multiple instances so this is a bit of a shot in the dark. > >> > -- > >> > HTH... > >> > > >> > Jim Thomlinson > >> > > >> > > >> > "Josh Sale" wrote: > >> > > >> >> Correct, I create the commandbars in the add-in's Workbook_Open event > >> >> handler and delete them in its Workbook_BeforeClose handler. The new > >> >> commandbars are created as temporary (4th argument to .Add is True) so > >> >> that > >> >> they disappear in the event the code doesn't shutdown normally. I do > >> >> this > >> >> because some of our users have multiple shortcuts that let them launch > >> >> Excel > >> >> with or without this add-in installed. If they launch without the > > add-in > >> >> I > >> >> don't want them to ever see my commandbars. > >> >> > >> >> So a solution that requires that the commandbars be permanent doesn't > >> >> sound > >> >> very good to me. > >> >> > >> >> I'm a little fuzzy when the AddinInstall/AddinUninstall events are > > raised > >> >> as > >> >> opposed to Workbook_Open/Workbook_BeforeClose. > >> >> > >> >> josh > >> >> > >> >> > >> >> > >> >> "Jim Thomlinson" <jamest(a)tcgiRe-Move-This.com> wrote in message > >> >> news:01704FE1-1FE0-4083-B4C0-7F7F64434351(a)microsoft.com... > >> >> > It sound like you are creating and destroying the toolbar at the > >> >> > workbook > >> >> > open events. I assume you are destroying the toolbar when you are > > done. > >> >> > Instead of using this even try using the AddinInstall event > >> >> > something > >> >> > like > >> >> > this... This way your toolbar does not need to be loaded each time > > and > >> >> > perhaps your onaction will not have the conflict. > >> >> > > >> >> > Private Sub Workbook_AddinInstall() > >> >> > Call CreateMyToolbar > >> >> > End Sub > >> >> > > >> >> > Private Sub Workbook_AddinUninstall() > >> >> > Call DestroyMyToolbar > >> >> > End Sub > >> >> > -- > >> >> > HTH... > >> >> > > >> >> > Jim Thomlinson > >> >> > > >> >> > > >> >> > "Josh Sale" wrote: > >> >> > > >> >> >> No ... a single copy of the add-in in each instance of Excel. > >> >> >> > >> >> >> At startup, the add-in creates a few toolbars and puts a number of > >> >> >> buttons > >> >> >> on each one. So there is a period where maybe 20 or 30 toolbar > >> >> >> buttons, > >> >> >> shortcut menu's, etc have their OnAction property set. And this is > >> >> >> where > >> >> >> it > >> >> >> blows it brains out. > >> >> >> > >> >> >> josh > >> >> >> > >> >> >> > >> >> >> > >> >> >> "Jim Thomlinson" <jamest(a)tcgiRe-Move-This.com> wrote in message > >> >> >> news:F1B4E722-B033-4AC3-B4F1-5879473937CC(a)microsoft.com... > >> >> >> > Multiple instances of excel running should not (to the best of my > >> >> >> > knowledge) > >> >> >> > cause any difficulty. Each one resides in it's own world and does > >> >> >> > not > >> >> >> > interact with other instances. In any one instance of Excel do > >> >> >> > you > >> >> >> > have > >> >> >> > the > >> >> >> > addin running more than once (I might be a little slow on the > > uptake > >> >> >> > but I > >> >> >> > could not determine that from your post)? > >> >> >> > -- > >> >> >> > HTH... > >> >> >> > > >> >> >> > Jim Thomlinson > >> >> >> > > >> >> >> > > >> >> >> > "Josh Sale" wrote: > >> >> >> > > >> >> >> >> Jim, > >> >> >> >> > >> >> >> >> We're actually running multiple instances of Excel and the > >> >> >> >> add-in > >> >> >> >> intentionally. We have a non-Excel process runs on a server > >> >> >> >> and > >> >> >> >> that > >> >> >> >> reads > >> >> >> >> messages off of a queue. Depending on the content of each > > message > >> >> >> >> this > >> >> >> >> process spawns off a copy of Excel with command line arguments > > that > >> >> >> >> are > >> >> >> >> based on the message content. > >> >> >> >> > >> >> >> >> So we want to make this work. > >> >> >> >> > >> >> >> >> The add-in isn't added by code. > >> >> >> >> > >> >> >> >> josh > >> >> >> >> > >> >> >> >> > >> >> >> >> > >> >> >> >> "Jim Thomlinson" <jamest(a)tcgiRe-Move-This.com> wrote in message > >> >> >> >> news:E8DB4F74-2391-4B78-A941-A873F980D20F(a)microsoft.com... > >> >> >> >> > My question is how are you getting two instances of the addin > >> >> >> >> > running > >> >> >> >> > on > >> >> >> >> > one > >> >> >> >> > machine concurrently? Solve that and the problems go away... > >> >> >> >> > Is > >> >> >> >> > the > >> >> >> >> > addin > >> >> >> >> > installed through code? > >> >> >> >> > -- > >> >> >> >> > HTH... > >> >> >> >> > > >> >> >> >> > Jim Thomlinson > >> >> >> >> > > >> >> >> >> > > >> >> >> >> > "Josh Sale" wrote: > >> >> >> >> > > >> >> >> >> >> I have an add-in that works just fine when there is a single > >> >> >> >> >> instance > >> >> >> >> >> of > >> >> >> >> >> it > >> >> >> >> >> running on a particular machine. > >> >> >> >> >> > >> >> >> >> >> However, if two copies get launched at essentially the same > > time > >> >> >> >> >> on > >> >> >> >> >> the > >> >> >> >> >> same > >> >> >> >> >> machine, I get the following error: > >> >> >> >> >> > >> >> >> >> >> Method 'OnAction' of object '_CommandBarButton' failed. > >> >> >> >> >> > >> >> >> >> >> The runtime error number is 80004005. The failing line of > > code > >> >> >> >> >> looks > >> >> >> >> >> like > >> >> >> >> >> this: > >> >> >> >> >> > >> >> >> >> >> Dim cmdButton As CommandBarButton > >> >> >> >> >> > >> >> >> >> >> cmdButton.OnAction = "SomeMacroName" > >> >> >> >> >> > >> >> >> >> >> Again, the assignment statement and the resulting toolbar > > button > >> >> >> >> >> work > >> >> >> >> >> just > >> >> >> >> >> fine when a single instance of Excel and the add-in is > >> >> >> >> >> running > >> >> >> >> >> ... > >> >> >> >> >> the > >> >> >> >> >> error > >> >> >> >> >> only happens when two copies are started at the same time. > >> >> >> >> >> > >> >> >> >> >> Any thoughts? > >> >> >> >> >> > >> >> >> >> >> Do multiple copies of Excel share something that's causing > > this > >> >> >> >> >> problem? > >> >> >> >> >> > >> >> >> >> >> BTW, am running XL2003. > >> >> >> >> >> > >> >> >> >> >> TIA, > >> >> >> >> >> > >> >> >> >> >> josh > >> >> >> >> >> > >> >> >> >> >> > >> >> >> >> >> > >> >> >> >> > >> >> >> >> > >> >> >> >> > >> >> >> > >> >> >> > >> >> >> > >> >> > >> >> > >> >> > >> > >> > > > > > >
From: "Josh Sale" <jsale@tril dot on 31 Aug 2005 16:02 I deleted my .xlb file, started Excel with my add-in and then crashed Excel and as you suggested, I still had no .xlb file. However, if I close Excel normally, I get a new .xlb file. There is no obvious indication of an existing .xlb file being updated at Excel/add-in startup (which might support the idea of .xlb contention). That is, the timestamp doesn't change until a normal Excel close. So if both instances are running under the same Windows ID and process 1 starts up, reads the .xlb file, process 2 starts up, reads the .xlb file, process 1 wraps up and updates the .xlb file and then process 2 wraps up and over-writes the .xlb file does this explain my abort? It doesn't seem like it. All of our aborts seem to occur during initialization which doesn't seem like a point of .xlb contention. josh "Tom Ogilvy" <twogilvy(a)msn.com> wrote in message news:%23hakEMmrFHA.2212(a)TK2MSFTNGP15.phx.gbl... >I would never claim to know everything and that has been proven many times > :) > > I really had a similar question when I was reading this. How do changes > to > commandbars in separate instances of excel in the same user id affect the > xlb file. I suspect the file isn't updated until the workbook is closed > or > excel is exited, but I don't actually know. > > Under the above impression, I wouldn't see temp toolbars updating the > file. > I guess a way to test would be to create some temp toolbars, then crash > excel (maybe a permanent one also). I suspect when reopened, they would > be > gone. > > -- > Regards, > Tom Ogilvy > > "Josh Sale" <jsale(a)tril dot cod> wrote in message > news:uG3ulilrFHA.2272(a)TK2MSFTNGP11.phx.gbl... >> Well that is the million dollar question! >> >> Clearly in general they aren't needed. >> >> Unfortunately the add-in contains code that depending on what the user is >> doing, makes certain comandbars visible or invisible as appropriate. The >> code which does this assumes that these commandbars exist and will abort > if >> they don't and this code could be stumbled into by the batch activity. >> >> I've created an experimental version of the code that creates all of the >> commandbars but skips the creation of all of the commandbuttons on those >> bars when operating in batch mode and that seems to work around the >> immediate problem. But then we run into a different not obviously >> related >> problem that I'm still investigating. >> >> Tom, you seem to know everything about Excel. Have you ever heard of >> this >> kind of problem? Any thoughts about it? >> >> Another theory I had was that there was some kind of contention over the >> user's .xlb file. I don't know if the .xlb file is updated for temporary >> commandbars or not. I asked for another test to be run where each >> process >> ran under its own Windows ID (and would thus have their own .xlb file) >> and >> was told that the problem still occurred ... but I'm not sure I trust >> that >> test result yet. >> >> josh >> >> >> >> >> >> "Tom Ogilvy" <twogilvy(a)msn.com> wrote in message >> news:%234zCvPlrFHA.3476(a)TK2MSFTNGP10.phx.gbl... >> > why do you need custom toolbars if no one is there to use them? >> > >> > -- >> > Regards, >> > Tom Ogilvy >> > >> > >> > "Josh Sale" <jsale(a)tril dot cod> wrote in message >> > news:u9hY8fkrFHA.260(a)TK2MSFTNGP11.phx.gbl... >> >> Thanks for the suggestion. >> >> >> >> Here's our latest theory ... one of my colleagues noticed that this >> > problem >> >> seemed to be related to running these processes on the server in a > Remote >> >> Desktop session. Last night we ran over 1,000 messages through this >> >> mechanism from the server's local console with no failures. >> >> >> >> We're in the process of retesting this theory now. >> >> >> >> josh >> >> >> >> >> >> >> >> >> >> "Jim Thomlinson" <jamest(a)tcgiRe-Move-This.com> wrote in message >> >> news:0EAEDEE5-0C9A-475C-BB10-F1F82E6DBAAC(a)microsoft.com... >> >> > The install/unistall events are raised when the Tools-> Addins -> > check >> > or >> >> > uncheck is done. In this way you are not creating the toolbar every >> >> > time >> >> > excel is opened. I was wondering if you might have a conflict if Two >> >> > copies >> >> > of excel are opened almost simultaneously and the xla file is by the >> > other >> >> > instance to create the toolbars. Like I said I don't have trouble > with >> >> > multiple instances so this is a bit of a shot in the dark. >> >> > -- >> >> > HTH... >> >> > >> >> > Jim Thomlinson >> >> > >> >> > >> >> > "Josh Sale" wrote: >> >> > >> >> >> Correct, I create the commandbars in the add-in's Workbook_Open > event >> >> >> handler and delete them in its Workbook_BeforeClose handler. The > new >> >> >> commandbars are created as temporary (4th argument to .Add is True) > so >> >> >> that >> >> >> they disappear in the event the code doesn't shutdown normally. I > do >> >> >> this >> >> >> because some of our users have multiple shortcuts that let them > launch >> >> >> Excel >> >> >> with or without this add-in installed. If they launch without the >> > add-in >> >> >> I >> >> >> don't want them to ever see my commandbars. >> >> >> >> >> >> So a solution that requires that the commandbars be permanent > doesn't >> >> >> sound >> >> >> very good to me. >> >> >> >> >> >> I'm a little fuzzy when the AddinInstall/AddinUninstall events are >> > raised >> >> >> as >> >> >> opposed to Workbook_Open/Workbook_BeforeClose. >> >> >> >> >> >> josh >> >> >> >> >> >> >> >> >> >> >> >> "Jim Thomlinson" <jamest(a)tcgiRe-Move-This.com> wrote in message >> >> >> news:01704FE1-1FE0-4083-B4C0-7F7F64434351(a)microsoft.com... >> >> >> > It sound like you are creating and destroying the toolbar at the >> >> >> > workbook >> >> >> > open events. I assume you are destroying the toolbar when you are >> > done. >> >> >> > Instead of using this even try using the AddinInstall event >> >> >> > something >> >> >> > like >> >> >> > this... This way your toolbar does not need to be loaded each >> >> >> > time >> > and >> >> >> > perhaps your onaction will not have the conflict. >> >> >> > >> >> >> > Private Sub Workbook_AddinInstall() >> >> >> > Call CreateMyToolbar >> >> >> > End Sub >> >> >> > >> >> >> > Private Sub Workbook_AddinUninstall() >> >> >> > Call DestroyMyToolbar >> >> >> > End Sub >> >> >> > -- >> >> >> > HTH... >> >> >> > >> >> >> > Jim Thomlinson >> >> >> > >> >> >> > >> >> >> > "Josh Sale" wrote: >> >> >> > >> >> >> >> No ... a single copy of the add-in in each instance of Excel. >> >> >> >> >> >> >> >> At startup, the add-in creates a few toolbars and puts a number > of >> >> >> >> buttons >> >> >> >> on each one. So there is a period where maybe 20 or 30 toolbar >> >> >> >> buttons, >> >> >> >> shortcut menu's, etc have their OnAction property set. And this > is >> >> >> >> where >> >> >> >> it >> >> >> >> blows it brains out. >> >> >> >> >> >> >> >> josh >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> "Jim Thomlinson" <jamest(a)tcgiRe-Move-This.com> wrote in message >> >> >> >> news:F1B4E722-B033-4AC3-B4F1-5879473937CC(a)microsoft.com... >> >> >> >> > Multiple instances of excel running should not (to the best of > my >> >> >> >> > knowledge) >> >> >> >> > cause any difficulty. Each one resides in it's own world and > does >> >> >> >> > not >> >> >> >> > interact with other instances. In any one instance of Excel do >> >> >> >> > you >> >> >> >> > have >> >> >> >> > the >> >> >> >> > addin running more than once (I might be a little slow on the >> > uptake >> >> >> >> > but I >> >> >> >> > could not determine that from your post)? >> >> >> >> > -- >> >> >> >> > HTH... >> >> >> >> > >> >> >> >> > Jim Thomlinson >> >> >> >> > >> >> >> >> > >> >> >> >> > "Josh Sale" wrote: >> >> >> >> > >> >> >> >> >> Jim, >> >> >> >> >> >> >> >> >> >> We're actually running multiple instances of Excel and the >> >> >> >> >> add-in >> >> >> >> >> intentionally. We have a non-Excel process runs on a server >> >> >> >> >> and >> >> >> >> >> that >> >> >> >> >> reads >> >> >> >> >> messages off of a queue. Depending on the content of each >> > message >> >> >> >> >> this >> >> >> >> >> process spawns off a copy of Excel with command line >> >> >> >> >> arguments >> > that >> >> >> >> >> are >> >> >> >> >> based on the message content. >> >> >> >> >> >> >> >> >> >> So we want to make this work. >> >> >> >> >> >> >> >> >> >> The add-in isn't added by code. >> >> >> >> >> >> >> >> >> >> josh >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> "Jim Thomlinson" <jamest(a)tcgiRe-Move-This.com> wrote in > message >> >> >> >> >> news:E8DB4F74-2391-4B78-A941-A873F980D20F(a)microsoft.com... >> >> >> >> >> > My question is how are you getting two instances of the > addin >> >> >> >> >> > running >> >> >> >> >> > on >> >> >> >> >> > one >> >> >> >> >> > machine concurrently? Solve that and the problems go >> >> >> >> >> > away... >> >> >> >> >> > Is >> >> >> >> >> > the >> >> >> >> >> > addin >> >> >> >> >> > installed through code? >> >> >> >> >> > -- >> >> >> >> >> > HTH... >> >> >> >> >> > >> >> >> >> >> > Jim Thomlinson >> >> >> >> >> > >> >> >> >> >> > >> >> >> >> >> > "Josh Sale" wrote: >> >> >> >> >> > >> >> >> >> >> >> I have an add-in that works just fine when there is a > single >> >> >> >> >> >> instance >> >> >> >> >> >> of >> >> >> >> >> >> it >> >> >> >> >> >> running on a particular machine. >> >> >> >> >> >> >> >> >> >> >> >> However, if two copies get launched at essentially the >> >> >> >> >> >> same >> > time >> >> >> >> >> >> on >> >> >> >> >> >> the >> >> >> >> >> >> same >> >> >> >> >> >> machine, I get the following error: >> >> >> >> >> >> >> >> >> >> >> >> Method 'OnAction' of object '_CommandBarButton' failed. >> >> >> >> >> >> >> >> >> >> >> >> The runtime error number is 80004005. The failing line of >> > code >> >> >> >> >> >> looks >> >> >> >> >> >> like >> >> >> >> >> >> this: >> >> >> >> >> >> >> >> >> >> >> >> Dim cmdButton As CommandBarButton >> >> >> >> >> >> >> >> >> >> >> >> cmdButton.OnAction = "SomeMacroName" >> >> >> >> >> >> >> >> >> >> >> >> Again, the assignment statement and the resulting toolbar >> > button >> >> >> >> >> >> work >> >> >> >> >> >> just >> >> >> >> >> >> fine when a single instance of Excel and the add-in is >> >> >> >> >> >> running >> >> >> >> >> >> ... >> >> >> >> >> >> the >> >> >> >> >> >> error >> >> >> >> >> >> only happens when two copies are started at the same time. >> >> >> >> >> >> >> >> >> >> >> >> Any thoughts? >> >> >> >> >> >> >> >> >> >> >> >> Do multiple copies of Excel share something that's causing >> > this >> >> >> >> >> >> problem? >> >> >> >> >> >> >> >> >> >> >> >> BTW, am running XL2003. >> >> >> >> >> >> >> >> >> >> >> >> TIA, >> >> >> >> >> >> >> >> >> >> >> >> josh >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> > >> > >> >> > >
First
|
Prev
|
Pages: 1 2 3 Prev: I want to use a oleDragDrop in an Excel VBA Userform Next: Macro with Essbase Add-in |