Excel Addin

[Sri]

Hi,

I have written some macro in a excel workbook and saved it
as "Add in" so that i can include it any of my workbooks..

I have some doubts regarding addin .....

1. When i tried opening the addin file , the excel does
not show any of the worksheets in that addin file...Is
there any reason for it? I wanted to do some more work
with it using the sheets actually. I have tried to make
visible all the sheets using code, but still it didn't
show up.

2. Even i tried renaming the file with .xls as workbook.
The same thing happes even if i open the newly convereted
xls book..This is really surprising. An Excel addin file
renamed or converted as a Workbook ,still Excel didn't
show the sheets when i opened the xls book.
Do any of you know why is this?

3. Adding to that, i have done some changes to the Addin
file in the VBA editor and by mistake i closed the
file..But Excel didn't promt for save..it just closed the
file without saving..This is really annoying me..Can any
of you help me understand this.

4. Lastly, do any of you know how to give a shortcut key
to one of the function in Addin file ( i want to show a
userform once some keys are pressed ! )

Sorry to put all my question in one single place..I
thought all are related..thats why...Any help would be
highly appreciated..

Reagrds

Sri

 

[Bob Phillips]

Sri,

BY default, the worksheets in an add-in are hidden. You can address those
worksheets from within code, but you cannot make them visible in Excel. The
purpose of the add-in is to provide a code application so to speak that you
can use on other workbooks.

Add-ins have an IsAddIn property, which you can change in the VBE editor.
Select the add-in project, then select ThisWorkbook, and you will see the
property in the properties window. For add-ins, that property is set to
true. Change it to false, and it becomes a workbook, with visible sheets.

Because add-ins are code applications, most changes you make (you may store
some data in the worksheets) is in the code, so save the add-in from within
VBE. As you have noticed, Excel will not be aware of changes, so will not
prompt.

To add a shortcut key, use Onkey

Application.OnKey "+^{A}", "myMacro"

sets Ctrl-Shift-A to run your macro.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Guest]

Thanks Bob for the quick response. Bang on as always.

Just to clarify myself a bit more, where do i put the code
for setting the shortcut key.

Does it need to be in activate event so that it is run
whenever excel is opened or some other place.

And also we need to reset the key to default when the
addin is deselected....

Regards
Sri

 

[Bob Phillips]

Sri,

I put them in Workbook_Open events in the ThisWorkbook code module.That way,
they are available once Excel starts up.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

Sri,

missed the deselect bit.
No point in putting it in Workbook_BeforeSave as this will only activate for
an add-in when Excel closes, so use the Addin_Uninstall event to change it
back.

To reset it use

Application.OnKey "+^{A}", ""


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)