Code to save XLA file

**ExcelCowboy** · 07-29-2011, 04:07 AM

Hi,

does anyone know the CODE to "automatically" save the XLA file that holds the code of a userform which the user is just busy with? I'd like to have the settings saved that the user has made in e.g. comboboxes? In other words, I am trying to avoid that the userform falls back to the original default settings, when called newly. In this example, the user can change the date format of a calendar (and other things) and it would be great if those settings are 'memorized', even after Excel has been closed and restarted.
A solution would be a "Save Settings" button, provided I knew what code to put behind it

The code would 'just' need to save the Add In, in other words, the XLA file.

THANK YOU!!!

Ben

**royUK** · 07-29-2011, 04:47 AM

You need to save the settings to a sheet within the addin. For example

With Thisworkbook
.Sheet1.Cells(1,1).Value = Me.TextBox1.value
'etc
End With
.Save

Load the setting back with the Initialize event picking up from the relevant cells

**romperstomper** · 07-29-2011, 04:47 AM

Saving the add-in will not work unless your form reads and writes settings to a sheet in the add-in, since run time changes to userforms are not saved. A better bet in my opinion would be to save the settings in the registry using SaveSetting and GetSetting to read them back again when loading the form.

**ExcelCowboy** · 07-29-2011, 07:37 AM

@ roy, THANK YOU, this worked nicely! The ListIndex of a combobox is now stored in a cell of the Add In Sheet (and is read on initialize obviously).
I have added this, not sure it's necessary:
ThisWorkbook.IsAddin = True
It takes 2-3 seconds to save the AddIn file (upon closing the userform; that's not exactly a long time but seems wrong when closing a form) so I wonder if romperstomper's idea about using the registry may be quicker.
I probably just leave out the saving part and am happy that the settings are stored during the Excel session (haven't 'messed' with the registry ever before).
Thanks once more to both of you!
Ben

**royUK** · 07-29-2011, 08:18 AM

You don't need IsAddin if it's already saved as an addin.

Registry is probably faster, I use it quite a bit. I just thought the save in worksheet would be easier and because an addin is basically an invisible workbook it would be secure