Listbox vs Data Validation

[rrtts]

What are the advantages disadvantages of using a Listbox over the Data Validation Method. They both seem very similar in that they provide a list of selectable input.

Also, I saw on a website the mention of "cascading drop down lists" - is this possible using the data validation method?

Basically it reads, "When you select a country in the first drop-down list you want only the cities from this country to show in the second drop-down list. We call it cascading drop-down lists."

I'm trying to achieve something similar to this, but they want you to buy their "template.xls" file in order to see how they've done it.

Any ideas?

[Bob Phillips]

What are the advantages disadvantages of using a Listbox over the Data Validation Method. They both seem very similar in that they provide a list of selectable input.

Personally, wherever possible, I would use DV over a Listbox.

Also, I saw on a website the mention of "cascading drop down lists" - is this possible using the data validation method?

Certainly is. See http://xldynamic.com/source/xld.Dropdowns.html#dv or http://www.contextures.com/xlDataVal02.html

Basically it reads, "When you select a country in the first drop-down list you want only the cities from this country to show in the second drop-down list. We call it cascading drop-down lists."

I'm trying to achieve something similar to this, but they want you to buy their "template.xls" file in order to see how they've done it.

Any ideas?

Pay? What is the world coming to? See the links above. My example is continent/country oriented.

[rrtts]

Looks like I have a new excel website to favorite...

xld - I know I've told you dozens of times before...but you are truly a blessing. Thanks for all your help!

*Edit In*
When I run the DV method - I get a runtime error? Am I doing something wrong...I prefer this method for my application over the combo box...

[Bob Phillips]

rrtts,

which one did you try, and what error?

Any chance of posting the workbook?

[rrtts]

Sure...I'm sure it is something simple...but beyond my level of knowledge...heh heh...which isn't too much.

The combo example works like a champ...the dv example...I get a runtime error 1004 - Method Range of Object _ Worksheet failed error whenever I select a continent...

Attached as requested...

[geekgirlau]

[vba]
'---------------------------------------------------------------------
Private Sub Dropdown_Change(ByVal Target As Range)
'---------------------------------------------------------------------
Dim oFoundCell As Range
Dim iTargetCol As Long
If Not Intersect(dv.Range("List1"), Target) Is Nothing Then
If Target.Count = 1 Then

With data.Range(kList1Hnd)
Set oFoundCell = .Find(what:=Target.Value, _
LookIn:=xlValues)
If oFoundCell Is Nothing Then
MsgBox "Critical error"
Exit Sub
End If
End With

'load the List2 dropdown and set the default to item 1
iTargetCol = oFoundCell.Column - 1
fzCreateValidationList2 Target.Offset(1, 0), iTargetCol, Target
Target.Offset(1, 0).Value = data.Range(kList2Hnd & iTargetCol).Value
End If
End If
End Sub
[/vba]

Use of data validation versus combo boxes is a matter of personal choice to a large extent. I like dv on sheets because you don't need to use a mouse to navigate to the drop-down list, however if you have a long list of items it's annoying not being able to start typing an entry to select it.

[Bob Phillips]

This is very odd, I can reporduce the problem, but I can't reproduce it when tracing it (?).

can you see if you still get the error with this version?

[rrtts]

Regret delay in responding...time zone differences...heh heh.

Your posted file works like a champ. Appreciate the help, that is exactly what I needed/wanted...and best of all...free!

Thanks a million.

[Bob Phillips]

Regret delay in responding...time zone differences...heh heh.

Your posted file works like a champ. Appreciate the help, that is exactly what I needed/wanted...and best of all...free!

Thanks a million.

But do you know, the odd thing is that I didn't change a thing. Just opened it, got the error, aborted, ran the workbook open again, and saved it. Odd!

[johnske]

Ran fine for me, got no errors. But I noticed that in the VBE it's showing that it hasn't been compiled - perhaps that's why the strange error the first time?

[rrtts]

This is very odd (?).

Indeed...the file you posted works...but if I try and edit it into mine I get the same error. If I start from scratch I get the error.

I think my problem is the shared code between the two examples you have listed...(combo box and data verification).

I'm still tinkering with it...just glad to know it's possible to do it.

[Bob Phillips]

Indeed...the file you posted works...but if I try and edit it into mine I get the same error. If I start from scratch I get the error.

I think my problem is the shared code between the two examples you have listed...(combo box and data verification).

I'm still tinkering with it...just glad to know it's possible to do it.

I think that you are right. I should oiutsort them into two workbooks. Don't hold your breath, it may take a while.

[rrtts]

I found a solution...

Too easy. Works like a champ.

http://www.contextures.com/xlDataVal02.html

[mail2bharath]

thankzzzzzzzzzzzzzzzzz