Populate forms from hidden XLA worksheet objects
Advanced Concept. Sometimes you have macros which need user parameters or your form objects such as listboxes need a list to load. Instead of storing the info in .txt or .ini text files store the info directly in your xla. The trick is reading the info from the xla. worksheets. As most know, as soon as you save your xls workbook as an xla your worksheets magically disappear. However, in reality they still exist as objects. Here are the steps
1) Build your code/form or whatever in your XLS workbook. This will be called your "Master Workbook".
2) Create a worksheet that has your list of parameters or whatever your macros need.
3) Save the XLS workbook as XLA. Voila your done.
In the sample you have the Master XLS as well as the XLA. Open the XLS and see the simple parameter list and see how it is stored on a worksheet. Next, close the XLS, and open the XLA. Do a ALT+F11 to open the visual basic editor then go to the mMain module. From the mMain module run the "ShowForm" macro. Your form will load and feed from hidden xla worksheet object.
'Method 1 - Read XLA worksheet values strTest = ThisWorkbook.Sheets("shtFormFeed").Cells(2, 1).Value 'Method 2 - Read XLA worksheet values With ThisWorkbook.Sheets("shtFormFeed") strTest = .Cells(2, 1).Value strTest2 = .Cells(2, 2).Value End With