Listbox mystery - any ideas?

Dav1d

New Member
Joined
Sep 27, 2010
Messages
27
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
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Sep 27, 2010
Messages
27
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

New Member
Joined
Sep 27, 2010
Messages
27
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.
Code:
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

New Member
Joined
Sep 27, 2010
Messages
27
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
David

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

Watch MrExcel Video

Forum statistics

Threads
1,122,755
Messages
5,597,929
Members
414,193
Latest member
bb60

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top