Same ComboBox Value to show in 3 other ComboBoxes in Seperate Userforms

[craigos]

Hi All,

I have a question that I need help with.

Scenario:
I have 4 Userforms, each one has a ComboBox on it.
Each Combo uses the same ListIndex on Sheet2 - 4 Districts say A,B,C & D.
Each Userform will show statistics to questions asked and will take the appropriate data from the appropriate range, also on sheet2 and show in a TextBox.


All the above no probs...however, the way I have it set up is that you select Question1 Userform - then select appropriate District from Cbo on that UF - data shows OK, but as you move to the next Question to view the stats on another UF, you have to reselect the same District from another Cbo.

The Question:
Is it possible to link Cbo1 on UF1 - when any of the Districts is selected - so that same selection will populate Cbo2 on UF 2 when a CommandButton is selected named: Next Question>>.

This action, i.e. taking the District selected on Cbo1 to Cb02 is then replicated for Cb02 to Cbo3 on UF3 and to Cbo4 on UF4 and backwards also on a CommandButton named: <<Previous Question.


I hope I have explained it correctly, but have attached a sample WB (much simplified) for ease.

Many Thanks

Craig

[mikerickson]

UserForm2.Combobox2.ListIndex = UserForm1.ComboBox1.ListIndex

[craigos]

Sorry mikerickson (http://www.vbaexpress.com/forum/member.php?u=10706), I don't quite understand where to put the code.

Tried it in commandbutton and in UF Activate on UF2 but nothing.

Craig

[Tinbendr]

What about a multi-page control? See attachment.

If you determined to use muliple userforms, store the Q1 combobox value to the sheet. Maybe at A2. Then when 2-? userform loads, load a LABEL from the A2 location. This way, the division becomes static across all forms.

[CodeNinja]

Craigos,
Looks like you want the default value in the combo box on the next form... to do this, you need 2 things...

First, you are unloading the question1 form before you get there, so you cannot see the data in it... instead of unload question_1, try question_1.hide or me.hide...

Now that you have data still in the userform, you can access it, so at the end of question_2's activate(), add the following line:
cboq2.Value = question_1.cboq1.value

this should default the value to whatever was in combo box 1 at the time of the click. You can control the other userforms the same way.

If this does not work for you, let me know and I can give you the code that worked for me... Hope this helps.

[CodeNinja]

One more note, because you are just defaulting the answer, the user could change the district between forms. This is fine if it is what you want. If you do not want that, you could restrict them to only having access to the first district by not adding all the items into cboq2, but instead of using the following line:
cboq2.AddItem Sheets("Sheet2").Range("A" & R).Value
replace it with this:
cboq2.additem question_1.cboq1.value

This would only put the one answer in the combobox and they would not be able to select a different district.

[craigos]

Tinbender...thanks for your reply....i will look at your solution shortly....

CodeNinja...Have tried your first suggestion and it does retain the initial selection in the combo....will test with the other UFs and get back to you...please bear with me as have to log off shortly as sleepies time and then work tomora....time difference and all that.

Must admit that I am intrigued to see your solution as well as the code posted but want to work through your code.....hope that OK

All the best

Craig

[craigos]

CodeNinja,

Works a charm thank you......however wth the risk of being cheeky, could you let me have the code that worked for you so I can compare and learn.

Many Thanks

Craig

[craigos]

Oops!....schoolboy error on my part.

The solution works great from one cbo to the other, however, when I tested on my actual WB, it fails because I forgot that as I am taking the same value from cbo1 to cbo2, but each cbo in the different UFs have a different list range to display.

Explanation:

In UF1, if cbo1 has 'A' selected, then the range displayed in the textboxes on UF1 are correct.

If I then go to UF2 and the same value (A) is then shown in cbo2 in UF2 (as taken from cbo1 on UF1), then the values that need to be displayed in the textboxes on UF2 are wrong because it is not using the range from cbo2 list range on that UF.

I hope that makes sense....any help?

Have to go to work now, so apolgies if anyone does post and I don't get back till later....can't access from work.

Craig

[CodeNinja]

Craigos,
I think you have a timing problem... But I cannot really determine as the code you loaded on 6-10 appears to be incomplete...

What I think is happening, is you are trying to populate the text boxes on form change. Unfortunately, you have set UF2 to load the data from UF1 on Activate... So...

What will happen is, you load UF1 fine. You change the Combo Box and the program says AHA! There is a change, and changes all the text boxes you have programmed it to do. The user than clicks to go to the next form, and it loads the next form. In the Activate, it puts in the data defaulted to the Combo Box. Unfortunately, this is not a change, so it does not know to put the data into the text boxes.

To fix this: You need to duplicate (or call a subroutine that handles it) the code that populates your data into the Text Boxes in the Activate _AFTER_ you have defaulted the combo box value.

I hope this all makes sense. If it does not, I will need to see the very latest code you have to help more.

Good luck!

[craigos]

CodeNinja,

Have taken your advice and put in a sub that populates the textboxes on each UF and works fine....thanks for making me think through the issue.

However, one I can't work out is when I move from Q1 to Q2 and then go back to Q1, then cboq1 on Question_1 UF has added the Districts again and therefore it reads in the dropdown as A B C A B C - this happens eveytime you go from Q1 - Q2 (and back).

I do need the choice of reselecting a District but only from Q1 so that a logical sequence is followed.

Any idea why that happens and how to ensure A B C is shown only once....revised WB attached so you can see the problem

Craig