Data Validation VS Combobox (Form VS ActiveX)

[Ancient Wolf]

Hello All,

I am using Excel 2007 and I've been working with a spreadsheet that utilizes several Data Validation to allow users to make different choices and depending on those choices the next options in line change. My problem is that I have so many different options, so long of formulas, that I run out of space to type in formulas in the DV source field. One way I get around this is to drop down to the next cell and start again, but I really need all the choices to stay in one row. Somebody suggested using VLookup, but I am not sure if that is the right path to take because a few of the final drop down choices have multiple options, and I'm not clear on how to use VLookup. I am strongly considering using a Combobox Form Control or Combobox ActiveX control because, from what I have learned, they wouldn't be restricted by formula space. However, being fairly new to Excel I do not know what the codes would be to allow the comboboxes to change options based on previous selections. I've searched the message boards extensively and haven't been able to find an answer. What would you recomend? Please help.

 

[xld]

Stick with DV, it is far better integrated.

I am not really sure what roblem you have, can you expand on it?

 

[Ancient Wolf]

The problem I have with DV is that there is only so much room to type my IF formulas in into the DV source field. After the formulas get so long I can no longer type in any more formula. So far, I haven't found anybody else run into this issue.
Example formula: =IF($F$2=FC,FD,IF($F$2=KC,KD,IF($F$2=LC,LD,IF($F$2=MC,MD,IF($F$2=QC,QD,IF($F$2=RaC,RD,IF($F$2=SC,SD,IF($F$2=TC,TD,IF($F$2=UC,UD,IF($F$2=WC,WD,IF($F$2=ZC,ZD,IF($F$2=AAC,AAD,IF($F$2=ABC,ABD,IF($F$2=AGC,AGD))))))))))))))

I shortened my named ranges as best as possible, but if I try to type in much more formula than what it shows above, Excel stops short and the cursor will not move any further. That is why I am looking for other options. Dropping down a cell is one choice, but after a while, I run out of room again and will eventually turn the spreadsheet into an Easter egg hunt. So to speak.

 

[Ancient Wolf]

Data Validation Running Out of Room

Hello everybody: I know I have posted this issue once, but only got one response so I am trying again.

I am currently using Excel 2007 and still pretty new at it. I have created a spreadsheet workbook that currently utilizes 7 Data Validation drop down menus. Five in one row. Two in another. I learned how to use IF formulas to limit and change the available options that users can pick from. The chosen selections then copy over to another cell that the user can copy and paste notes from. I really need to keep the drop down menus limited to just the five in the one row, but have learned that the DV Source field box only allows so much room for the formulas before it suddenly stops short. (I haven't counted just how many keystrokes it allows)

So my real problem is I am trying to find ways around this limitation and have searched your forum extensively, but cannot find a solution. Can anyone help me? I have looked into using vlookup and the indirect formulas, but can't make heads or tails on how they work. I am also considering using either form or activex control comboboxes, but not sure if they are the right answers either. Please please please help.

 

[Ancient Wolf]

Nobody can help me? I've given up on Data Validation. I am now looking for the codes that would get 5 form control combo boxes or 5 activex control combo boxes to change list options based on previously selected choices in the other boxes. All of my possible combination choices are in a tables sheet and named and saved in the name manager. I learned I can get the individual slections appear by typing in their names in the listfillrange, but do not know how to make the ranges change based on the selections in other combo boxes. I tried using the following code for activeX and adapt it to my workbook, but cannot get it to work:

Private Sub ComboBox1_Change()
If Range("A1") = 1 Then
ComboBox1.ListFillRange = Range("First").Address
ElseIf Range("A1") = 2 Then
ComboBox1.ListFillRange = Range("Second").Address
End If

End Sub

Same goes for this code for a form control combo box:

Sub DropDown1_Change()
If Range("A1") = 1 Then
ActiveSheet.Shapes("Drop Down 1").ControlFormat.ListFillRange = Range("First").Address
ElseIf Range("A1") = 2 Then
ActiveSheet.Shapes("Drop Down 1").ControlFormat.ListFillRange = Range("Second").Address
End If
End Sub

I tried recording a macro and put the code in but it wouldn't work. I do not know what else to do.

 

[RoryA]

Generally speaking, for data validation, the issue is how you arrange your lists. There is an excellent tutorial on dependent lists on Debra Dalgleish's site here.

 

[Ancient Wolf]

Thank you for the reply. I have actually seen that website, but I just do not think it works for my situation. Maybe its because I don't quite understand how the Indirect formula works, but my spreadsheet is more complicated than simply comparing fruits and vegetables. There are currently 49 possible scenarios that a user can come up with. So someone can choose fruit, and choose apple, but then they may need to choose granny apple or delicious red, and then need to choose worms or no worms, and then if there are worms take one action. if no worms take another action. I've never used Excel to this scale before, so I still learning which is one of the reasons why I want to get the combo boxes to work.

 

[Ancient Wolf]

I'm sorry. Am I supposed to be selecting Quick Reply or Post reply when answering?

 

[RoryA]

You can actually extend the table format pretty much without limits. Each subitem in the first table will simply lead you to a different table and so on.
If you want to use comboboxes instead (which works quite well if you only have a few cells that are used for data entry), then you can use code similar to what you have, except you don't really want to change a dropdown's list in its own click event. That should be used for changing the list for the next dropdown.

 

[dafan]

I'm sorry. Am I supposed to be selecting Quick Reply or Post reply when answering?

Doesnt matter

I suggest using a build like this. Make a button and have it start like this (this is just 2 comboboxes with 2 options but you should get the idea)

Private Sub CommandButton1_Click()
ComboBox1.Clear
ComboBox2.Clear

With ComboBox1
.AddItem "Fruit"
.AddItem "Vegetable"
End With
End Sub

This will add Fruit and Vegetable to Combobox1.

Next, the combobox1 is changed:

Private Sub ComboBox1_Change()
With ComboBox2
Select Case ComboBox1.Value
    Case "Fruit"
        .AddItem "Apples"
        .AddItem "Bananas"
    Case "Vegetables"
        .AddItem "Broccoli"
End Select
     
End With
End Sub

Now the second box is filled with Apples and Bananas. You can add box #3 yourself and build a change event just like I did with combobox1.

You can even choose to hide or disable Combobox2 when Combobox1 didn't get chosen yet

Combobox2.Visible = False

Etc.

Hope this gives you a push in the right direction.