excel dropdown box

[toryee]

Is there a way to create a drop down menu in a combobox in Excel? I don't want the dependent list in Data Validation, I want everything in one combobox.

For example, if the user clicks on the combox,
Level 1 choices:
Vegetable
Fruit

Then if Vegetable is selected,
Level 2 choice for Vegetable appears like a submenu to Vegetable:
Celery
Cucumber
Cabbage
...

thanks.
Tory

 

[xlbo]

And what have you tried so far ??

Have you put any comboboxes on the sheet ?
Have you looked at the help files ?
Have you looked at the object model ?

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[toryee]

I guess everything was in my head. I worked on putting the combobox on the sheet, and realized I don't know how to attach the combobox to a cell of the spreadsheet.

I tried setting up the Data, Validation for cell A1 and have the following code in a procedure:

cbo = "Apple, Pear, Orange"
With Application.Worksheets("Sheet1").Range("A1").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=cbo
.IgnoreBlank = True
End With

The code wouldn't go past the .Add line. Maybe this question should come before my previous question.

thanks
Tory

 

[xlbo]

Ok - so which do you want ?? You say NO data>validation in your 1st post but then post Data>Validation code...

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[toryee]

I don't really know; whichever works. I don't know enough to know the right terminology. I have found this thread thread68-578865, which is exactly my question, but the answer to it, the dependent list, is not what I'm looking for. I want everything in one dropdown list, as per the first post, not more than one.

thanks
Tory

 

[xlbo]

And you are happy to use VBA ??

Do you need users to be able to "back up" out of their selection or should it continue through to the end ?? To that end - how will we know when the dropdown is showing the "last" set of values which should be chosen and when it is a set that should link to another set of options ??

To keep this in 1 dropdown list is inviting difficulties and complexity that need not be there. Why can you not just have dependant lists ??

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[toryee]

> And you are happy to use VBA ??
What does this mean?

I just think one menu combobox looks better and more user friendly than making multiple cell selections. I probably will using the latter given your suggestion.

thanks.
Tory

 

[xlbo]

This means that you can only get what you want using VBA - just thought I would ask whether you were comfortable with that ??

1 menu combobox may well look more "friendly" but it is also more "challenging" to set up and use.....multiple drop down Data>Validation lists can be managed with no code. What you are asking for would need quite a lot of code...

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[toryee]

Thanks very much for your help Geoff.
I'll go with a custom menu for now.

Tory