Avansert nedtrekkslister i Excel / vlookup el.l

[deaktivert443556]

I Excel ønsker jeg å lage to nedtrekkslister hvor de tilgjengelige valgene avhenger av hva man har valgt i den første nedtrekkslisten. Eksempelvis kan man velge 1, 2, 3 eller 4 i den første listen. Og A, B, C eller D i den andre. Men dersom man velger 1 i den første listen, så skal kanskje bare A og C være tilgjengelig den andre listen. Velger man 2, så kanskje A, B og C være tilgjengelig.

 

Ideelt sett skal valget i liste 1 påvirke mulighetene i liste 2, og motsatt. Men jeg er realist, og jeg vil være meget fornøyd om jeg klarer å fullføre det som står i det første leddet i setningen over.

 

Med vlookup (finn.rad) kan jeg jeg hente ut det første alternativet til liste 2, men jeg tror ikke den funksjonen kan benyttes til å klare flere treff (kan den?). Derfor hadde jeg håpet på å kunne lage en makro som lager vlookup-funksjoner i flere celler etter hverandre, men som for hver celle endrer matrise-referansen til å starte etter forrige treff. Kanskje blir dette noe tungvint, men det er kanskje en annen måte å gjøre det på?

 

 

Edit: Vel, jeg har på en måte løst problemet ved en enkel makro som leter seg frem i tabellen ut ifra hva man har valgt, og deretter lager en liste (som brukes til nedtrekksliste 2) ut ifra hvor mange treff det er på valget i liste 1. Problemet er bare at området til nedtrekksliste 2 er dynamisk og må settes slik at det omfatter det største alternativet (11 valgmulighetet i mitt tilfelle), men da blir det en del blanke felter når det er få valgmuligheter. Dette trodde jeg ikke kom til å bli et problem siden de legger seg nederst, men når man åpner listen, så er den nederst i utgangspunktet og da må man i blant scrolle oppover for i det hele tatt å se noen valg. Kanskje må jeg bare skrote validerings-nedtrekklisten, og heller gå for den andre typen...

Endret 8. mai 2007 av Bradbury

[Harald Staff]

Kan du vise meg koden du bruker til å fylle liste 2 ? Den lar seg sikkert tilpasse.

 

Beste hilsen Harald

[aadnk]

Jeg ville lagd de to nedtrekkslistene som kolonner og rader i en tabell (fortrinnsvis i et egnet ark), der avkrysninger i cellene indikerer om liste 1 skal vise et element i liste to. Kodemessig kan dette (i arket med nedtrekkslister) implementeres således:

Klikk for å se/fjerne innholdet nedenfor

Private Sub cmbFirst_Change()

 

    Dim Row As Integer

   

    ' Raden vi skal slå opp kryss i

    Row = cmbFirst.ListIndex + 3

 

    ' Oppdater liste to

    With Sheets("Data")

        AddValues cmbSecond, .Range("C2", "IV2"), .Range("C" & Row, "IV" & Row)

    End With

 

End Sub

 

Private Sub Worksheet_Activate()

 

    ' Initialiser liste èn

    AddValues cmbFirst, Sheets("Data").Range("B3", "B65535")

 

End Sub

 

Public Sub AddValues(ComboBox As ComboBox, Range As Range, Optional Lookup As Range)

 

    Dim Index As Integer, oLookup As Collection, Cell As Object

 

    ' Lag oppslagsarray (urgh, burde vært en Stack-klasse i VB6)

    Set oLookup = ToCollection(Lookup)

   

    ' Fjern eksisterende verdier

    ComboBox.Clear

   

    ' Legg til hver celle inntil en tom er nådd

    For Each Cell In Range

        If Len(Cell.Value) <> 0 Then

           

            ' Merk at første indeks er 1

            Index = Index + 1

           

            ' Se om oppslagstavlen tillater dette

            If Index > oLookup.Count Then ' AndAlso

                ComboBox.AddItem Cell

            Else

                If oLookup(Index).Value <> "" Then

                    ComboBox.AddItem Cell

                End If

            End If

       

        Else

            Exit For

        End If

    Next

 

End Sub

 

Public Function ToCollection(Range As Range) As Collection

 

    Dim Cell As Object

 

    ' Lag et nytt objekt

    Set ToCollection = New Collection

   

    If Not (Range Is Nothing) Then

        For Each Cell In Range

            ToCollection.Add Cell

        Next

    End If

 

End Function

For å gjøre det enklere har jeg vedlagt et Excel-ark som eksemplifiserer dette. Lykke til.

 

Edit: Jeg tror jeg misforstod nedtrekkslister med kombinasjonsbokser. Harald Staffs løsning er nok derfor veien å gå, vil jeg tro. Den er bedre på både brukervennlighet og sikkerhet. For øvrig ser jeg nå at en helst bør definerer felter og ikke hardkode referansene direkte i koden.

Nedtrekkslister.zip

Endret 10. mai 2007 av aadnk

[Harald Staff]

Bare for moro skyld en løsning med "data validation"-bokser og formler. Ingen makroer altså:

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

 

HTH. Beste hilsen Harald

[deaktivert443556]

I utgangspunktet ville jeg bare bruke nedtrekkslisten via validering, men jeg var ikke klar over at denne ikke kunne hente en liste som ligger i et annet ark. Derfor bruker jeg denne til liste 1, mens jeg bruker kombinasjonsboks til liste 2.

 

 

Kan du vise meg koden du bruker til å fylle liste 2 ? Den lar seg sikkert tilpasse.

 

Beste hilsen Harald

8563214[/snapback]

Jeg skulle gjerne postet hele koden, men da hadde du ledd deg i hjel :!:

 

I dette skjemaet har jeg 2 x 8 nedtrekkslister, og for å danne alternativer til alle "2.listene" går koden gjennom de åtte "1.listene" hver gang excelarket kalkulerer. Deretter går den inn på Listearket, søker etter det som står i første nedtrekksliste, og kopierer cellen til høyre og X antall celler nedover som limes inn i området som benyttes til den første "2.listen". Dette gjentas da 7 ganger...

 

Foreløpig ser det ut til å fungerer fint, så da kan jeg ikke klage. Takk for forslagene, begge to.

[Harald Staff]

Validering kan hente fra andre ark via navngitte områder.

I ark1, merk lista og gi den et navn, f.eks Liste1.

I ark2, gå til en celle, åpne validering, velg tillat liste, og i listefeltet skriver du

=Liste1

 

Jeg kan ikke være mer spesifikk på menyer og greier uten å vite hvilken excelversjon/språkversjon du bruker.

 

Trenger ellers ikke hele koden, bare den som fyller comboboksen.

 

HTH. Beste hilsen Harald

[deaktivert443556]

Validering kan hente fra andre ark via navngitte områder.

8564775[/snapback]

Oj, et smutthull! Så fint, nå slipper jeg å ha denne listen gjemt i hovedarket.

 

Her er et utdrag fra koden. Den fungerer, så du trenger virkelig ikke gjøre for mye!

 

modl3 = Range("c19")
[...]
3:
If modl3 = "" Then GoTo 4
Range("a1:a136").Find(What:=modl3).Select
ant = Range("l1")
ActiveCell.Range("b1:b" & ant).Copy Destination:=Range("l3:l" & ant + 2)
4:
If modl4[...]

Under l1 har jeg en enkel funksjon som teller hvor mange alternativer det skal være i Liste 2.

[aadnk]

Oj, et smutthull! Så fint, nå slipper jeg å ha denne listen gjemt i hovedarket.

Dersom du leser litt på artikkelen Harald Staff linket til, finner du flere forklaringer til hvordan du legger til navngitte områder. Rent kodemessig benytter du dem ved å skrive Range("navn på felt her").

 

For øvrig bør du unngå GoTo og labeller. Du får langt mer strukturert programflyt dersom du bruker prosedyrer og IF-setninger i større grad.

[deaktivert443556]

Oj, et smutthull! Så fint, nå slipper jeg å ha denne listen gjemt i hovedarket.

Dersom du leser litt på artikkelen Harald Staff linket til, finner du flere forklaringer til hvordan du legger til navngitte områder. Rent kodemessig benytter du dem ved å skrive Range("navn på felt her").

 

For øvrig bør du unngå GoTo og labeller. Du får langt mer strukturert programflyt dersom du bruker prosedyrer og IF-setninger i større grad.

8571054[/snapback]

Den siden hadde jeg faktisk bokmerke på allerede. Jeg snublet innom den en gang da jeg søkte på google. Jeg så raskt at dette var en nyttig side så jeg satte bokmerke på den, og så fikk jeg meg en god latter etter å ha leste hele FAQen og så at det stod "Harald Staff" nederst