populate listbox on UserForm with fixed data

[craigexcel]

I'm creating a UserForm that will display as soon as the workbook has been opened. The UserForm has a listbox which needs to be populated with the same set of items (e.g. A, B, C, D) each time the workbook is opened, from which the user will select ONLY a single item from the list. Is it possible for the VB code to populate the listbox with this same set of items each time, as opposed to referencing a worksheet range (so I can avoid having 'distracting' data appear in the worksheet)?

Or is it just simply easier, come to think of it, to have a hidden worksheet on which I can enter the listbox items, and have the listbox refer to this range on the hidden worksheet?

 

[Norie]

craig

Of course you could populate it with fixed data, but I don't see how that can't be done using a range on a worksheet.

How would using that method end up with 'distracting' data?

 

[craigexcel]

Was thinking the 'distracting' data would be visible on the worksheet, and would therefore leave the user wondering 'what's this data?'. However, I could just insert the values off to the right of the existing worksheet data, hide the values in those cells, and set the print range so it excludes these cells. Sounds like that's the easier way to go.
Thanks for getting me to think / consider other possibilities.

 

[Norie]

craig

You ought to watch using that approach.

I don't know exactly what you are doing but hiding data like that on a sheet could cause problems.

For example VBA might include the values in the used range.

 

[craigexcel]

Norie,
The listbox will contain acronyms to specify the division an employee works in. Once selected, the acronym will be entered into the relevant cells in the worksheet. The worksheet won't change form; users will only enter data into specific cells, so hiding these data shouldn't be problematic.

 

[Norie]

Craig

Like I said I don't know what you are doing.

I'm just trying to give you a heads up on the fact that just hiding data in some far flung place on a worksheet could cause problems.

 

[craigexcel]

Agreed. "Shouldn't" was my operative word.
Thanks again.

 

[craigexcel]

How do I reference the worksheet range in the RowSource property of the ListBox? I've tried several combinations using =, ", commas & semicolons (as separators), all without success. Have also tried including <worksheet name>!<cell reference> without success.

 

[Norie]

Craig

One of the easiest ways would be to name the range then use the name for the RowSource - no need for =, !, ; etc