MrExcel Publishing
Your One Stop for Excel Tips & Solutions

values from a different book into the current book


Posted by steven scaife on October 24, 2001 7:04 AM

If I have a list box that is sitting in a different workbook how do I get information into the list box in the current workbook.

Or would it be easier and just as secure to hide the sheet in the current workbook. Would this stop people seeing the hidden sheet if it got mailed to someone.

thanks in advance for any help you may offer me


Posted by Damon Ostrander on October 24, 2001 12:05 PM

Hi Steven,

You can securely hide the sheet within the current workbook. However, just hiding a worksheet from the Format menu in Excel is not very secure. If you really don't want people to be able to access the information, then you must make it "Very Hidden," and this can only be done from within VBA. This is done by setting the worksheet's Visible property to xlVeryHidden. Such hiding prevents it from even showing up in the Unhide menu, so the user does know it is even there. In addition, you should password protect both the worksheet AND the code (the VBProject). This means that your listbox or userform initialization code will have to call code that unhides the hidden sheet so that you can load its data into the listbox, then rehides it (it can be unhidden without becoming visible by making sure it does not become the active sheet). You will also want to temporarily deactivate the ESC key so the user cannot abort your code while the sheet is unhidden and thereby access it via this "trap door."

Regarding loading the data into the listbox, there are two ways: assigining the range to the listbox's RowSource property (done at design time), and loading the list using the AddItem method or assiging the array of items directly to the List() property at run time. The former is easier if the list will never change. The latter is required if the list can change during run time.

I'm sorry this is such a rough outline, and that I don't have time to give you the code, but hopefully this will get you going in the right direction.

Happy computing.

Damon