Populate Forms From Hidden Xla Worksheet Values

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.

Enjoy!:wowee:

'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

Archived Projects
http://programminglibrary.com/…y/DOWNLOAD/downloads.aspx