Accessing Addin from Sheet_Event Code
 

To conserve size, I deleted all the code but the sheet(s)_event code from my
spreadsheet, to create MyAddin. Now, from within a sheet_event, I need to
access a sub in MyAddin. Searching the help file, I found what I thought
was an explanation in the AddIn Object:

I have unsuccessfully tried setting my addin as:
1. Set wb = Workbooks("MyAddin.xla")
2. Set wb = Workbooks(AddIns("MyAddin").Name)
and then calling the sub that is in MyAddin by:
1. wb.Sub99
2. wb.Module.Sub99

How can I successfully call "Sub99", located in MyAddin, from a sheet_event?
A second question: Is it bad practice to delete most of the code from the
spreadsheet, as I have done? Would it have been better to leave it all
within the spreadsheet (and leave the spreadsheet itself to be huge)?

TIA

Accessing Addin from Sheet_Event Code
 

Application.Run "MyAddin.xla!Module1.Sub99"

If you create a reference from the workbook to myaddin, then you just call
it with

Sub99

--
Regards,
Tom Ogilvy


zSplash wrote in message
...
To conserve size, I deleted all the code but the sheet(s)_event code from
my
spreadsheet, to create MyAddin. Now, from within a sheet_event, I need to
access a sub in MyAddin. Searching the help file, I found what I thought
was an explanation in the AddIn Object:

I have unsuccessfully tried setting my addin as:
1. Set wb = Workbooks("MyAddin.xla")
2. Set wb = Workbooks(AddIns("MyAddin").Name)
and then calling the sub that is in MyAddin by:
1. wb.Sub99
2. wb.Module.Sub99

How can I successfully call "Sub99", located in MyAddin, from a
sheet_event?
A second question: Is it bad practice to delete most of the code from the
spreadsheet, as I have done? Would it have been better to leave it all
within the spreadsheet (and leave the spreadsheet itself to be huge)?

TIA

Accessing Addin from Sheet_Event Code
 

Dim wb As Workbook
Set wb = Workbooks("MyAddin.xla")
Application.Run wb.Name & "!Sub99"

or a modification for workbook names with apostrophes and/or spaces, this
will work for everything:
Application.Run _
"'" & Replace(wb.Name, "'", "''") & "'!Sub99"


"zSplash" wrote in message
...
To conserve size, I deleted all the code but the sheet(s)_event code from
my
spreadsheet, to create MyAddin. Now, from within a sheet_event, I need to
access a sub in MyAddin. Searching the help file, I found what I thought
was an explanation in the AddIn Object:

I have unsuccessfully tried setting my addin as:
1. Set wb = Workbooks("MyAddin.xla")
2. Set wb = Workbooks(AddIns("MyAddin").Name)
and then calling the sub that is in MyAddin by:
1. wb.Sub99
2. wb.Module.Sub99

How can I successfully call "Sub99", located in MyAddin, from a
sheet_event?
A second question: Is it bad practice to delete most of the code from the
spreadsheet, as I have done? Would it have been better to leave it all
within the spreadsheet (and leave the spreadsheet itself to be huge)?

TIA

Accessing Addin from Sheet_Event Code
 

Thanks, Tom and Tim. It works great.

st.

"Tim Zych" wrote in message
...
Dim wb As Workbook
Set wb = Workbooks("MyAddin.xla")
Application.Run wb.Name & "!Sub99"

or a modification for workbook names with apostrophes and/or spaces, this
will work for everything:
Application.Run _
"'" & Replace(wb.Name, "'", "''") & "'!Sub99"


"zSplash" wrote in message
...
To conserve size, I deleted all the code but the sheet(s)_event code
from
my
spreadsheet, to create MyAddin. Now, from within a sheet_event, I need
to
access a sub in MyAddin. Searching the help file, I found what I
thought
was an explanation in the AddIn Object:

I have unsuccessfully tried setting my addin as:
1. Set wb = Workbooks("MyAddin.xla")
2. Set wb = Workbooks(AddIns("MyAddin").Name)
and then calling the sub that is in MyAddin by:
1. wb.Sub99
2. wb.Module.Sub99

How can I successfully call "Sub99", located in MyAddin, from a
sheet_event?
A second question: Is it bad practice to delete most of the code from
the
spreadsheet, as I have done? Would it have been better to leave it all
within the spreadsheet (and leave the spreadsheet itself to be huge)?

TIA