Excel Add In - Store Value

[DaveTurton]

Hi all


I am creating an add in and have a userform which the user can type in values (trigger points) for certain topics. When the userform is closed I want the values to be stored and used by the UDFs of the add in. I also want these values to be stored after the user exits the workbook.


At the moment I have declared them as variables, but they are not stored for use by the UDFs after the userform is closed, presumably as that sub has now finished and no code is running.

 

[Hui]

Dave


The easiest way is to have a Hidden worksheet where you store variables


Write to them when you close the userform or close excel

Read them when you open the file


Alternatively you could store the values in Named Formula

Doing this makes them available at all times to both VBA and Formulas

 

[DaveTurton]

Hi Hui


As this is to be complied as an excel add in would your suggestions still work?


If so, would you mind telling me how I can store them in named formulas?


Thank you for your help

 

[Hui]

Setup a name in the Name Manager and store a value in it

eg: myRng

Value: 0


in VBA


To read:


myVar = [myRng]


To write


[myRng] = myVar

 

[DaveTurton]

Hi Hui


Getting an error on

[Journal] = CInt(Journal_Trigger.Text)

Object required

 

[DaveTurton]

got it to work using

ActiveWorkbook.Names.Add Name:="Journal", RefersToR1C1:=CInt(Journal_Trigger.Text)

Thanks for your help

 

[DaveTurton]

Hi


I've created a toolbar with a button that opens the form etc.


Compiled and saved as an addin.


I've distributed this to a collegue but when the add in is added, the custom toolbar is not available.


Do you know why this might be?


Thanks

 

[Hui]

I've never done anything with custom toolbars

I do know that you need to build that into the File Open routines

 

[NARAYANK991]

Hi Dave ,


Can this help ?


http://www.jkp-ads.com/articles/distributemacro04.asp


Narayan

 

[DaveTurton]

Yes, that's great thank you