add-ins & events

I made an add-in with code to fir the ThisWorkbook of the add-in, but it
doesn't seem to be working now that I've added the add-in into another
workbook.

It seems silly to make an add-in and then have to put vba code in the host
workbook, so what's the right way to do this?

Thanks, Eric

How about some specifics on the code and events that are not working? :-)

--

Vasant


"Eric" <[email protected]> wrote in message
news:[email protected]...
> I made an add-in with code to fir the ThisWorkbook of the add-in, but it
> doesn't seem to be working now that I've added the add-in into another
> workbook.
>
> It seems silly to make an add-in and then have to put vba code in the host
> workbook, so what's the right way to do this?
>
> Thanks, Eric
>
>

Fair enough :-)

It's located in the add-in's ThisWorkbook module as below - pretty vanilla
stuff, and it WILL work if I manaully make it work, but I want the event to
trigger it.

Thanks, Eric

Private Sub Workbook_Activate()
Call SetToolbarStateToCustom(TOOLBAR_NAME)
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call SetToolbarStateToNormal(TOOLBAR_NAME, doPermanently:=True)
End Sub

Private Sub Workbook_Deactivate()
Call SetToolbarStateToNormal(TOOLBAR_NAME, doPermanently:=False)
End Sub

Private Sub Workbook_NewSheet(ByVal Sh As Object)
Call SetToolbarStateToNormal(TOOLBAR_NAME, doPermanently:=False)
End Sub

Private Sub Workbook_Open()
modMain.Initialize
End Sub

"Vasant Nanavati" <vasantn *AT* aol *DOT* com> wrote in message
news:[email protected]...
> How about some specifics on the code and events that are not working? :-)
>
> --
>
> Vasant
>
>
> "Eric" <[email protected]> wrote in message
> news:[email protected]...
>> I made an add-in with code to fir the ThisWorkbook of the add-in, but it
>> doesn't seem to be working now that I've added the add-in into another
>> workbook.
>>
>> It seems silly to make an add-in and then have to put vba code in the
>> host
>> workbook, so what's the right way to do this?
>>
>> Thanks, Eric
>>
>>
>
>

Just to add to Vasant's request, it may be just terminology, but an add-in
is added to Excel, not to a workbook. How did you install it?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Vasant Nanavati" <vasantn *AT* aol *DOT* com> wrote in message
news:[email protected]...
> How about some specifics on the code and events that are not working? :-)
>
> --
>
> Vasant
>
>
> "Eric" <[email protected]> wrote in message
> news:[email protected]...
> > I made an add-in with code to fir the ThisWorkbook of the add-in, but it
> > doesn't seem to be working now that I've added the add-in into another
> > workbook.
> >
> > It seems silly to make an add-in and then have to put vba code in the
host
> > workbook, so what's the right way to do this?
> >
> > Thanks, Eric
> >
> >
>
>

1) I saved a workbook as "PunchMgr.xla"
2) I open a workbook called "Construction Issues.xls"
3) Under Tools for "Construction Issues.xls", I opened the add-in mnager. I
had to browse for my xla file since I didn't keep it in the standard place
excel like to put them in
4 After finding "PunchMgr.xla", I selected it, which checked it off and made
it an add-in to "Construction Issues.xls". Its code is available in the VBE

Hope that makes it clearer. The xls file has no code of its own, and one of
my goals is to not make the end user deal with enabling macros once they
have elected to select the add-in.

Thanks, Eric

"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> Just to add to Vasant's request, it may be just terminology, but an add-in
> is added to Excel, not to a workbook. How did you install it?
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Vasant Nanavati" <vasantn *AT* aol *DOT* com> wrote in message
> news:[email protected]...
>> How about some specifics on the code and events that are not working? :-)
>>
>> --
>>
>> Vasant
>>
>>
>> "Eric" <[email protected]> wrote in message
>> news:[email protected]...
>> > I made an add-in with code to fir the ThisWorkbook of the add-in, but
>> > it
>> > doesn't seem to be working now that I've added the add-in into another
>> > workbook.
>> >
>> > It seems silly to make an add-in and then have to put vba code in the
> host
>> > workbook, so what's the right way to do this?
>> >
>> > Thanks, Eric
>> >
>> >
>>
>>
>
>

I'm a bit confused. Add-ins are hidden workbooks. How does an add-in get
activated and deactivated? Or get sheets added to it? ThisWorkbook is the
workbook containing the code; i.e., the add-in. Perhaps you are confusing
the ActiveWorkbook and ThisWorkbook objects.

--

Vasant



"Eric" <[email protected]> wrote in message
news:[email protected]...
> Fair enough :-)
>
> It's located in the add-in's ThisWorkbook module as below - pretty vanilla
> stuff, and it WILL work if I manaully make it work, but I want the event
to
> trigger it.
>
> Thanks, Eric
>
> Private Sub Workbook_Activate()
> Call SetToolbarStateToCustom(TOOLBAR_NAME)
> End Sub
>
> Private Sub Workbook_BeforeClose(Cancel As Boolean)
> Call SetToolbarStateToNormal(TOOLBAR_NAME, doPermanently:=True)
> End Sub
>
> Private Sub Workbook_Deactivate()
> Call SetToolbarStateToNormal(TOOLBAR_NAME, doPermanently:=False)
> End Sub
>
> Private Sub Workbook_NewSheet(ByVal Sh As Object)
> Call SetToolbarStateToNormal(TOOLBAR_NAME, doPermanently:=False)
> End Sub
>
> Private Sub Workbook_Open()
> modMain.Initialize
> End Sub
>
> "Vasant Nanavati" <vasantn *AT* aol *DOT* com> wrote in message
> news:[email protected]...
> > How about some specifics on the code and events that are not working?
:-)
> >
> > --
> >
> > Vasant
> >
> >
> > "Eric" <[email protected]> wrote in message
> > news:[email protected]...
> >> I made an add-in with code to fir the ThisWorkbook of the add-in, but
it
> >> doesn't seem to be working now that I've added the add-in into another
> >> workbook.
> >>
> >> It seems silly to make an add-in and then have to put vba code in the
> >> host
> >> workbook, so what's the right way to do this?
> >>
> >> Thanks, Eric
> >>
> >>
> >
> >
>
>

I am sure Vasant will tell you all about application level events, but just
out of curiosity, how did you:

" I've added the add-in into another workbook."

--
Regards,
Tom Ogilvy


"Eric" <[email protected]> wrote in message
news:[email protected]...
> Fair enough :-)
>
> It's located in the add-in's ThisWorkbook module as below - pretty vanilla
> stuff, and it WILL work if I manaully make it work, but I want the event
to
> trigger it.
>
> Thanks, Eric
>
> Private Sub Workbook_Activate()
> Call SetToolbarStateToCustom(TOOLBAR_NAME)
> End Sub
>
> Private Sub Workbook_BeforeClose(Cancel As Boolean)
> Call SetToolbarStateToNormal(TOOLBAR_NAME, doPermanently:=True)
> End Sub
>
> Private Sub Workbook_Deactivate()
> Call SetToolbarStateToNormal(TOOLBAR_NAME, doPermanently:=False)
> End Sub
>
> Private Sub Workbook_NewSheet(ByVal Sh As Object)
> Call SetToolbarStateToNormal(TOOLBAR_NAME, doPermanently:=False)
> End Sub
>
> Private Sub Workbook_Open()
> modMain.Initialize
> End Sub
>
> "Vasant Nanavati" <vasantn *AT* aol *DOT* com> wrote in message
> news:[email protected]...
> > How about some specifics on the code and events that are not working?
:-)
> >
> > --
> >
> > Vasant
> >
> >
> > "Eric" <[email protected]> wrote in message
> > news:[email protected]...
> >> I made an add-in with code to fir the ThisWorkbook of the add-in, but
it
> >> doesn't seem to be working now that I've added the add-in into another
> >> workbook.
> >>
> >> It seems silly to make an add-in and then have to put vba code in the
> >> host
> >> workbook, so what's the right way to do this?
> >>
> >> Thanks, Eric
> >>
> >>
> >
> >
>
>

4 After finding "PunchMgr.xla", I selected it, which checked it off and made
it an add-in to "Construction Issues.xls".

That just makes it a loaded addin like theAnalysis toolpak or Solver. It
has no relation to Construction Issues.xls. The code is available in the
VBE, but it is no more available to Contruction Issues.xls than to any other
open workbook. Nor do the events in PunchMgr.xla have any cognizance of
what is happening in Construciton Issues.xls.

You need to instantiate application level events.

for documentation and some explanation on Application Level Events, you
might want to read Chip Pearson's site
http://www.cpearson.com/excel/appevent.htm

this reflects the method presented in VBA help, but see this simpler, more
logical method suggested by KeepitCool and Further endorsed by Jamie Collins
and which uses only the Thisworkbook class module (eliminating the need to
instantiate the class and which keeps everything in one place)

[KeepitCool, otherwise known as Jurgen Volkerink @ www.XLsupport.com ]

Some discussion:
http://groups.google.co.uk/groups?se....microsoft.com
the thread:
http://groups.google.co.uk/groups?th....microsoft.com

Source of article below:
http://groups.google.co.uk/groups?se....microsoft.com

From: keepITcool ([email protected])
Subject: Re: Using Events with the Application Object & XL 2003


View this article only
Newsgroups: microsoft.public.excel.programming
Date: 2005-01-23 18:47:05 PST



What Chip doesn't mention and what I find a lot easier...

you dont need a separate class module..
Thisworkbook IS a class module so you can keep all your code
(and the withevents application variable) there.

'thisworkbook code module...
Option Explicit

Private WithEvents XlsEvents As Application

Private Sub Workbook_Open()
Set XlsEvents = Application
End Sub

Private Sub XlsEvents_WorkbookOpen(ByVal Wb As Workbook)
MsgBox "You just opened " & Wb.Name
End Sub




--
Regards,
Tom Ogilvy


"Eric" <[email protected]> wrote in message
news:[email protected]...
> 1) I saved a workbook as "PunchMgr.xla"
> 2) I open a workbook called "Construction Issues.xls"
> 3) Under Tools for "Construction Issues.xls", I opened the add-in mnager.
I
> had to browse for my xla file since I didn't keep it in the standard place
> excel like to put them in
> 4 After finding "PunchMgr.xla", I selected it, which checked it off and
made
> it an add-in to "Construction Issues.xls". Its code is available in the
VBE
>
> Hope that makes it clearer. The xls file has no code of its own, and one
of
> my goals is to not make the end user deal with enabling macros once they
> have elected to select the add-in.
>
> Thanks, Eric
>
> "Bob Phillips" <[email protected]> wrote in message
> news:[email protected]...
> > Just to add to Vasant's request, it may be just terminology, but an
add-in
> > is added to Excel, not to a workbook. How did you install it?
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Vasant Nanavati" <vasantn *AT* aol *DOT* com> wrote in message
> > news:[email protected]...
> >> How about some specifics on the code and events that are not working?
:-)
> >>
> >> --
> >>
> >> Vasant
> >>
> >>
> >> "Eric" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > I made an add-in with code to fir the ThisWorkbook of the add-in, but
> >> > it
> >> > doesn't seem to be working now that I've added the add-in into
another
> >> > workbook.
> >> >
> >> > It seems silly to make an add-in and then have to put vba code in the
> > host
> >> > workbook, so what's the right way to do this?
> >> >
> >> > Thanks, Eric
> >> >
> >> >
> >>
> >>
> >
> >
>
>

Cool :-)

Going to read it now.

Thanks, Eric

"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
>4 After finding "PunchMgr.xla", I selected it, which checked it off and
>made
> it an add-in to "Construction Issues.xls".
>
> That just makes it a loaded addin like theAnalysis toolpak or Solver. It
> has no relation to Construction Issues.xls. The code is available in the
> VBE, but it is no more available to Contruction Issues.xls than to any
> other
> open workbook. Nor do the events in PunchMgr.xla have any cognizance of
> what is happening in Construciton Issues.xls.
>
> You need to instantiate application level events.
>
> for documentation and some explanation on Application Level Events, you
> might want to read Chip Pearson's site
> http://www.cpearson.com/excel/appevent.htm
>
> this reflects the method presented in VBA help, but see this simpler, more
> logical method suggested by KeepitCool and Further endorsed by Jamie
> Collins
> and which uses only the Thisworkbook class module (eliminating the need to
> instantiate the class and which keeps everything in one place)
>
> [KeepitCool, otherwise known as Jurgen Volkerink @ www.XLsupport.com ]
>
> Some discussion:
> http://groups.google.co.uk/groups?se....microsoft.com
> the thread:
> http://groups.google.co.uk/groups?th....microsoft.com
>
> Source of article below:
> http://groups.google.co.uk/groups?se....microsoft.com
>
> From: keepITcool ([email protected])
> Subject: Re: Using Events with the Application Object & XL 2003
>
>
> View this article only
> Newsgroups: microsoft.public.excel.programming
> Date: 2005-01-23 18:47:05 PST
>
>
>
> What Chip doesn't mention and what I find a lot easier...
>
> you dont need a separate class module..
> Thisworkbook IS a class module so you can keep all your code
> (and the withevents application variable) there.
>
> 'thisworkbook code module...
> Option Explicit
>
> Private WithEvents XlsEvents As Application
>
> Private Sub Workbook_Open()
> Set XlsEvents = Application
> End Sub
>
> Private Sub XlsEvents_WorkbookOpen(ByVal Wb As Workbook)
> MsgBox "You just opened " & Wb.Name
> End Sub
>
>
>
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Eric" <[email protected]> wrote in message
> news:[email protected]...
>> 1) I saved a workbook as "PunchMgr.xla"
>> 2) I open a workbook called "Construction Issues.xls"
>> 3) Under Tools for "Construction Issues.xls", I opened the add-in mnager.
> I
>> had to browse for my xla file since I didn't keep it in the standard
>> place
>> excel like to put them in
>> 4 After finding "PunchMgr.xla", I selected it, which checked it off and
> made
>> it an add-in to "Construction Issues.xls". Its code is available in the
> VBE
>>
>> Hope that makes it clearer. The xls file has no code of its own, and one
> of
>> my goals is to not make the end user deal with enabling macros once they
>> have elected to select the add-in.
>>
>> Thanks, Eric
>>
>> "Bob Phillips" <[email protected]> wrote in message
>> news:[email protected]...
>> > Just to add to Vasant's request, it may be just terminology, but an
> add-in
>> > is added to Excel, not to a workbook. How did you install it?
>> >
>> > --
>> >
>> > HTH
>> >
>> > RP
>> > (remove nothere from the email address if mailing direct)
>> >
>> >
>> > "Vasant Nanavati" <vasantn *AT* aol *DOT* com> wrote in message
>> > news:[email protected]...
>> >> How about some specifics on the code and events that are not working?
> :-)
>> >>
>> >> --
>> >>
>> >> Vasant
>> >>
>> >>
>> >> "Eric" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > I made an add-in with code to fir the ThisWorkbook of the add-in,
>> >> > but
>> >> > it
>> >> > doesn't seem to be working now that I've added the add-in into
> another
>> >> > workbook.
>> >> >
>> >> > It seems silly to make an add-in and then have to put vba code in
>> >> > the
>> > host
>> >> > workbook, so what's the right way to do this?
>> >> >
>> >> > Thanks, Eric
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>>
>
>