Userform listbox: allow scrolling; disable selecting

[Jeff H]

I want to be able to make it so items in a listbox cannot be selected but the user can still scroll through the list.

Here’s the situation: The userform is used for editing various drop down lists maintained in named ranges. It has a textbox and a listbox. The user selects an option button to a.) enter a new item in the textbox, which will be added to the list; or else b.) select an item from the existing list which will appear in the textbox for editing and then replace the current item in that list.

With the Add option selected, I want them to be able to scroll through the list to be sure they aren’t duplicating an item, but I don’t want them to be able to select any items in the listbox.

With the Edit option selected, I want them to select an item from the list which then populates the textbox. They edit it and click a “Replace” button to replace the edited item. (This part already works fine.)

I’ve tried “lstList.ListIndex = -1” in lstList_Click, lstList_MouseDown, lstList_MouseUp, and in the Command Button that activates the replacement action. So if that is the correct command, I clearly don’t how to apply it.

Thanks,
- Jeff

[HansV]

I'd let the users select an item in the list box even if the "Add" option is active.
In the code behind the command button, simply use the text box and ignore the list box if the "Add" option has been selected.

[Jeff H]

Yes, I guess that's the simplest solution. I take it my idea can't be done or it's too complicated to be worth the effort.

Thanks Hans.

[HansV]

An alternative would be to use a combo box with Limit to List set to Yes, and an On Not in List event procedure to add a new item to the list.

See ComboBox.NotInList event (Access)

[Jeff H]

I forgot to add in my original post that this is in Excel. This database I'm building really should be in Access, but I'm just not proficient enough to pull it off.

Any chance Not In List can work in Excel?

[HansV]

Sorry for assuming that you were working in Access.
I'd keep on using the list box and text box.

[rory]

You could put a scrollbar control over the actual scroll bar for the listbox and use that to scroll the list (using the TopIndex property of the listbox set to the value of the scrollbar). Then you can simply lock the listbox when required.

[Jeff H]

Thanks Rory, this sounds interesting, but I can't see how to set it up.

First, I can't make the Scrollbar control sit on top of the Listbox. When I drag it to the text box or buttons it covers them, but even after setting Bring Forward/Front for the Scrollbar and Send Backward/Back for the Listbox, the Listbox always covers the Scrollbar.

Then, I don't understand the settings. The Value of a new Scrollbar is 0, but I can't set the TopIndex of the Listbox to 0. What is the linkage between the two?

[rory]

You'd use the scrollbar's change event to synchronise the two:

Code: Select all

    ListBox1.TopIndex = ScrollBar1.Value

for example. I don't know why you can't simply put the scrollbar over the listbox's scrollbar; it works for me.

[Jeff H]

Ha! Very nice!! When I put it to the side of the Listbox and disable the Listbox, the new Scrollbar works great.

I guess there must be something about the Listbox that insists on being on top. I'll try deleting it and adding a new one yet.

In any case, thanks for the tip. I've wondered what the Scrollbar control was for.

- Jeff