Installing a Custom Add-In

[scott]

I've created an add-in with functions, subs and an "auto_open" function that
loads a custom menu. I'm looking for a little guidance on what path to take
to accomplish the following:

My user opens a monthly spreadsheet with data to be transferred to access. I
only want my add-in to load and subs, menus, etc. to work in excel if this
workbook is opened. This spreadsheet is machine outputed so I will never be
able to have vba code inside it. I also don't have a lot of control over
it's file name. I could detect if it's the right workbook based on certain
cell criteria though.

Should I add my add-in permently to the personal.xls file so it's always
loaded and have "auto_open" check for cell's meeting my criteria and then
let my custom menus appear?

 

[Bob Phillips]

If you add it Personal.xls, it isn't an add-in. You could load the add-in as
normal (Tools>Add-Ins...), but don't load the menu on auto-open. Instead,
add some application event code that checks every workbook being opened for
your cell criteria, if so, fire the commandbar load.

Here's some application event code to get you started

Private WithEvents App As Application


Private Sub Workbook_Open()
Set App = Application
End Sub

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

This goes in the ThisWorkbook class module of the add-in. Clearly, you would
change my simple msgbox for your check code, and then commandbar launch.
Similarly, you could delete the commandbar when your workbook is closed.

--

HTH

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

 

[scott]

What file should I put the Workbook_Open() sub? The personal.xls or the
custom add-in? What are you doing below with the "WithEvents App As
Application" part in code example?

 

[Bob Phillips]

What file should I put the Workbook_Open() sub? The personal.xls or the
custom add-in?

In the add-in.

What are you doing below with the "WithEvents App As
Application" part in code example?

The WithEvents allows the object variable to respond to events, in this
instance application events..

 

[Tom Ogilvy]

See Chip Pearson's site for an explanation of how to set up Application
Level events:

http://www.cpearson.com/excel/appevent.htm

 

[Jim Cone]

Scott,

Here is another way that doesn't involve application events.
Load all your menus when the add-in opens.
Add an OnAction property to the control that contains your
sub menu items. The OnAction procedure determines
whether the menu items should be enabled. Something like...

'-----------------------------------------------
Sub CheckMenuStatus()
Dim CtrlButton As CommandBarButton

'DISABLE MENU ITEMS IF
With Application.CommandBars.ActionControl
If ActiveSheet Range("B5").Value <> "OK" Then
For Each CtrlButton In .Controls
CtrlButton.Enabled = False
Next
Else
For Each CtrlButton In .Controls
CtrlButton.Enabled = True
Next
End if
Set CtrlButton = Nothing
End With
End Sub
'-----------------------------------------------
This works very fast and the user will not see any delay.

Regards,
Jim Cone
San Francisco, USA

 

[Bob Phillips]

Hi Jim,

The App events were there to help determine when to enable. As it is upon
open of a specific workbook whose name might not be known, I think app
events will still be needed.

Regards

Bob