How to Manipulate ComboBox and Drop Down List?

[feipezi]

Hi,
Suppose I have a couple of comboboxes. The first one has 2 values in the drop-down list, "A", "B"; the 2nd one has 4 values: "A12","A22","B12","B22". But if I select one value in box1, all the 4 values in box2 will be visible. Is there any way to make it work like this: if I pick "A" in box1, the drop-down list of box2 will only show "A12","A22". The same way is true for "B" -> "B12", "B22".
I tried to set up a IF condition for LinkRange but not working.
Thanks in advance.

 

[docjohn52]

once they're set, they have to be delete

use box1.change delete all, reload...

JB

 

[feipezi]

Hi JB,
Thanks for replying.
I have had it working. The code goes like this:

Private Sub regbox_Click()
Application.ScreenUpdating = False
If regbox.Text = "CMA" Then
statebox.Text = "MA"
distbox.ListFillRange = "dist_cma"
terrbox.ListFillRange = "terr_cma"
ElseIf regbox.Text = "CMN" Then
statebox.Text = "MN"
distbox.ListFillRange = "dist_cmn"
terrbox.ListFillRange = "terr_cmn"
....
....
Else:
statebox.Text = ""
distbox.ListFillRange = "district_name"
terrbox.ListFillRange = "territory_name"
End If
Application.ScreenUpdating = True
End Sub

However, if I do this, the graphs (command buttons) will not working well. The will come out but it will take forever and before it comes out, tab will shake like an earth quake. If I eliminate ...box_click, then everything will be fine.
I'm still working on it trying to find out what's going on. It must be some conflict among the events.
I guess it has something to do with what you said: they have to be deleted are they're set. Could you be more specific?
Thanks again.
JQZ.

 

[SkipVought]

I do this regularly using MS Query to return the customized list for the second combobox, based on the user's selection from the first combobox. faq68-5829.

I use a dynamically defined Name RAnge for the customized list. faq68-1331.

Almost no VB code; just a smidge for the query criteria et al.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[feipezi]

Skip,
Thanks for the tip. It's a great idea of using MS SQL to make up for what Excel lacks: ability of query. I'll do more homework on that (too bad I don't have MS Query installed).
I use a macro to create Named Range for multiple sheet, but without INDIRECT():

Sub tnt()
On Error Resume Next
Dim rngarray As Variant
rngarray = Array("district_name", "region_name", "territory_name", "regionlist", "distpayerlist", _
"regionramlist", "ram_code_list")
For i = LBound(rngarray) To UBound(rngarray)
Sheets(rngarray(i)).Activate
If Range("a2") <> "" Then Rows(2).Insert shift:=xlDown
ActiveWorkbook.Names.Add Name:=rngarray(i), RefersToR1C1:= _
"=OFFSET(" & rngarray(i) & "!R1C1,0,0,COUNTA(" & rngarray(i) & "!C1,4),COUNTA(" & rngarray(i) & "!R1))"
Next
On Error GoTo 0
End Sub

For dynamic Named Range, I've been using the following code, by checking the first 4 letters of a long list: territory_name, to define a Named Range (multiple subset of the long list):

Private Sub distbox_Click()
terrbox.ListFillRange = "terrtemp"
If distbox.Text = "CMNA" Then
SetNameRange "CMNA", "c1:c60", "l1:l50", "l", "terrtemp", 4
ElseIf distbox.Text = "CMNB" Then
SetNameRange "CMNB", "c1:c60", "l1:l50", "l", "terrtemp", 4
...
Else: terrbox.ListFillRange = "territory_name"
End If
End Sub

Sub SetNameRange(rd, source_rng, dest_rng, dest_col, namedrng As String, cut As Single)
On Error Resume Next
Dim rng As Range
Set rng = Range(dest_rng)
If Not rng Is Nothing Then rng.Clear
i = 2
For Each c In Range(source_rng)
If Mid(c, 1, cut) = rd Then
Cells(i, dest_col) = c
i = i + 1
End If
Next
rng.SpecialCells(xlCellTypeBlanks).Delete shift:=xlUp
rng.Name = namedrng
On Error GoTo 0
End Sub

My problem now is with the combobox click set up, the rest of features of my report do not work well, like graphs.
JB said, once the boxes set up, they must be deleted. I don't quite get it. How and when?
Please advise.
Thanks in advance.
JQZ