How to clear up a dropdown list for a ComboBox?

[feipezi]

Hi,
Supposed I have a ComboBox with a dropdown list, which is based on a named range. The size of the dropdown list depends on the value selected in another ComboBox where I have value of 'region', 'district'. Usually district has bigger dropdown list than region. However, as I select 'region' first, which has 10 rows in the dropdown list; then if I select 'district', the dropdown will show only 10 rows of district and the rest (about 20) won't show.
Is there anyway of clearing all the dropdown before the second selection?
Here is the code behind the ComboBoxes.

Private Sub cmbSelectView_Change()
Dim CurrSheetName As String
Dim strSelection As String
Application.Calculate
CurrSheetName = ActiveSheet.Name
If CurrSheetName = "DataView" Then
On Error Resume Next
ActiveWorkbook.Names("Acct_NameRange").Delete
ActiveWorkbook.Names("TLN_NameRange").Delete
On Error GoTo 0
If strSelection = "District" Then
Sheets("Dist_tnlist").Range("A2").CurrentRegion.Name = "TLN_NameRange"
Sheets("distacctlist").Range("A2").CurrentRegion.Name = "Acct_NameRange"
ElseIf strSelection = "Region" Then
Sheets("Reg_tnlist").Range("A2").CurrentRegion.Name = "TLN_NameRange"
Sheets("regacctlist").Range("A2").CurrentRegion.Name = "Acct_NameRange"
End If
End If
End Sub

Thanks in advance.

 

[ck1999]

Are you referring to the listrow property or that only 10 items are located in your dropdown list?

ck1999

 

[SkipVought]

Hmmmmm?

What's the difference here?

    If strSelection = "District" Then
       Sheets("Dist_tnlist").Range("A2").CurrentRegion.Name = "TLN_NameRange"
       Sheets("distacctlist").Range("A2").CurrentRegion.Name = "Acct_NameRange"
    ElseIf strSelection = "Region" Then
       Sheets("Reg_tnlist").Range("A2").CurrentRegion.Name = "TLN_NameRange"
       Sheets("regacctlist").Range("A2").CurrentRegion.Name = "Acct_NameRange"
    End If

please use TGML Code tags for code. Search this page for TGML.

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[WCL01]

Is all your trying to do is make the second drop down list dynamic depending on what is selected in the first drop down?

 

[feipezi]

Hello folks,
Sorry to get back to you so late since I was out of town.

ck1999,
No, the length of dropdowns is variable depending on strSelection. BTW, I have 3 boxes, the one with strSelection has the control over the other 2. So the length of dropdown in the other 2 boxes depends on what value strSelection bears.

Skip,
the difference is that the named range will be overwritten each time strSelection is reset, because the named range has the same name no matter what strSelect really is.

WCL01,
that's correct.

In fact, I found the answer. The reason why the code that I attached did not work well was that I did not reset the ListFillRange of the 2 boxes every time strSelection changes. Here is part of the code:

If CurrSheetName = "DataView" Then
strSelection = Range("D14").Text
On Error Resume Next
ActiveWorkbook.Names("Acct_NameRange").Delete
ActiveWorkbook.Names("TLN_NameRange").Delete
On Error GoTo 0
With ActiveSheet.OLEObjects("cmbKeyAcct")
If strSelection = "Territory" Then
Sheets("terracctlist").Range("A2").CurrentRegion.Name = "Acct_NameRange"
.ListFillRange = "Acct_NameRange"
ElseIf strSelection = "District" Then
Sheets("distacctlist").Range("A2").CurrentRegion.Name = "Acct_NameRange"
.ListFillRange = "Acct_NameRange"
ElseIf strSelection = "Region" Then
Sheets("regacctlist").Range("A2").CurrentRegion.Name = "Acct_NameRange"
.ListFillRange = "Acct_NameRange"
ElseIf strSelection = "OU" Then
Sheets("areaacctlist").Range("A2").CurrentRegion.Name = "Acct_NameRange"
.ListFillRange = "Acct_NameRange"
End If
End With
....


Thanks a lot for your input.

 

[WCL01]

If your getting your data from a table like this.

Field1 Field2 Field3
a 1 3
a 3 6
b 23 43
c 3 4
c 7 s
c 8 2

Then what I would do is set the second list boxes control source to a query that says something along the lines of select field2 where field1 = listbox1 on the form (field1 = [frmMain]![listbox1].

Then do vb code after update of listbox1 that says listbox2.requery. This should refresh (requery) listbox2 every time a new selection is picked in listbox1 you can do the same thing for lets say listbox3 if the choices in that box are dynamic depending on whats selected in listbox2. Both listbox2 and 3 will be empty until something is selected in listbox1. In case none of this was clear I'm going to put in my code

A quick break down. List56 is based on a distinct query, List58 is also a distinct query however it selects the value of field2 in the table where field1 = List56. So every time you update List56 is clears out the values of List58 and List60 and does a requery of List58. Then when a value is selected in List58 it clears out List60 and does a requery. The query for List60 is similar to the other query however it says to Select the value of field3 where field1 = List56 and field2 = List58
<code>
Private Sub List56_AfterUpdate()
List58 = Null
List60 = Null
Me!List58.Requery
End Sub

Private Sub List58_AfterUpdate()
List60 = Null
Me!List60.Requery
End Sub
</code>

I hope at least a little of this was some help.

 

[fumei]

feipezi, as Skip mentioned, please use the TGML code tags when posting code. It makes it easier for us all. Thanks.

WCL01, the TGML tags use square brackets - [ and ] - not angle brackets - < and >.

Gerry