Best Way to Create Options Menu for an add-in?

[Sock]

Hi everyone,

When I've developed code within a workbook, I had an options menu userform made, which would have preferences saved into a worksheet and then those options would be pulled from that sheet. This seemed to work fine, as it would enable the user to have different sets of options made for each workbook they used this in. However, when I finally put my code into an add-in, I realized it doesn't work as smoothly as I would have hoped. Ie. I had no idea what I was doing (and still don't!) :(

This add-in is to be used across any workbook.

So here's the question:

If I want to make an options menu that can save option preferences within a workbook for this add-in, what would be the best way to go about doing this?* It seems like worksheets can't be created and used with an add-in like they can be with a regular .xls/.xlsx file. Any suggestions?

*For example, a user may want to use "Algorithm Type A" and they select this from the options menu. Then if in a different workbook they run the add-in and select "Algorithm Type B" instead.

As usual, your help is GREATLY appreciated. Please let me know if any more detail is required.

[lotuxel]

Hi Sock,
To discuss with that problem, I also have some experience same with you!.
1). Why you uses userform in worksheet module, if some thing special need?(Worksheet change(..))
2). I suggest this is the best way to put in standard modules for addin.
3). When you addin the workbook the worksheets all hided and you can open by thisworkbook.isaddin = false ..
4). For me all the procedures and function of codes all put in addin and uses every workbooks that i need.
thanks,

[snb]

In the attachment you'll find an addin, containing a userform.
The attachment also contains a normal worbook with which the options userform in the addin can be started.
After clicking the button in the userform the result will be written into this normal workbook.

[Sock]

Hi Sock,
To discuss with that problem, I also have some experience same with you!.
1). Why you uses userform in worksheet module, if some thing special need?(Worksheet change(..))
2). I suggest this is the best way to put in standard modules for addin.
3). When you addin the workbook the worksheets all hided and you can open by thisworkbook.isaddin = false ..
4). For me all the procedures and function of codes all put in addin and uses every workbooks that i need.
thanks,

Hi lotuxel, thanks for the response. Unfortunately I don't think I understand what you're saying.


In the attachment you'll find an addin, containing a userform.
The attachment also contains a normal worbook with which the options userform in the addin can be started.
After clicking the button in the userform the result will be written into this normal workbook.

Hey snb, thanks! I have noticed that when I click the button it gives me an error ("Subscript out of range"). I looked into the code and saw that the add-in is listed as .xlsm so I changed it to .xlam but that resulted in a different error on the button. While I think I understand what the code is doing, do you know what's causing this error?

[snb]

A new zipfile containing both (adapted) files.

[Sock]

A new zipfile containing both (adapted) files.

Thanks, snb. I think this should work! :)