Hi,

I need to add a form to multiple workbooks.

I have the form saved on a network drive and I need to build a workbook to
email out users so they can click a button and it will run code to add a form
to their open workbooks. Is this possible?

Thanks

Simon Shaw
www.kode101.com

You want to add a form to every open workbook on your users' computers? What
if the workbook they are currently using has nothing to do with your form?
If you wrote code that tried to add VB components to workbooks on my
computer, it would be the last code of yours I'd ever run.

Write an add-in that contains and controls the form, make it accessible from
every relevant workbook, then distribute it so the users install it as an
add-in.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Simon Shaw" <simonATsimonstoolsDOTcom> wrote in message
news:C3BF78F2-23D7-47B0-A7D9-(E-Mail Removed)...
> Hi,
>
> I need to add a form to multiple workbooks.
>
> I have the form saved on a network drive and I need to build a workbook to
> email out users so they can click a button and it will run code to add a
> form
> to their open workbooks. Is this possible?
>
> Thanks
>
> Simon Shaw
> www.kode101.com
>
>

You can incorporate the following into your code. Its possible to run this
on workbook open, if you prefer.

ActiveWorkbook.VBProject.VBComponents.Import "C:\Documents and Settings\..."
' whatever your path directory is on your network drive.

hope this helps!
-chad

"Jon Peltier" wrote:

> You want to add a form to every open workbook on your users' computers? What
> if the workbook they are currently using has nothing to do with your form?
> If you wrote code that tried to add VB components to workbooks on my
> computer, it would be the last code of yours I'd ever run.
>
> Write an add-in that contains and controls the form, make it accessible from
> every relevant workbook, then distribute it so the users install it as an
> add-in.
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Tutorials and Custom Solutions
> http://PeltierTech.com
> _______
>
>
> "Simon Shaw" <simonATsimonstoolsDOTcom> wrote in message
> news:C3BF78F2-23D7-47B0-A7D9-(E-Mail Removed)...
> > Hi,
> >
> > I need to add a form to multiple workbooks.
> >
> > I have the form saved on a network drive and I need to build a workbook to
> > email out users so they can click a button and it will run code to add a
> > form
> > to their open workbooks. Is this possible?
> >
> > Thanks
> >
> > Simon Shaw
> > www.kode101.com
> >
> >
>
>
>

obviously I would validate that the open workbook is one that I want to add a
form to... I am by no means a reckless programer!

--
Simon Shaw, CA
President
Kode101 Inc.
www.kode101.com


"Jon Peltier" wrote:

> You want to add a form to every open workbook on your users' computers? What
> if the workbook they are currently using has nothing to do with your form?
> If you wrote code that tried to add VB components to workbooks on my
> computer, it would be the last code of yours I'd ever run.
>
> Write an add-in that contains and controls the form, make it accessible from
> every relevant workbook, then distribute it so the users install it as an
> add-in.
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Tutorials and Custom Solutions
> http://PeltierTech.com
> _______
>
>
> "Simon Shaw" <simonATsimonstoolsDOTcom> wrote in message
> news:C3BF78F2-23D7-47B0-A7D9-(E-Mail Removed)...
> > Hi,
> >
> > I need to add a form to multiple workbooks.
> >
> > I have the form saved on a network drive and I need to build a workbook to
> > email out users so they can click a button and it will run code to add a
> > form
> > to their open workbooks. Is this possible?
> >
> > Thanks
> >
> > Simon Shaw
> > www.kode101.com
> >
> >
>
>
>

Yes, but the approach is still cumbersome. What if the form changes? You
would have to replace it in every workbook. If it's in an add-in, you can
replace the entire add-in and leave the data files alone, which is easier
than tracking down all the files that need to be changed.

One way to program more effectively is to separate parts of the system. Keep
the data (workbooks) separate from the logic (the add-in). This way, you
don't risk losing tons of data to adjust the code.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Simon Shaw" <simon AT kode101 DOT com> wrote in message
news:02159FF6-B85D-43FC-9F33-(E-Mail Removed)...
> obviously I would validate that the open workbook is one that I want to
> add a
> form to... I am by no means a reckless programer!
>
> --
> Simon Shaw, CA
> President
> Kode101 Inc.
> www.kode101.com
>
>
> "Jon Peltier" wrote:
>
>> You want to add a form to every open workbook on your users' computers?
>> What
>> if the workbook they are currently using has nothing to do with your
>> form?
>> If you wrote code that tried to add VB components to workbooks on my
>> computer, it would be the last code of yours I'd ever run.
>>
>> Write an add-in that contains and controls the form, make it accessible
>> from
>> every relevant workbook, then distribute it so the users install it as an
>> add-in.
>>
>> - Jon
>> -------
>> Jon Peltier, Microsoft Excel MVP
>> Tutorials and Custom Solutions
>> http://PeltierTech.com
>> _______
>>
>>
>> "Simon Shaw" <simonATsimonstoolsDOTcom> wrote in message
>> news:C3BF78F2-23D7-47B0-A7D9-(E-Mail Removed)...
>> > Hi,
>> >
>> > I need to add a form to multiple workbooks.
>> >
>> > I have the form saved on a network drive and I need to build a workbook
>> > to
>> > email out users so they can click a button and it will run code to add
>> > a
>> > form
>> > to their open workbooks. Is this possible?
>> >
>> > Thanks
>> >
>> > Simon Shaw
>> > www.kode101.com
>> >
>> >
>>
>>
>>

good point. I do normally setup my projects as such. however, this one was a
patch for something very old.

--
Simon Shaw, CA
President
Kode101 Inc.
www.kode101.com


"Jon Peltier" wrote:

> Yes, but the approach is still cumbersome. What if the form changes? You
> would have to replace it in every workbook. If it's in an add-in, you can
> replace the entire add-in and leave the data files alone, which is easier
> than tracking down all the files that need to be changed.
>
> One way to program more effectively is to separate parts of the system. Keep
> the data (workbooks) separate from the logic (the add-in). This way, you
> don't risk losing tons of data to adjust the code.
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Tutorials and Custom Solutions
> http://PeltierTech.com
> _______
>
>
> "Simon Shaw" <simon AT kode101 DOT com> wrote in message
> news:02159FF6-B85D-43FC-9F33-(E-Mail Removed)...
> > obviously I would validate that the open workbook is one that I want to
> > add a
> > form to... I am by no means a reckless programer!
> >
> > --
> > Simon Shaw, CA
> > President
> > Kode101 Inc.
> > www.kode101.com
> >
> >
> > "Jon Peltier" wrote:
> >
> >> You want to add a form to every open workbook on your users' computers?
> >> What
> >> if the workbook they are currently using has nothing to do with your
> >> form?
> >> If you wrote code that tried to add VB components to workbooks on my
> >> computer, it would be the last code of yours I'd ever run.
> >>
> >> Write an add-in that contains and controls the form, make it accessible
> >> from
> >> every relevant workbook, then distribute it so the users install it as an
> >> add-in.
> >>
> >> - Jon
> >> -------
> >> Jon Peltier, Microsoft Excel MVP
> >> Tutorials and Custom Solutions
> >> http://PeltierTech.com
> >> _______
> >>
> >>
> >> "Simon Shaw" <simonATsimonstoolsDOTcom> wrote in message
> >> news:C3BF78F2-23D7-47B0-A7D9-(E-Mail Removed)...
> >> > Hi,
> >> >
> >> > I need to add a form to multiple workbooks.
> >> >
> >> > I have the form saved on a network drive and I need to build a workbook
> >> > to
> >> > email out users so they can click a button and it will run code to add
> >> > a
> >> > form
> >> > to their open workbooks. Is this possible?
> >> >
> >> > Thanks
> >> >
> >> > Simon Shaw
> >> > www.kode101.com
> >> >
> >> >
> >>
> >>
> >>
>
>
>