Excel Add-ins where to put a button to open form

Topic

New Reply

Ok, I’m not very familar with Excel and am a bit confused with where things needs to go.

In my test spreadsheet I have a simple sub in the ‘This Workbook’ object.

Sub showPlantForm()

frmGetPlantInfo_v3.Show

End Sub

I have added a button to a toolbar and assign it to this ‘macro’ The ‘assign macro’ thing keeps trying assign itself to the a fully qualify path rather than just ThisWorkbook.macroname.

Now, I want to save the development spreadsheet as an add-in but I’m not sure where either the open form sub or the button need to live.

Sorry for the probably rather basic question, I’m out of my normal happy environment (Access) and finding Excel a bit quirky.

Reply | Quote

Replies

You need to move your code to a normal module, the Thisworkbook module is meant for workbok event code, not for macros called by buttons.
Check out this article on building addins

Reply | Quote

Thank you, still a bit stuck though.

Right, the original xls workbook has an extra code module containing a single sub (to show my form).

I’ve saved the workbook as an add-in, opened a blank spreadsheet and referenced the add-in.

I can’t see the show form ‘macro’. There are no macros visible to the assign macro dialog.

I do remember reading something the other data about custom functions in an add-in not being visible to the workbook and there was as simple fix to sort it out. I can’t for the life of me remember where I read this.

Edit: I just typed the sub name ( I hate using the word macro) into the macro name box in the macro dialog and clicking the button opens my form fine. I am baffled why I can’t pick it from a list though.

Reply | Quote

I would put a button on a custom toolbar to load your form from the addin instead of a button on another workbook. The article I referred to tells you exactly how to do that.

Reply | Quote

I’ve read some of the article. The bit on adding an item to a menu seems to imply I need to write code (copying the example) to both add and remove a menu item. It doesn’t explain from where (or when) these routines need calling.

As per usual there is no time to do these things, so I’m finding what’s required to achieve the simple task just showing a form, more than a little long winded.

To quote the article ‘The sub RemoveMenu should be called by the utilities’ closing code so the entry is removed when the utility is closed.’

I don’t understand what the ‘utility’ is referring to. Does is mean when the add-in is loaded or unloaded?

I appreciate your patience on this.

Reply | Quote

It means you put a bit of code in the Thisworkbook module of the addin, for example:

Code:

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    RemoveMenu
End Sub

Private Sub Workbook_Open()
    CreateMenu
End Sub

Then in a normal module you have the two subs called RemoveMenu and CreateMenu respectively:

Code:

Sub RemoveMenu()
'Code to remove menu item goes here
End Sub

Sub CreateMenu()
'Code to create menu item goes here
End Sub

Reply | Quote

Sorted, thank you.

Now I just need to re-write some code. I was writing a string (an access database path) to a custom document property. This doesn’t seem to work with the add-in.

I guess I need to look at ini files right?

Reply | Quote

You can write a string to a custom document property in an addin too, but then you have to save the addin every time. Registry is better.

Reply | Quote