Multiple combo boxes (form control) - reset cell link number to 1 automatically

Question

Hey everyone,

I have a small issue with using multiple combo boxes' (form control) cell links.

let's say - i have 3 baskets with different set of items inside of them:

Basket 1 - carrot, potato

Basket 2 - cucumber, onion and tomato

i have two drop down lists: 

1st - basket 1, basket 2

2nd - list of items - updates automatically based on the basket chosen.

combo box (form control) is used for each drop down list. when an item is chosen from a list it produces a cell link with an ordinal number of the item chosen.

so, choosing basket 1 and potato would produce cell links:

1

2

basket 2 and onion:

2

2

etc.

NOW, the problem is - if i choose basket 2 and tomato (it gives us cell links equal to 2 and 3) and then I switch back to basket 1 - items' cell link would still be equal to 3. Whereas I want items' cell link automatically update to be equal to 1 every time i choose another basket without being have to choose first item manually.

please note that example provided is very simple compared to the actual set of data. if you were unable to recreate the problem - just tell me how to make all cell links automatically update to 1 when I choose another basket.

Thank you.

Answer 1

I have tried "ActiveX controls and VBA only" method you suggested- it does the job, however not perfectly.

if I alter my choice using combo box (form control) drop down list - 2nd cell link does not change automatically. i can not understand why.

a) Please download and look into my file, IMHO you will be surprised.

b) The Worksheet_Change event is not fired if you change the form control, even if you have assigned a cell link.

As you can see in my file, it is possible to get it to work with a cell data validation list or ActiveX controls, but form controls... you need workarounds and the result at the end is not nice.

Andreas.

Answer 2

thanks for the reply.

I have tried "ActiveX controls and VBA only" method you suggested- it does the job, however not perfectly. it would be nice if just choosing another bakset would reset other cell links to 1 automatically. otherwise it becomes very inconvenient.

i tried another VBA but it worked in a weird way. basically, it results in the following - when i manually alter 1st cell link (basket) - other cell link (items) changes to 1, BUT if I alter my choice using combo box (form control) drop down list - 2nd cell link does not change automatically. i can not understand why.

code used:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = Range("D1").Address Then

Range("D9").Values = 1

End If

End Sub

your help is appreciated.

Answer 3

That's hard to solve with Form controls, because you need a combination of formulas and VBA code.

An easier way is to use ActiveX controls and VBA only, have a look into this file:

https://www.dropbox.com/s/hwi91qpjgh2nb8j/Casca...

Andreas.