Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Form Object Difficulties.
Hello All,
Many thanks if you can help with my particular problem. Will offer as much of my wisdom as I can in return (most of which has been stolen off places like here though). I have a fairly complex Excel file that uses forms, events and commandbars to help the user navigate its functionality. Forms are initialised through a custom commandbar, which is itself handled by worksheet and workbook events (to initialise upon activating/opening, remove upon deactivating/closing and modify its contents depending upon which sheet is selected). Everything works fine until I have two spreadsheets open. As all these spreadsheets are based on the same template, they all contain the same macros and forms, etc. and therefore the same names are used. I can force (through capturing events) the commandbar to run the correct subroutine from the module specific to the worksheet (by building up the filename and subroutine name and using Application.Run). When the subroutine activates the form (e.g. UserForm1.Show), however, debugging shows that it seems to run the form from the latest file that I opened instead of the one I initialised things from. This then gives me Run Time 1004 _Global errors when the form code starts using Range.Selections. Rather than do some kind of bodge fix for all of my Range and Sheet selections, I'd prefer to run the correct form from the file itself. Is there a way, similar to the above Application.Run, whereby I can specify the file to run the form from? I've tried the VBComponents to hopefully define the form's object heirarchy, but I keep getting the "Programmatic access to visual basic project is not trusted" error and as there are many potential users of my template I don't want to have everyone compromise their security settings just to use the file. Again, thanks for any assistance you can offer. If you want any of my (poorly written) code to help diagnose, then please shout. Cheers, Ross. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Form Object Difficulties.
Rawce,
Keep all of your code in a separate add-in that way there is only one copy of the code that can be accessed by whichever workbook is active at the time without confusion. Regards, Alan Rawce wrote: Hello All, Many thanks if you can help with my particular problem. Will offer as much of my wisdom as I can in return (most of which has been stolen off places like here though). I have a fairly complex Excel file that uses forms, events and commandbars to help the user navigate its functionality. Forms are initialised through a custom commandbar, which is itself handled by worksheet and workbook events (to initialise upon activating/opening, remove upon deactivating/closing and modify its contents depending upon which sheet is selected). Everything works fine until I have two spreadsheets open. As all these spreadsheets are based on the same template, they all contain the same macros and forms, etc. and therefore the same names are used. I can force (through capturing events) the commandbar to run the correct subroutine from the module specific to the worksheet (by building up the filename and subroutine name and using Application.Run). When the subroutine activates the form (e.g. UserForm1.Show), however, debugging shows that it seems to run the form from the latest file that I opened instead of the one I initialised things from. This then gives me Run Time 1004 _Global errors when the form code starts using Range.Selections. Rather than do some kind of bodge fix for all of my Range and Sheet selections, I'd prefer to run the correct form from the file itself. Is there a way, similar to the above Application.Run, whereby I can specify the file to run the form from? I've tried the VBComponents to hopefully define the form's object heirarchy, but I keep getting the "Programmatic access to visual basic project is not trusted" error and as there are many potential users of my template I don't want to have everyone compromise their security settings just to use the file. Again, thanks for any assistance you can offer. If you want any of my (poorly written) code to help diagnose, then please shout. Cheers, Ross. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Form Object Difficulties.
Add in? Is that similar to Word in that you can apply a template which
pulls in a common module/form/object? Is that accessed through the Add In Manager? If so, I'll look into it. Maintaining a single file will be a hell of a lot easier than updating multiple instances when I issue bug fixes/enhancements! Many thanks, Ross. Alan wrote: Rawce, Keep all of your code in a separate add-in that way there is only one copy of the code that can be accessed by whichever workbook is active at the time without confusion. Regards, Alan Rawce wrote: Hello All, Many thanks if you can help with my particular problem. Will offer as much of my wisdom as I can in return (most of which has been stolen off places like here though). I have a fairly complex Excel file that uses forms, events and commandbars to help the user navigate its functionality. Forms are initialised through a custom commandbar, which is itself handled by worksheet and workbook events (to initialise upon activating/opening, remove upon deactivating/closing and modify its contents depending upon which sheet is selected). Everything works fine until I have two spreadsheets open. As all these spreadsheets are based on the same template, they all contain the same macros and forms, etc. and therefore the same names are used. I can force (through capturing events) the commandbar to run the correct subroutine from the module specific to the worksheet (by building up the filename and subroutine name and using Application.Run). When the subroutine activates the form (e.g. UserForm1.Show), however, debugging shows that it seems to run the form from the latest file that I opened instead of the one I initialised things from. This then gives me Run Time 1004 _Global errors when the form code starts using Range.Selections. Rather than do some kind of bodge fix for all of my Range and Sheet selections, I'd prefer to run the correct form from the file itself. Is there a way, similar to the above Application.Run, whereby I can specify the file to run the form from? I've tried the VBComponents to hopefully define the form's object heirarchy, but I keep getting the "Programmatic access to visual basic project is not trusted" error and as there are many potential users of my template I don't want to have everyone compromise their security settings just to use the file. Again, thanks for any assistance you can offer. If you want any of my (poorly written) code to help diagnose, then please shout. Cheers, Ross. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Form Object Difficulties.
Ross,
I suppose that there are similarities. You'll need to convert a copy of your spreadsheet to an add-in (set the IsAddin property to true in ThisWorkbook - I suggest you also strip out the content of the worksheets because this extra content will simply add weight without being of use) and save it as with a .xla extension then reference the add-in within the spreadsheet you want to use it in so that it is available. It's not difficult but you may find it a bit tricky when you do it for the first time ... but then you will be laughing! Alan Rawce wrote: Add in? Is that similar to Word in that you can apply a template which pulls in a common module/form/object? Is that accessed through the Add In Manager? If so, I'll look into it. Maintaining a single file will be a hell of a lot easier than updating multiple instances when I issue bug fixes/enhancements! Many thanks, Ross. Alan wrote: Rawce, Keep all of your code in a separate add-in that way there is only one copy of the code that can be accessed by whichever workbook is active at the time without confusion. Regards, Alan Rawce wrote: Hello All, Many thanks if you can help with my particular problem. Will offer as much of my wisdom as I can in return (most of which has been stolen off places like here though). I have a fairly complex Excel file that uses forms, events and commandbars to help the user navigate its functionality. Forms are initialised through a custom commandbar, which is itself handled by worksheet and workbook events (to initialise upon activating/opening, remove upon deactivating/closing and modify its contents depending upon which sheet is selected). Everything works fine until I have two spreadsheets open. As all these spreadsheets are based on the same template, they all contain the same macros and forms, etc. and therefore the same names are used. I can force (through capturing events) the commandbar to run the correct subroutine from the module specific to the worksheet (by building up the filename and subroutine name and using Application.Run). When the subroutine activates the form (e.g. UserForm1.Show), however, debugging shows that it seems to run the form from the latest file that I opened instead of the one I initialised things from. This then gives me Run Time 1004 _Global errors when the form code starts using Range.Selections. Rather than do some kind of bodge fix for all of my Range and Sheet selections, I'd prefer to run the correct form from the file itself. Is there a way, similar to the above Application.Run, whereby I can specify the file to run the form from? I've tried the VBComponents to hopefully define the form's object heirarchy, but I keep getting the "Programmatic access to visual basic project is not trusted" error and as there are many potential users of my template I don't want to have everyone compromise their security settings just to use the file. Again, thanks for any assistance you can offer. If you want any of my (poorly written) code to help diagnose, then please shout. Cheers, Ross. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Form Object Difficulties.
Yes, I've gone down that route. The advantages are threefold:
1. Eliminates the problem when running two different spreadsheets with the same forms/modules/objects; 2. Halves the size of the spreadsheets (down to 250Kb) so they run quicker (especially when you've got multiple ones open); 3. Means I only have to maintain one instance of the module/forms instead of potentially 100 different files. The only potential problem is that .xlas aren't specific to certain files (like the .dots in Word can be), so once I've added this to many people's Excel, there's the possibility of them running the form on other files not specific to this project. Hopefully I've made it specific enough so the commandbar only appears on certain named worksheets, plus I've made private as many macros as I can, but we'll have to see. I have told everyone that they can remove the .xla once they've finished their involvement on the project, but I reckon most would forget. Anyway, thanks for pointing me down the right route, you've probably saved me a load of effort in the long run, plus added a bit more robustness to my work. Cheers, Ross. Alan wrote: Ross, I suppose that there are similarities. You'll need to convert a copy of your spreadsheet to an add-in (set the IsAddin property to true in ThisWorkbook - I suggest you also strip out the content of the worksheets because this extra content will simply add weight without being of use) and save it as with a .xla extension then reference the add-in within the spreadsheet you want to use it in so that it is available. It's not difficult but you may find it a bit tricky when you do it for the first time ... but then you will be laughing! Alan Rawce wrote: Add in? Is that similar to Word in that you can apply a template which pulls in a common module/form/object? Is that accessed through the Add In Manager? If so, I'll look into it. Maintaining a single file will be a hell of a lot easier than updating multiple instances when I issue bug fixes/enhancements! Many thanks, Ross. Alan wrote: Rawce, Keep all of your code in a separate add-in that way there is only one copy of the code that can be accessed by whichever workbook is active at the time without confusion. Regards, Alan Rawce wrote: Hello All, Many thanks if you can help with my particular problem. Will offer as much of my wisdom as I can in return (most of which has been stolen off places like here though). I have a fairly complex Excel file that uses forms, events and commandbars to help the user navigate its functionality. Forms are initialised through a custom commandbar, which is itself handled by worksheet and workbook events (to initialise upon activating/opening, remove upon deactivating/closing and modify its contents depending upon which sheet is selected). Everything works fine until I have two spreadsheets open. As all these spreadsheets are based on the same template, they all contain the same macros and forms, etc. and therefore the same names are used. I can force (through capturing events) the commandbar to run the correct subroutine from the module specific to the worksheet (by building up the filename and subroutine name and using Application.Run). When the subroutine activates the form (e.g. UserForm1.Show), however, debugging shows that it seems to run the form from the latest file that I opened instead of the one I initialised things from. This then gives me Run Time 1004 _Global errors when the form code starts using Range.Selections. Rather than do some kind of bodge fix for all of my Range and Sheet selections, I'd prefer to run the correct form from the file itself. Is there a way, similar to the above Application.Run, whereby I can specify the file to run the form from? I've tried the VBComponents to hopefully define the form's object heirarchy, but I keep getting the "Programmatic access to visual basic project is not trusted" error and as there are many potential users of my template I don't want to have everyone compromise their security settings just to use the file. Again, thanks for any assistance you can offer. If you want any of my (poorly written) code to help diagnose, then please shout. Cheers, Ross. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to put a calader object on a form??? | Excel Programming | |||
Pass a form Listbox as an object | Excel Programming | |||
Equation object in the form | Excel Programming | |||
VBA won't recognize form object | Excel Programming | |||
Difficulties in VBA ( User form and option button) | Excel Programming |