How to import a sheet into XLA code

[Rasm]

I have just written my first XLA application and installed it as an Excel Add-in. It works great (special thank you to AlphaFrog and Richard Schollar for putting me on Toolbars and Add-ins). I have a bunch of settings that I want to keep inbetween loading the XLA - so I have all these settings on a separate hidden sheet - on the data file that I open (XLSX) - in other words - not inside the XLA. I much rather have the settings sheet embedded into the XLA code. An example would be a Listbox.Index - so the same index is set if the add-in is used between loading up Excel - but I cannot figure out :

1) How to import a sheet into my XLA

2) Not sure how I would address the worksheet (after I have it inside my XLA) - do I reference it with a workbook and then worksheet type statement. This I most likely can figure out - If I can get the sheet into the XLA code

3) Is there something in VBA like a propertybag - or similar function that allow me store variables between loadings of Excel.

I am using 2007.


One option would be if I could save my XLA code as i.e. XLSM - but that does not seem to be possible either.

This forum is great - I am new here - but it is such a great source of info and inspiration - My wife done think so - heheheh

 

[Rasm]

OK - I went back to my XLSM code - so now I have a sheet in the code. I load up my XLAM code - Now saved it as XLAM instead of XLA - should make no difference. Next I load up my XLS (data file) - that is now my activesheet - I select a menu on my toolbar in the add-in. Now the add-in (XLAM) code tries to read my default settings.

But the code below generates an error the --- Err.Number=9

Err.Clear
On Error Resume Next
With Workbooks("NewXLAM").Worksheets("Default_Settings")
 
If Err.Number <> 0 Then MsgBox ="Grrrrrr"

 

[Rasm]

I am an idot - forgot the extension of the workbook - This is all working now - OK - I am nuts talking to myself - Just dont want anybody out wasting their time