Listbox mystery - any ideas?

[Dav1d]

So I've been (painfully) teaching myself UserForms (with a little help). Not great by any means but I'm progressing ok.

Yet this has me absolutely stumped and I'd really appreciate a clue if anyone will lend me one.

The UserForm has 5 Tabs, each has a Listbox drawing from the same RowSource "MyList" (a list of 3 choices) and they are linked back via ControlSource to a column in a worksheet "Workings" B1 through B5.

Listboxes 2 - 5 work perfectly. You can make a selection, the selection gets a blue background, it copies to Workings!B2(3,4,5).

Listbox 1 won't do this. The selection doesn't turn blue, nor does it transfer to Workings!B1. Actually, it's more irrational, it does turn blue on the 3rd selection of the list but not the first 2.

I've tried deleting and recreating the listbox, copying one of the working ones from tabs 2 -5, all with the same result.

Any ideas - short of 'Give Up' - would be welcome
David

 

[Norie]

David

How exactly are you using ControlSource?

That can only be one cell, is it set to B1 for 1st listbox, B2, for 2nd and so on.

Also you mention 'tabs', do you mean a TabStrip control?

Finally, and I apologise for all the questions, what are you actually trying to do?

The first thing I can think of doing, or not doing if you like, is not to use ControlSource for any of the lisboxes.

 

[Dav1d]

Thanks for responding

Yes, it's a Tab Strip control

ControlSource: Workings!B1 [This is the faulty box]
The other 4 tabs are linked to Workings!B2, Workings!B3, Workings!B4, Workings!B5, respectively

 

[Dav1d]

Missed a question, sorry:

What am I trying to do?

It's an assessment. The main body of the Userform is a set of choices with descriptions, the Listbox provides them with the title of the choices and asks them to pick one.

In "Workings" the answers are used to generate other content.

 

[Norie]

Why not just add a little code to put the values on the worksheet instead of using ControlSource?

Something like this for a start maybe.

For I = 1 To 5
     Worksheets("Worksheet").Range("B" & I).Value Me.Controls("Listbox"& I).Value
Next I

The reason I'm sort of recommending not using ControlSource is because you can cause unpredictable result.

With code like the above you can 'dictate', to some extent anyway, what's going to happen.

By the way are the listbox items some sort of multiple choice?

eg Yes/No/Maybe

 

[Dav1d]

Hi Norie

Yes, the listboxes are a multiple choice - select 1 from 4 choices

I'm certainly experiencing the 'unpredictability of the ControlSource route'. I've been trying to apply different code variations to bypass this but with no luck so far.

How would I apply the code you provided?

Many thanks
David

 

[Norie]

David

Hard to tell without further information and/or seeing what code you currently have.