gegevens listbox valideren

276159 · 10 mrt 2009

Ik zit met het volgende probleempje

Ik haal dmv een een code gegevens uit bepaalde bestanden. Deze code kan iedere keer andere gegevens bevatten, dus typ ik de gegevens in een tekstbox. Maar dit is zoveel typwerk iedere week dat ik een listbox heb gemaakt waar ik de gegevens aan kan klikken en deze dan in de formule gezet worden.

Nu komt het probleem, ik weet niet precies hoe ik deze gegevens zeg maar kan 'valideren' & tevens ook meerdere zalen uit een dag kan halen (dus listbox 3 moet meerdere gegevens kunnen valideren en dan dmv een loop macro de bijbehorende gegevens uit de bestanden halen).

Bijgevoegd is het betreffende bestand waar ik in vast zit.

Cobbe · 10 mrt 2009

Zet de eigenschappen van Listbox3 op Multiselect.
Dan kan je meerdere selectie in een handeling gebruiken.

Cobbe

276159 · 10 mrt 2009

Stom, ik allemaal zoeken naar die funcite

Nu loop ik alleen nog tegen het valideren van de gegevens, en de loop macro aan.

snb · 10 mrt 2009

In de bijlage in het userform de in Excel ingebouwde kalender.
Tevens een vereenvoudiging van de combobox zalen.

276159 · 10 mrt 2009

snb, bedankt voor je hulp. Ziet er stukken beter uit

Alleen moet de maand en de dag apart gebruikt worden in de macro.

Alleen hoe kan ik de gegevens die ik in het userform gebruik valideren in de macro

Met andere woorden:
Listbox1; de waarde moet komen te staan in de code als maand
Listbox2; de waarde moet komen te staan als de datum
Listbox3; de waarde is de zaal waaruit de gegevens opgevraagd worden

Ik neem aan dat in mijn code de waarde's maand = listbox1.value niet juist is, maar hoe valideer ik de gegevens dan wel.

Sorry voor alle lastige vragen, maar het zou mijn werk een stuk makkelijker maken

PHP:

    Dim Dag As String
    Dim Zaal As String
    Dim Maand As String
    
    UserForm1.Show
    
    Maand = ListBox1.Value
    Dag = ListBox2.Value
        
    ChDir "G:\DPT-SB\BANQUET SALES\After-Sales\2009"
    Workbooks.Open Filename:= _
        "G:\DPT-SB\BANQUET SALES\After-Sales\2009\" & Maand & "\" & Dag & ".xls", UpdateLinks:=3
    
   Zaal = ListBox3.Value
    
    Workbooks("Top-Booker.xls").Activate
    Sheets("Totaal").Copy After:=Sheets(1)
    Sheets("Totaal (2)").Name = Zaal
        
        Range("C2").FormulaR1C1 = _
            "=IF([" & Dag & ".xls]" & Zaal & "!R2C2=RC[-1],[" & Dag & ".xls]" & Zaal & "!R7C18,0)"
        Range("C2").AutoFill Destination:=Range("C2:C500"), Type:=xlFillDefault
    
        Range("D2").FormulaR1C1 = _
            "=IF([" & Dag & ".xls]" & Zaal & "!R2C2=RC[-2],[" & Dag & ".xls]" & Zaal & "!R1C19,0)"
        Range("D2").AutoFill Destination:=Range("D2:D500"), Type:=xlFillDefault
    
        Range("E2").FormulaR1C1 = _
            "=IF([" & Dag & ".xls]" & Zaal & "!R2C2=RC[-3],[" & Dag & ".xls]" & Zaal & "!R2C19,0)"
        Range("E2").AutoFill Destination:=Range("E2:E500"), Type:=xlFillDefault
            
        Range("F2").FormulaR1C1 = _
            "=IF([" & Dag & ".xls]" & Zaal & "!R2C2=RC[-4],[" & Dag & ".xls]" & Zaal & "!R3C19,0)"
        Range("F2").AutoFill Destination:=Range("F2:F500"), Type:=xlFillDefault
           
        Range("G2").FormulaR1C1 = _
            "=IF([" & Dag & ".xls]" & Zaal & "!R2C2=RC[-5],[" & Dag & ".xls]" & Zaal & "!R4C19,0)"
        Range("G2").AutoFill Destination:=Range("G2:G500"), Type:=xlFillDefault
         
        Range("H2").FormulaR1C1 = _
            "=IF([" & Dag & ".xls]" & Zaal & "!R2C2=RC[-6],[" & Dag & ".xls]" & Zaal & "!R6C18,0)"
        Range("H2").AutoFill Destination:=Range("H2:H500"), Type:=xlFillDefault
          
    Columns("A:H").Sort Key1:=Range("C2"), Order1:=xlDescending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal
    Rows("3:500").Delete Shift:=xlUp
          
    Windows(Dag & ".xls").Close (Savechanges = wdDoNotSaveChanges)

snb · 10 mrt 2009

Alleen moet de maand en de dag apart gebruikt worden in de macro

dat kan met format(datum.value,"mmmm") en format(datum.value,"dd")

Zet de volgende code achter de OK-knop vna het userform.

Code:

Private Sub OKButton_Click()
    UserForm1.Hide
    
    With Workbooks("Top-Booker.xls").Sheets.Add
        .Name = Zaal.Value
        Sheets("totaal").Rows(1).Copy .[A1]
    End With
End Sub

De rol van

Code:

    Workbooks.Open "G:\DPT-SB\BANQUET SALES\After-Sales\2009\" & Replace(Format(datum.Value, "mmmm \ dd"), " ", "\") & ".xls", UpdateLinks:=3

is mij onduidelijk.

De code die je in je bericht plaatste kan dan vervallen.
Die plaatst overigens allerlei formules die later weer integraal worden verwijderd.
Beschrijf liever eens wat er in het nieuwe werkblad voor deze zaal moet komen te staan.

276159 · 11 mrt 2009

De code werkt als volgt (tenminste zo zou hij moeten gaan werken).

Bij het starten van de macro verschijnt de userform waarin je de gegevens van maand, dag & zaal aan kan klikken.

Deze gegevens gebruikt hij verder in de macro. De maand en dag gebruikt hij om een bepaald bestand te openen die op een andere locatie staat.

code:

Code:

    ChDir "G:\DPT-SB\BANQUET SALES\After-Sales\2009" 
    Workbooks.Open Filename:= _ 
        "G:\DPT-SB\BANQUET SALES\After-Sales\2009\" & Maand & "" & Dag & ".xls", UpdateLinks:=3

Uit dit bestand gaat hij dus de gegevens halen. Hierin vullen wij omzetten in, die bij verschillende zalen horen (wat tot 20 zalen per dag kan zijn).

Om iedere dag, handmatig 20 keer eenzelfde handeling te gebruiken (maand & datum typen + zaalnaam wat correspondeert met een werkblad) is veel werk en zeer tijdrovend. Daarom was ik op zoek gegaan naar een mogelijkheid om dmv een userform 1 datum en meerdere zalen tegelijk aan te kunnen wijzen waaruit gegevens moeten gehaald worden. Dit zou mij de handeling van 20 keer een andere zaal typen vereenvoudigen.

De code werkt wel op deze manier. Alleen moet ik hier dus in tekstboxen zelf de maand, datum en zaalnaam/werkbladnaam typen en deze handeling 20 keer herhalen.

Code:

Sub DatabaseZaal()
    
    Dim Dag As String
    Dim Zaal As String
    Dim Maand As String
    
    Maand = InputBox("Welke maand moet er geopend worden?")
    Dag = InputBox("Welke dag moet er geopend worden?")
        
    ChDir "G:\DPT-SB\BANQUET SALES\After-Sales\2009"
    Workbooks.Open Filename:= _
        "G:\DPT-SB\BANQUET SALES\After-Sales\2009\" & Maand & "\" & Dag & ".xls", UpdateLinks:=3
    
    Zaal = InputBox("Naam van de Zaal")
    
    Workbooks("Top-Booker.xls").Activate
    Sheets("Totaal").Copy After:=Sheets(1)
    Sheets("Totaal (2)").Name = Zaal
        
        Range("C2").FormulaR1C1 = _
            "=IF([" & Dag & ".xls]" & Zaal & "!R2C2=RC[-1],[" & Dag & ".xls]" & Zaal & "!R7C18,0)"
        Range("C2").AutoFill Destination:=Range("C2:C500"), Type:=xlFillDefault
    
        Range("D2").FormulaR1C1 = _
            "=IF([" & Dag & ".xls]" & Zaal & "!R2C2=RC[-2],[" & Dag & ".xls]" & Zaal & "!R1C19,0)"
        Range("D2").AutoFill Destination:=Range("D2:D500"), Type:=xlFillDefault
    
        Range("E2").FormulaR1C1 = _
            "=IF([" & Dag & ".xls]" & Zaal & "!R2C2=RC[-3],[" & Dag & ".xls]" & Zaal & "!R2C19,0)"
        Range("E2").AutoFill Destination:=Range("E2:E500"), Type:=xlFillDefault
            
        Range("F2").FormulaR1C1 = _
            "=IF([" & Dag & ".xls]" & Zaal & "!R2C2=RC[-4],[" & Dag & ".xls]" & Zaal & "!R3C19,0)"
        Range("F2").AutoFill Destination:=Range("F2:F500"), Type:=xlFillDefault
           
        Range("G2").FormulaR1C1 = _
            "=IF([" & Dag & ".xls]" & Zaal & "!R2C2=RC[-5],[" & Dag & ".xls]" & Zaal & "!R4C19,0)"
        Range("G2").AutoFill Destination:=Range("G2:G500"), Type:=xlFillDefault
         
        Range("H2").FormulaR1C1 = _
            "=IF([" & Dag & ".xls]" & Zaal & "!R2C2=RC[-6],[" & Dag & ".xls]" & Zaal & "!R6C18,0)"
        Range("H2").AutoFill Destination:=Range("H2:H500"), Type:=xlFillDefault
          
    Columns("A:H").Sort Key1:=Range("C2"), Order1:=xlDescending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal
    Rows("3:500").Delete Shift:=xlUp
          
    Windows(Dag & ".xls").Close (Savechanges = wdDoNotSaveChanges)
    
End Sub

Ik was dus op zoek gegaan naar de mogelijkheid om de inputboxen te vervangen door een listbox (userform) waarin ik de gegevens aanklik. Alleen krijg ik de aangeklikte gegevens niet verwerkt in mijn macro.

snb zei:
De code die je in je bericht plaatste kan dan vervallen.
Die plaatst overigens allerlei formules die later weer integraal worden verwijderd.
Beschrijf liever eens wat er in het nieuwe werkblad voor deze zaal moet komen te staan.

Wat er dus uiteindelijk na het afronden van de macro staat, is een werkblad met de corresponderende zaal. de gast die in deze zaal heeft gezeten en de bijbehorende omzetgroepen. Uiteindelijk kan ik deze gegevens gebruiken om aan het einde van de maand te zien, welk bedrijf de meeste omzet bij ons neerzet.

Hopelijk is het verhaal een beetje duidelijk, want is lastig om te omschrijven wat er precies gebeurd.

276159 · 11 mrt 2009

Eventjes een update:

Het is nu gelukt op de gegevens uit een listbox werkend te krijgen, en de benodigde info uit het bestand te halen. Nu het laatste nog, en dit zal wel een redelijk eenvoudige oplossing zijn.

Door de code achter de OK knop in de userform te plaatsen werkt het wel. Hele simpele oplossing eigenlijk, al heeft SNB we een redelijke eind op weg geholpen.

Nu wil ik graag dat de gegevens uit Listbox3, waar je meedere optie's kan selecteren, als een loop functie gebruikt wordt.

Code:

Private Sub OKButton_Click()
    
    UserForm1.Hide
    
    Dim Dag As String
    Dim Zaal As String
    Dim Maand As String
    
    Maand = ListBox1.Value
    Dag = ListBox2.Value
        
    ChDir "G:\DPT-SB\BANQUET SALES\After-Sales\2009"
    Workbooks.Open Filename:= _
        "G:\DPT-SB\BANQUET SALES\After-Sales\2009\" & Maand & "\" & Dag & ".xls", UpdateLinks:=3
    
    Zaal = ListBox3.Value
    
    Workbooks("Copy of Top-Booker.xls").Activate
    Sheets("Totaal").Copy After:=Sheets(1)
    Sheets("Totaal (2)").Name = Zaal
        
        Range("C2").FormulaR1C1 = _
            "=IF([" & Dag & ".xls]" & Zaal & "!R2C2=RC[-1],[" & Dag & ".xls]" & Zaal & "!R7C18,0)"
        Range("C2").AutoFill Destination:=Range("C2:C500"), Type:=xlFillDefault
    
        Range("D2").FormulaR1C1 = _
            "=IF([" & Dag & ".xls]" & Zaal & "!R2C2=RC[-2],[" & Dag & ".xls]" & Zaal & "!R1C19,0)"
        Range("D2").AutoFill Destination:=Range("D2:D500"), Type:=xlFillDefault
    
        Range("E2").FormulaR1C1 = _
            "=IF([" & Dag & ".xls]" & Zaal & "!R2C2=RC[-3],[" & Dag & ".xls]" & Zaal & "!R2C19,0)"
        Range("E2").AutoFill Destination:=Range("E2:E500"), Type:=xlFillDefault
            
        Range("F2").FormulaR1C1 = _
            "=IF([" & Dag & ".xls]" & Zaal & "!R2C2=RC[-4],[" & Dag & ".xls]" & Zaal & "!R3C19,0)"
        Range("F2").AutoFill Destination:=Range("F2:F500"), Type:=xlFillDefault
           
        Range("G2").FormulaR1C1 = _
            "=IF([" & Dag & ".xls]" & Zaal & "!R2C2=RC[-5],[" & Dag & ".xls]" & Zaal & "!R4C19,0)"
        Range("G2").AutoFill Destination:=Range("G2:G500"), Type:=xlFillDefault
         
        Range("H2").FormulaR1C1 = _
            "=IF([" & Dag & ".xls]" & Zaal & "!R2C2=RC[-6],[" & Dag & ".xls]" & Zaal & "!R6C18,0)"
        Range("H2").AutoFill Destination:=Range("H2:H500"), Type:=xlFillDefault
          
    Columns("A:H").Sort Key1:=Range("C2"), Order1:=xlDescending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal
    Rows("3:500").Delete Shift:=xlUp
          
    Windows(Dag & ".xls").Close (Savechanges = wdDoNotSaveChanges)
    

End Sub

Vanaf het stukje
Zaal = Listbox3.value tot Rows("3:500").Delete Shift:=xlUp moet wederkerend gebruikt worden. Dus eerst gebruikt hij een keuze uit listbox3, waarna hij de macro afwerkt, en terug gaat naar zaal = listbox3.value (2e keuze) etc...

Nu heb ik wel het een en ander gelezen over loop functie's in macro, maar krijg dat hierop niet toegepast. Iemand die me zou kunnen hulpen?

276159 · 12 mrt 2009

Het is gelukt. Na wat speurwerk, en veel keer foutmeldingen te krijgen op mijn macro, is het gelukt om de loop in mijn macro te krijgen, zodat ik meerdere zalen kan selecteren en hij dit dmv een loop uitvoert

Hieronder de code: (vraag is opgelost hiermee)

Wat doet de code in stappen:

- Opent mijn userform (te zien in TS)
- Ik selecteer de maand en de dag in listbox1 & listbox2
- Ik selecteer de zalen uit welke mijn gegevens gehaald moeten worden (listbox3 - multiselect)
- daarna opent hij aan de hand van listbox1 & listbox2 het bestand waarin hij moet gaan zoeken.
- maakt een nieuwe sheet aan in bronbestand
- aan de hand van het 1e geselecteerde in listbox3 opent hij een werkblad en kopieert gegevens in mijn nieuwe werkblad
- sorteert mijn nieuwe werkblad en verwijdert alles waar geen gegevens bij staan
- loopt naar 2e geselecteerde zaal in listbox3 etc.
- sluit mijn geopende bestand af zonder op te slaan.

Na afloop van de macro heb ik dus in nieuwe werkbladen staan bij welke zaal, welke gast, welke omzet had. Dit kan ik dan in een ander bestand weer opvragen en zo aan het einde van de maand/week of jaar zien wie het meeste bij ons boekt.

Code:

Private Sub OKButton_Click()
    
    UserForm1.Hide
    
    Dim Dag As String
    Dim i As Integer
    Dim Maand As String
    Dim Zaal As String
    
    Maand = ListBox1.Value
    Dag = ListBox2.Value
        
    ChDir "G:\DPT-SB\BANQUET SALES\After-Sales\2009"
    Workbooks.Open Filename:= _
        "G:\DPT-SB\BANQUET SALES\After-Sales\2009\" & Maand & "\" & Dag & ".xls", UpdateLinks:=3
    
    For i = 0 To ListBox3.ListCount - 1
    If ListBox3.Selected(i) Then
    Zaal = ListBox3.List(i)
              
    Workbooks("Copy of Top-Booker.xls").Activate
    Sheets("Totaal").Copy After:=Sheets(1)
    Sheets("Totaal (2)").Name = Zaal
        
        Range("C2").FormulaR1C1 = _
            "=IF([" & Dag & ".xls]" & Zaal & "!R2C2=RC[-1],[" & Dag & ".xls]" & Zaal & "!R7C18,0)"
        Range("C2").AutoFill Destination:=Range("C2:C500"), Type:=xlFillDefault
    
        Range("D2").FormulaR1C1 = _
            "=IF([" & Dag & ".xls]" & Zaal & "!R2C2=RC[-2],[" & Dag & ".xls]" & Zaal & "!R1C19,0)"
        Range("D2").AutoFill Destination:=Range("D2:D500"), Type:=xlFillDefault
    
        Range("E2").FormulaR1C1 = _
            "=IF([" & Dag & ".xls]" & Zaal & "!R2C2=RC[-3],[" & Dag & ".xls]" & Zaal & "!R2C19,0)"
        Range("E2").AutoFill Destination:=Range("E2:E500"), Type:=xlFillDefault
            
        Range("F2").FormulaR1C1 = _
            "=IF([" & Dag & ".xls]" & Zaal & "!R2C2=RC[-4],[" & Dag & ".xls]" & Zaal & "!R3C19,0)"
        Range("F2").AutoFill Destination:=Range("F2:F500"), Type:=xlFillDefault
           
        Range("G2").FormulaR1C1 = _
            "=IF([" & Dag & ".xls]" & Zaal & "!R2C2=RC[-5],[" & Dag & ".xls]" & Zaal & "!R4C19,0)"
        Range("G2").AutoFill Destination:=Range("G2:G500"), Type:=xlFillDefault
         
        Range("H2").FormulaR1C1 = _
            "=IF([" & Dag & ".xls]" & Zaal & "!R2C2=RC[-6],[" & Dag & ".xls]" & Zaal & "!R6C18,0)"
        Range("H2").AutoFill Destination:=Range("H2:H500"), Type:=xlFillDefault
          
    Columns("A:H").Sort Key1:=Range("C2"), Order1:=xlDescending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal
    Rows("3:500").Delete Shift:=xlUp
    
    End If
    Next i
          
    Windows(Dag & ".xls").Close (Savechanges = wdDoNotSaveChanges)
    
End Sub

gegevens listbox valideren

276159

Gebruiker

Bijlagen

Cobbe

Giga Honourable Senior Member †

276159

Gebruiker

snb

Verenigingslid

Bijlagen

276159

Gebruiker

snb

Verenigingslid

276159

Gebruiker

276159

Gebruiker

276159

Gebruiker

Nieuwste berichten

Wij waarderen jouw privacy