combox vullen gebaseerd op selectie van andere combobox

[shodan]

Hoi,

Het is zo moeilijk in het engels, dat ik het eens in nederlands naar hulp zoek.

Wat ik wil lijkt simpel, maar ik krijg het niet voor elkaar. Ik wil op basis van een selectie in mijn eerste combobox, mijn tweede combobox vullen. dit dacht ik te doen door de onchange event te gebruiken, maar daar lopt het steeds mis. Te pas en te ontpas triggert mijn code nu die change event. kan er iemand mij een voorbeeld code laten zien hoe ik dit moet doen. het lukt mij wel om bij het openen van het werkboek, mij comboboxen te vullen.

Mvg
shodan

 

[erik.van.geit]

Hi, Shodan,

Het zou makkelijk kunnen om allerhande voorbeeldcode te tonen, maar het lijkt me toch nuttiger om eerst jouw code te zien. Dat is handiger om te weten waar je naartoe wil...

Kijk alvast ook eens naar deze "beroemde" thread
(de link wijst naar de meest "relevante post")

http://www.mrexcel.com/board2/viewt...=0&postorder=asc&highlight=cascading&start=26
dat gaat over validering

met comboboxes kan je dit eens bekijken
http://www.mrexcel.com/board2/viewtopic.php?t=152281&start=5

en hier is nog wat uitleg
http://www.contextures.com/xlDataVal02.html

Maar laat dus gerust jouw code eens zien.

beste groeten,
Erik

 

[shodan]

Eric,

alvast bedankt om een poging te doen me in het nederlands te willen helpen. Ik ga dadelijk je voorbeelden bestuderen, maar hier zoals gevraagd reeds de code. (het gedeelte dat betrekking heeft op comboboxen)

Private Sub Cmdtest_Click()
'Application.ScreenUpdating = False

Call tosheet 'dit haalt items uit een lsitbox op
Call Plant_Item

'fill the comboboxes
Worksheets("BOM").Cmbitems.ListFillRange = "myrangeitems"

'Worksheets("BOM").Cmbitems.ListIndex = 1
Worksheets("DATA").Activate
Range("C2").Select
Worksheets("BOM").CmbPlants.ListFillRange = Range("myrangeplants").Address(external:=True)

Worksheets("BOM").Activate
Range("C3").Select
frmtreeview.Hide

'Worksheets("BOM").Activate
'Application.ScreenUpdating = True

End Sub

Sub Plant_Item()

'//Determine the different plants an items is made in.


'//first clear the current list of items and plants

    Range("C:D").Select
    Selection.Clear

Set rngselecteditem = Worksheets("DATA").Range("A1").CurrentRegion
inttotselitem = rngselecteditem.rows.Count

intlistitems = Worksheets("ITEMS").Range("A1").CurrentRegion.rows.Count

For i = 1 To inttotselitem
    For j = 1 To intlistitems
    intnextrow = Worksheets("DATA").Range("C1").CurrentRegion.rows.Count
        If Worksheets("DATA").Cells(i, 1).Text = Worksheets("ITEMS").Cells(j, 2).Text Then
        Worksheets("DATA").Cells(intnextrow + 1, 3) = "'" & Worksheets("ITEMS").Cells(j, 2).Text
        Worksheets("DATA").Cells(intnextrow + 1, 4) = Worksheets("ITEMS").Cells(j, 1).Text
        End If
    Next j
Next i

Worksheets("DATA").Range("C1").CurrentRegion.Sort Key1:=Range("C2"), order1:=xlAscending, Header:=xlNo

End Sub

Private Sub CmbPlants_Change()
'Worksheets("BOM").CmbPlants.ListFillRange = "myrangeplants"
End Sub

enkele opmerkingen; de comboboxen staan niet op een userform maar direct op een werkklbad (BOM) en ik tracht ze te vullen door middel van de listfillrange te gebruiken met een dynamische "named range".

Ik heb twee dynamische range, nl één voor mijn items:

myrangeitems :=OFFSET($A$1;0;0;COUNTA($A:$A);1)
en een voor mijn plants:
myrangeplants:=OFFSET(INDEX($C:$C;MATCH(BOM!$A$3;DATA!$C:$C;0));0;1;COUNTIF(DATA!$C:$C;BOM!$A$3);1)

myrangeplants werkt (maar waarschijnlijk zie jij dat wel op basis van de formule) op range C:D. in C staan mijn items, en in D de plants. Als een itemX bijvoorbeeld 2 maal voorkomt in kolom C dan staan in D twee verschillend plants, vb.plantY en plantZ.

beide rangen werken als ik ze evalueer met F9 in excell.

het vreemde is, dat als ik een selectie maak van items (op basis van een listbox die ik naar mijn werkblad exporteer) dat de items altijd in mijn combobox van de items staan, maar mijn plants niet. ik dacht dat ik mijn combobox van mijn plants telkens moet herladen (dmw het change event) maar dat werkt op geen enkele manier. Als ik echter mijn werkboek volledig sluit en terug open, dan zijn merkwaardig genoeg zowel mijn combobox met items gevuld, maar ook mijn comboboxen met de plants werken. Deze laatste mogen enkel die plants tonen die bij een item horen. dit betekent dan toch ook dat mijn dynamische named range moet werken???? ik snap er niets meer van!!!

Ik hoop dat ik duidelijk ben geweest met mijn uitleg en dat je er iets mee kan aanvangen. ik heb uren en uren het net zitten afsurfen maar tot not toe blijf ik hier vasthangen.

groetjes,
shodan

en nu uw links bekijken....

 

[erik.van.geit]

nog nooit gebruikte ik deze syntax

Worksheets("BOM").CmbPlants.ListFillRange = Range("myrangeplants").Address(external:=True)
'k heb niet zo'n goed gevoel bij die regel, 't lijkt wel of je verwijzen wil naar een andere werkmap

doe eens even

MsgBox Range("myrangeplants").Address(external:=True)

wat is het resultaat ?

Het is lastig tekst te lezen zonder voorbeeld. Toon alsjeblieft wat sampledata. Misschien een gelegenheid om Table-It uit te proberen. Dan is het ook meteen gedaan met formules moeizaam copiëren en vertalen ...

Nog een losse TIP:
code kan je nog meer leesbaar maken met WITH

With Worksheets("DATA")
    For i = 1 To inttotselitem
        For j = 1 To intlistitems
        intnextrow = .Range("C1").CurrentRegion.Rows.Count
            If .Cells(i, 1).Text = Worksheets("ITEMS").Cells(j, 2).Text Then
            .Cells(intnextrow + 1, 3) = "'" & Worksheets("ITEMS").Cells(j, 2).Text
.....    
    .Range("C1").CurrentRegion.Sort Key1:=Range("C2"), order1:=xlAscending, Header:=xlNo
End With

en/of door je sheets in een variabele te steken

Set ****ems = Sheets("ITEMS")
.....
... =  If .Cells(i, 1).Text = ****ems.Cells(j, 2).Text Then

tot gauw
Erik

 

[shodan]

  A        B  C        D   
1 10000200    10000200 P23 
2 10000300    10000200 P24 
3 10000700    10000300 P25 
4 10000900    10000300 P26 
5             10000700 P27 
6             10000700 P28 
7             10000900 P29 
8             10000900 P30 

DATA
[Table-It] version 05 by Erik Van Geit

Eric, dat is juist het probleem denk ik. Er zijn helemaal niet veel formules, dus daarom is het ook moeilijk uit te leggen.

Hierboven zie je de eerte stappen die uitgevoerd worden. Op mijn sheet "DATA" staat in de kolom A een lijst met items. Die haal ik op een een listbox. Daar heb ik geen enkel probleem mee.(to_sheet)

dan ga ik na gaan waar die items kunnen gemaakt worden. Die routine werkt ook (plant_item). Ok, misschien niet zo mooi geschreven maar ik die echt mijn uiterste best.

Ik heb een dynamische named range die terugslaat op kolom A via de formula zoals je in mijn eerdere post ziet (myrangeitems)
Dan heb ik een dynamische named range die slaat op de kolomen C en D. die formule zie je ook in mijn vorige post (myrangeplants)

Het enige wat ik eigenlijk wil is dat nadat ik mijn basis gegevens naar mijn sheet haal, is dat er twee comboboxen gevuld worden.
mijn eerste combobox: Cmbitems op basis van myrangeitems , en cmb plants op basis van myrangeplants. en het is daar waar er ergens iets schort aan mijn code.

Als ik dan in mijn cmbitems bv item 10000300 selecteer, wil ik in mijn Cmbplants alleen de plants P25 en P26 zien. Als ik dat sheet nu opendoe werkt het, maar als ik een nieuwe selectie maak, werkt het niet meer. Ik denk nu echter wel dat ik die change event niet nodig heb.

Is dat iets duidelijker? Ik hoop echt dat je me kan helpen.
Groeten,
shodan

 

[erik.van.geit]

dag, shodan,

Dit it uitgetest met jouw gegevens
pas nog even de namen van de comboboxen aan, zoals je wenst

Option Explicit

Private Sub ComboBox1_Change()
Dim LR As Long
Dim i As Long
Dim k As Long
Dim cnt As Long
Dim data As Range
Dim totArr As Variant
Dim subArr As Variant
Dim filterValue As String

filterValue = ComboBox1
LR = Me.Cells(Rows.Count, "C").End(xlUp).Row
Set data = Range("C1:D" & LR)

On Error Resume Next
cnt = Application.CountIf(data, filterValue)
On Error GoTo 0
    If cnt = 0 Then
    MsgBox "Item not found in list.", 48, "title"
    Exit Sub
    End If

ReDim subArr(1 To cnt)

    With data
    totArr = .Value
        For i = 1 To .Rows.Count
            If totArr(i, 1) = filterValue Then
            k = k + 1
            subArr(k) = totArr(i, 2)
            End If
        Next i
    End With

    With ComboBox2
    .List() = subArr
    .ListIndex = -1
    End With

Erase subArr
Erase totArr

End Sub

Private Sub ComboBox1_GotFocus()
Dim LR As Long
LR = Me.Cells(Rows.Count, "A").End(xlUp).Row

ComboBox1.List() = Range("A1:A" & LR).Value

End Sub

leer een beetje met arrays werken
dat laat de zaken vlot lopen
deze code maakt dus geen gebruik van de benoemde bereiken
de lijst hoeft ook niet gesorteerd te zijn (dezelfde items hoeven niet juist onder elkaar te staan)

beste groeten,
Erik

 

[shodan]

Eric,

dan voor de code. Wel wat ingewikkeld voor mij, maar ik ga het proberen.

Ik heb de namen van de comboboxen aangepast, maar ik krijg een permission denied error bij het stuk van de gotfocus.

Groeten,
shodan

 

[shodan]

eric,

Door je code te bekijken in nogmaals te bezinenn over mijn code, heb ik het ineens precies gevonden:

Private Sub Cmdtest_Click()
'Application.ScreenUpdating = False



Call tosheet
Call Plant_Item

'fill the comboboxes
Worksheets("BOM").Cmbitems.ListFillRange = "myrangeitems"

'//dit stuk is overbodig!!!!!!
''Worksheets("BOM").Cmbitems.ListIndex = 1
'Worksheets("DATA").Activate
'Range("C2").Select
'Worksheets("BOM").CmbPlants.ListFillRange = Range "myrangeplants"

Worksheets("BOM").Activate
Range("C3").Select
frmtreeview.Hide

'Worksheets("BOM").Activate
'Application.ScreenUpdating = True

End Sub

Volgens mij had ik een stuk in de code dat in conflict was met mijn dynamische range. Ik probeerde telkens ook de plants opnieuw te laden in mijn combobox, maar doordat ik een dynamische range had, moest ik dit helemaal niet doen. Ik heb dat juist in commentaar gezet en het werkt perfect. Kan dit kloppen wat ik zeg? Daar ben ik nu dus al twee weken achter aan het zoeken

Nu heb ik nog één vraagje:
Hoe zou ik ernu voor moeten zorgen dat als ik een nieuwe selectie heb in mijn combobox, die ook onmiddelijk toont. momenteel blijft mijn oude selectie, met mijn oude plant staan. pas als ik een de dropdown box gebruik, kan ik mijn nieuw item zien en aanklikken, en dan moet ik ook de plant aanklikken. Ik zou liever hebben dat dit automatisch is, ofdat mijn comboboxen eigenlijk niets tonen.

Heel heel heel fel bedankt voor je help, maar je kan niet geloven hoe blij ik ben dat ik toch mijn simpele code heb kunnen gebruiken.

 

[erik.van.geit]

Hoe zou ik ernu voor moeten zorgen dat als ik een nieuwe selectie heb in mijn combobox, die ook onmiddelijk toont. momenteel blijft mijn oude selectie, met mijn oude plant staan. pas als ik een de dropdown box gebruik, kan ik mijn nieuw item zien en aanklikken, en dan moet ik ook de plant aanklikken. Ik zou liever hebben dat dit automatisch is, ofdat mijn comboboxen eigenlijk niets tonen.

zie het stukje in mijn code

    With ComboBox2 
    ....
    .ListIndex = -1 
    End With

Dit zet de box op "leeg"

ComboBox1 = ""

doet zowat hetzelfde

Jouw laatste reply geeft me de indruk dat jet voor de listfillrange zelfs helemaal geen code nodig hebt. Je kan eenvoudigweg die eigenschap invullen. Door de vreemde combinatie van dynamic range + code zat ik op een ander spoor.
In elk geval is mijn code in veel gevallen heel nuttig: ooit zal je nog wel eens een combobx moeten filteren... Dan zal je de code hier terugvinden.

"Permission denied": wellicht had je nog een "ComboBox1" of 2 of er zat nog een box met die naam in het geheugen. Als je aan het frutselen bent geraak je op den duur ook het overzicht kwijt, hé? Daarom kan je best code en userformstuff in een nieuw bestand uitproberen en pas daarna overbrengen naar je bestaande project.

tot ziens,
Erik

 

[shodan]

Eric,

Ik ga zeker en vast je stuk code bijhouden!!!
Ik vind ook dat je gelijk hebt ivm die arrays. Ik zou dit inderdaad moeten leren, maar eraan beginnen he ...! Weet je ergens een goed post die als tutor kan dienen zodat ik dat goed kan begrijpen? Ik weet niet ook niet juist wanneer ik dat juist in toepassing zou moeten brengen. Ik bedoel, waar baseer je je op, om de optie van arrays te overwegen?

Groeten,
Joris