Excel - Dynamiske lister og userforms

[trn100]

Hallo.

 

Sitter og jobber med en applikasjon og trenger litt hjelp til VBA.

I flere userforms bruker jeg dynamiske lister. I disse userforms har jeg en "listbox" som viser alle verdiene i den dynamiske listen.

 

Fra userformen skal man kunne legge til, slette og endre informasjonen i listen.

 

Rutinen for å legge til nye fungerer helt greit!

 

Når det gjelder sletterutinen virker den også greit inntil jeg eventuelt sletter den siste raden. Da blir formelen i den dynamiske listen ødelagt? Formelen jeg bruker er:

"=FORSKYVNING(Hunderaser!$B$5;;;ANTALLA(Hunderaser!$B$5:$B$1000))"

Etter at jeg eventuelt sletter den siste posten i listen endder formelen med:

"=FORSKYVNING(Hunderaser!#REF!;;;ANTALLA(Hunderaser!$B$5:$B$1000))"

Jeg trenger derfor enten en sjekk i VBA som stopper med en feilmelding når noen forsøker å slette den siste posten i listen, eller en VBA kode som bygger listen på nytt? Det siste vil antagelig være det beste siden jeg ved sletting bruker ".EntireRow.Delete" noe som reduserer rangen hver gang den brukes.

Både for endring og sletting trenger også jeg en rutine som sjekker et annet ark med opptil flere tusen hunder og gir beskjed i en msgbox om hvor mange av hundene som er registrert med den aktuelle rasen slik at brukeren ikke uten videre kan slette/endre rasenavn som er i bruk?

 

Noen som har noen tips? :-)

[Harald Staff]

Hei

 

Det er veldig vanskelig å svare på dette uten regnearket og koden. Har du anledning til å laste opp fila? Det pleier å gå hvis du legger til .txt rett i bakkant av filnavnet.

 

Beste hilsen Harald

[trn100]

Hei Harald.

 

Har laget en .zip som inneholder både den gamle fila som skal erstattes med noe mer effektivt samt den spede begynnelsen på en ny.

 

I det gamle arket som jeg mer eller mindre har overtatt halvferdig skjer alt i arkene, noe som blir helt feil så mye data. I det nye er det meningen å foreta beregningen ved registrering i et skjema. Har også vurdert å legge både hunder og resultater i et og samme ark for å slippe oppslag og heller bruke noen avanserte filtre for å vise informasjon.

 

Det er også flere ark med resultater i form av pivot tabeller som er tenkt redusert til ett ark og bare bruke filtre/slicer for å vise resultater fra ulike konkurranser i samme ark.

Tok bare med det gamle for å vise hva jeg jobber med.

I det nye NKK arket har jeg overført litt data og begynt så vidt med skjemaene. Tanken er å gjøre ferdig et skjema 100% med all feilkontroll og i kopiere det meste til neste skjema.

Så til det jeg nevnte over. Jeg trenger egentlig bare en rutine som ved sletting av en rase sjekker om rasen er brukt i databasen og gi brukeren beskjed om at rasen ikke kan slettes uten å slette x antall hunder. Regner med at det er å bare lage en rutine som teller antall forekomster så det kunne jeg sikkert ha funnet ut. Det samme er det antagelig med problemet at den dynamiske listen "Raser" får formelen ødelagt om brukeren sletter alle rasene i listen.

Det aller største problemet mitt er imidlertid at jeg er usikker på hvordan jeg bør bygge opp databasen? Hvis du har noen tips omkring det hadde jeg vært veldig takknemlig! :-)

Utstilling-Jakt-2016-11-07-jakt.zip

[Harald Staff]

Hei igjen

 

Veldig fint arbeid dette. Og du er veldig nær.

 

Antall forekomster kan du for eksempel finne med en funksjon som denne -legg den for seg selv rett nedenfor prosedyren cmdSlett_Click() :

Private Function EriBruk(Rasenavn As String) As Long
EriBruk = Application.WorksheetFunction.CountIf(Sheets("Hunder").Range("D:D"), Rasenavn)
End Function

Så kaller du den slik, etter at du har sjekket om en rase er dobbelklikket:

AntallRaser = EriBruk(Me.txtRaser.Text)
If AntallRaser > 0 Then
    MsgBox "Beklager, " & AntallRaser & " hunder er registrert på " & Me.txtRaser.Text, , "Voff bjeff!!!"
    Exit Sub
End If

Jeg gjetter på at dette egentlig har virket hos deg også, men tilsynelatende ikke fordi det står "Flat Coated Retriever" i den ene lista og "Flatcoated Retriever" i den andre. Du kan unngå det ved å sette inn datavalidering mot liste Raser i arket Hunder kolonne D.

 

Når det gjelder sletting av rader og liste Raser, du kan definere navnet på nytt hver gang:

Dim N As Name
Dim RL As Long
On Error Resume Next
With ThisWorkbook.Sheets("Hunderaser")
    RL = .Cells(10000, 2).End(xlUp).Row
    Set N = ThisWorkbook.Names("Raser")
    N.RefersTo = .Range(.Cells(5, 2), .Cells(RL, 2))
End With

Et lite tips angående koding. Gå til tools > options i VBA-editoren og huk av for "Require variable declaration". Systemet setter da Option Explicit øverst i alle moduler og de blir mye mer byråkratiske mht variabler, men også veldig mye mer hjelpsomt når det dukker opp feil.

 

Igjen, dette var veldig fint jobba. Følg opp hvis jeg har vært uklar eller tenkt feil.

 

Beste hilsen Harald

 

EDIT: Lenke til datavalidering https://support.office.com/nb-no/article/Bruke-datavalidering-p%C3%A5-celler-29fecbcc-d1b9-42c1-9d76-eff3ce5f7249

Endret 18. november 2017 av Harald Staff

[trn100]

Hei Harald.

 

Beklager sen tilbakemelding, men har ikke hatt tid til å se så mye på dette før i dag.

 

Mange takk for kode og inspirerende tilbakemelding! 

 

Dataene i "Hunder" arket er bare kopiert over fra den gamle arbeidsboken hvor jeg hadde lagt inn datavalidering i flere av kolonnene. Problemet med det er at så lenge jeg ikke vet hvor mange hunder det kan bli etterhvert så måtte jeg legge inn valideringen i veldig mange rader, noe jeg innbiller meg er grunnen til at den gamle fila er stor og uhåndterlig. Derfor har jeg også begynt på skjemaet frmHunder hvor jeg vil bruke nettopp disse listene for Raser, Land, Kenneler osv. til validering i skjemaet i stede og la Hunder arket være en ren flat tabell.

Jeg har drevet med Excel i en del år, men som du skjønner er jeg ganske ny på VBA og setter stor pris på hjelpen! 

 

Nå forsøker jeg å implementere koden din, og håper jeg snart er klar med selve grunndatabasen!

 

Takk igjen for hjelp!