Pro tip: Populere en Excel UserForm-kombinasjonsboks uten VBA

Forrige måneds artikkel, "Pro tip: Legg til en UserForm for å hjelpe datainnføring i Excel, " deler det grunnleggende om å opprette en Excel UserForm ved hjelp av VBA for å fylle listekontroller. For å oppdatere listen, kan du legge til eller slette elementet i VBA-prosedyren. Denne måneden vil jeg dele teknikker for å fylle ut kombinasjonsboksen og listekassekontrollene ved å bruke data som er lagt inn på arknivå.

Før vi kommer i gang, har jeg to råd til arkivnivå:

  • Bruk et dedikert ark for lister, selv om det bare er en liste. Sjansen er stor for at du vil legge til mer når du får tak i prosessen, og du vil finne et dedikert ark praktisk, effektivt og trygt. Det dedikerte listearket i eksemplet er navngitt, passende nok, lister.
  • Hvis mulig, skriv inn listeelementene i en rekkefølge som gir mening for dataene og måten brukerne vil bruke dem på. For eksempel kan du bruke alfabetisk eller numerisk rekkefølge. Det er ikke alltid praktisk å legge inn listeelementene i rekkefølge, men når du kan, kan du gjøre det. Å sette en liste i rekkefølge etter faktum kompliserer ting.

Bruk et områdenavn

Kanskje er den enkleste måten å fylle ut en av Excel's listekontroller å gå inn på listen på arknivå og gi listen et områdenavn. Deretter kan du referere til det området som kontrollens Row Source-egenskap. Det er den enkleste metoden, men den minst fleksible. For å komme i gang trenger du en liste, for eksempel den som er vist i figur A. Du kan jobbe med hvilken som helst liste, eller du kan laste ned eksemplet .xls eller .xlsm-fil.

Figur A

Legg inn en liste over elementer i et dedikert ark.

Når du har lagt inn listen din, bruk et navneområde:

  1. Velg listen, inkludert overskriften (A1: A6).
  2. Klikk på Formler-fanen.
  3. I gruppen Definerte navn klikker du Opprett fra valg.
  4. Excel vil som standard velge Topprad i den resulterende dialogen ( figur B ). Klikk OK.
    Figur B

Nå er du klar til å legge til UserForm:

  1. Trykk Alt + F11 for å starte Visual Basic Editor (VBE).
  2. Velg UserForm fra Sett inn-menyen.
  3. Klikk på kombinasjonsboksen-kontrollen i verktøykassen, og klikk deretter på UserForm for å sette inn den nye kontrollen ( figur C ).
    Figur C
  4. Hvis Egenskapsvinduet er åpent, vil du se at det viser egenskapene til den nye kontrollen (det valgte objektet). Hvis dette vinduet ikke er synlig, trykker du på F4 eller velger Egenskaper-vindu fra Vis-menyen.
  5. Når kombinasjonsboksen fremdeles er valgt, finner du Row Source-egenskapen og skriver inn ClassRange ( figur D ). Det er navnet på området (listen) du opprettet tidligere (se figur B ).
    Figur D

Det er det! På dette tidspunktet er du klar til å teste den nye UserFormen ved å trykke på F5 eller klikke på Kjør makro-knappen på VBEs standardverktøylinje. Figur E viser kontrollen åpen for å vise listen. Det var enkelt og krevde ikke litt VBA.

Figur E

Excel fyller kontrollen ved å bruke dataene som er lagret i det angitte området ClassRange.

Tidligere nevnte jeg at selv om denne metoden sannsynligvis er den enkleste, er den minst fleksibel. Her er grunnen: Hvis du legger til et element nederst på listen, vises ikke det nye elementet i kontrolllisten med mindre du husker å oppdatere rekkevidden for å inkludere den nye cellen. Hvis du setter inn en rad og legger til elementet, vil Excel oppdatere rekkeviddenavnet slik at den inkluderer den nye raden, men du må fortsatt huske å gjøre det. I tillegg, hvis listen er i alfabetisk rekkefølge, må du huske å sortere den på nytt. Det er ikke en fryktelig løsning på noen måte, men å redusere nødvendig vedlikehold er et godt mål, som er et utmerket segment til neste metode - ved å bruke et tabellobjekt for å lagre listen.

Før vi går videre, vet jeg at noen av dere lurer på hvorfor jeg ikke foreslår en formel for dynamisk område ("Populere en kombinasjonsboks for brukerform i Excel med en dynamisk liste"). I de fleste situasjoner er en slik kompleks løsning ikke nødvendig - og den er min minst favoritt, fordi et dynamisk område kan være vanskelig å opprettholde i en distribuert arbeidsbok.

Bruk et tabellobjekt

Noen lister vil ikke være statiske. Du vil legge til og kanskje til og med slette elementer. Excel's Table-objekt utmerker seg med dette! (Tabellobjektet er nytt for Excel 2007, så det er ikke tilgjengelig i tidligere versjoner.) Prosessen er egentlig den samme som et navngitt område, men i stedet for et område bruker du en tabell og refererer deretter til tabellen som kontrollens radkilde eiendom. Som tidligere trenger du en liste, lik den som er vist i figur F.

Figur F

Legg inn listeelementene dine.

Med den opprinnelige listen på plass, er du klar til å konvertere den til et tabellobjekt:

  1. Klikk hvor som helst inne i listen.
  2. Klikk kategorien Sett inn.
  3. I tabellen-gruppen klikker du på Tabell ( figur G ).
    Figur G
  4. Standardinnstillingene er riktige, så klikk OK. Hvis listen din ikke har en overskrift, må du fjerne merkingen av dette alternativet. Excel oppretter Table-objektet og bruker standard tabellformater.

Excel tildeler et standardnavn til hver tabell. Du må vite dette navnet, så når tabellen er valgt, klikker du på Design kontekstuell fane. I Egenskaper-gruppen (til venstre) viser Excel tabellens standardnavn. Du kan bruke standardnavnet eller tilordne et tilpasset navn som følger:

  1. Klikk inne i tabellenavn-kontrollen for å velge navnet.
  2. Skriv det nye navnet ( figur H ).
    Figur H

Det er det!

Gå tilbake til VBE og legg til en andre kombinasjonsboks-kontroll i UserForm. Når den kontrollen er valgt, skriv ClassTable som den nye kontrollens Row Source-egenskapen, som vist i figur I.

Figur I

Angi kontrollens Row Source-egenskap.

Trykk på F5 for å kjøre UserForm. Figur J viser listen over den andre kontrollen.

Figur J

Den nye kontrollen viser den samme listen, men kilden er annerledes.

Så langt har prosessen vært nesten den samme som den første navngitte rekkeviddemetoden, men her er hvor de to metodene deler veier. Lukk UserForm og gå tilbake til Lists-arket. Der legger du til et nytt element nederst i tabellisten. Bare velg C6 og skriv Reptil. Hvis Excel ikke utvider tabellen til å inkludere det nye elementet, sletter du det. Velg deretter celle C5 og trykk Tab for å utvide tabellen til å omfatte C6 og angi Reptil, som vist i figur K.

Figur K

Legg til et nytt element i tabellen.

Gå tilbake til VBE og trykk F5 for å kjøre UserForm. Som du ser i figur L, inkluderer den nye listen Reptile. Du behøvde ikke å oppdatere et områdenavn eller sortere listen. Det fungerer bare.

Figur L

Den nye kontrollen oppdateres automatisk for å inkludere den nye varen, Reptile.

Ingen av disse metodene krever litt VBA-kode, men tabellmetoden er mer fleksibel enn avstandsnavnet metoden. Når du oppretter lister, må du huske på vedlikehold og brukervennlighet.

Send meg spørsmålet ditt om Office

Jeg svarer på lesernes spørsmål når jeg kan, men det er ingen garanti. Vær så spesifikk som mulig når du kontakter meg. For eksempel "Vennligst feilsøk arbeidsboka mine og fikse hva som er galt", vil sannsynligvis ikke få noe svar, men "Kan du fortelle meg hvorfor denne formelen ikke returnerer forventede resultater?" kanskje. Jeg får ikke refundert TechRepublic for min tid eller kompetanse, og ber heller ikke om et gebyr fra leserne. Du kan kontakte meg på

© Copyright 2020 | mobilegn.com