Dubbele waarden in comboboxen na aanpassen gegevens!

Kwabbernoot1980 · 6 feb 2012

Beste

ik zit weer met een probleempje in mijn programma. Ik wil gegevens vanuit een Excel werkblad laden en aanpassen. Dit lukt allemaal vlot, maar na een druk op de Commandbutton verdubbelt de inhoud van elke combobox. Heel vreemd natuurlijk, maar ik vind geen oplossing. Hopelijk kan er weer iemand onder jullie me geweldig helpen.

alvast dank bij voorbaat!

groetjes
Dave

Code:

Private Sub CB_Grp_Change()

Dim Nr       As Integer
  i = CB_Grp.ListIndex                                     'welke rij heb je gekozen, niks=-1, 1e rij=0, etc
  If i = -1 Then
    
    TextBox198.Value = ""
    ComboBox2 = ""
    TextBox169.Value = ""
    TextBox166.Value = ""
    TextBox165.Value = ""
    TextBox8.Value = ""
    TextBox9.Value = ""
    TextBox10.Value = ""
    TextBox11.Value = ""
    TextBox186.Value = ""
    TextBox185.Value = ""
    TextBox14.Value = ""
    TextBox168.Value = ""
    TextBox170.Value = ""
    TextBox167.Value = ""
    
    TextBox184.Value = ""
          
    TextBox193.Value = ""
    TextBox192.Value = ""
    TextBox194.Value = ""
    TextBox195.Value = ""
    TextBox196.Value = ""
    
    TextBox161.Value = ""
    ComboBox122.Value = ""
    ComboBox123.Value = ""
    ComboBox124.Value = ""
    ComboBox125.Value = ""
    ComboBox126.Value = ""
    TextBox129.Value = ""
    TextBox130.Value = ""
    
    DTPicker2.Value = "00:00"
    DTPicker3.Value = "00:00"
    DTPicker4.Value = "00:00"
                                              
              Else
    
    DTPicker1.Value = CB_Grp.List(i, 1)
    DTPicker2.Value = CB_Grp.List(i, 2)
    DTPicker3.Value = CB_Grp.List(i, 3)
    DTPicker4.Value = CB_Grp.List(i, 4)
    CheckBox68.Value = CB_Grp.List(i, 5) = "dag"
    CheckBox69.Value = CB_Grp.List(i, 6) = "avond"
    ComboBox2.Value = CB_Grp.List(i, 7)
    TextBox166.Value = CB_Grp.List(i, 8)
    TextBox165.Value = CB_Grp.List(i, 9)
    ComboBox128.Value = CB_Grp.List(i, 10)
    TextBox198.Value = CB_Grp.List(i, 11)
    CheckBox63.Value = CB_Grp.List(i, 12) = "intra muros"
    CheckBox64.Value = CB_Grp.List(i, 13) = "extra muros"
    CheckBox65.Value = CB_Grp.List(i, 14) = "in openlucht"
    TextBox137.Value = CB_Grp.List(i, 15)
    ComboBox127.Value = CB_Grp.List(i, 16)
    TextBox108.Value = CB_Grp.List(i, 17)
    CheckBox66.Value = CB_Grp.List(i, 18) = "exact"
    TextBox129.Value = CB_Grp.List(i, 19)
    CheckBox67.Value = CB_Grp.List(i, 20) = "geraamd"
    TextBox130.Value = CB_Grp.List(i, 21)
    
    CheckBox34.Value = CB_Grp.List(i, 23) = "Laptop"
    CheckBox9.Value = CB_Grp.List(i, 24) = "Beamer"
    CheckBox13.Value = CB_Grp.List(i, 25) = "Internet"
    CheckBox10.Value = CB_Grp.List(i, 26) = "Projectiescherm"
    CheckBox12.Value = CB_Grp.List(i, 27) = "Geluidsversterking"
    CheckBox81.Value = CB_Grp.List(i, 28) = "Extra stoelen"
    TextBox134.Value = CB_Grp.List(i, 29)
    CheckBox33.Value = CB_Grp.List(i, 30) = "Catering via refter"
    TextBox97.Value = CB_Grp.List(i, 31)
    CheckBox38.Value = CB_Grp.List(i, 32) = "Catering via cas"
    
    TextBox161.Value = CB_Grp.List(i, 34)
    ComboBox122.Value = CB_Grp.List(i, 35)
    ComboBox123.Value = CB_Grp.List(i, 36)
    ComboBox124.Value = CB_Grp.List(i, 37)
    ComboBox125.Value = CB_Grp.List(i, 38)
    
    CheckBox71.Value = CB_Grp.List(i, 40) = "families"
    CheckBox83.Value = CB_Grp.List(i, 41) = "jongeren"
    CheckBox82.Value = CB_Grp.List(i, 42) = "senioren"
    CheckBox70.Value = CB_Grp.List(i, 43) = "scholen"
    
    CheckBox74.Value = CB_Grp.List(i, 45) = "lokale vereniging"
    CheckBox75.Value = CB_Grp.List(i, 46) = "bibliotheek of cultuurantenne"
    CheckBox76.Value = CB_Grp.List(i, 47) = "actor(en) binnen de gemeente"
    CheckBox77.Value = CB_Grp.List(i, 48) = "andere stedelijke actoren"
    CheckBox79.Value = CB_Grp.List(i, 49) = "cca-projecten"
    
    CheckBox86.Value = CB_Grp.List(i, 51) = "festival, wedstrijd"
    CheckBox85.Value = CB_Grp.List(i, 52) = "landelijk cultureel event"
    
    CheckBox44.Value = CB_Grp.List(i, 54) = "lokale vereniging"
    CheckBox45.Value = CB_Grp.List(i, 55) = "cultuurbeleidscoördinator"
     
    CheckBox46.Value = CB_Grp.List(i, 57) = "organisatorisch"
    CheckBox87.Value = CB_Grp.List(i, 58) = "promotioneel"
    
    ComboBox126.Value = CB_Grp.List(i, 60)
    
    TextBox169.Value = CB_Grp.List(i, 64)
    TextBox168.Value = CB_Grp.List(i, 65)
    TextBox170.Value = CB_Grp.List(i, 66)
    TextBox167.Value = CB_Grp.List(i, 67)
    TextBox8.Value = CB_Grp.List(i, 68)
    TextBox9.Value = CB_Grp.List(i, 69)
    TextBox10.Value = CB_Grp.List(i, 70)
    TextBox11.Value = CB_Grp.List(i, 71)
    TextBox186.Value = CB_Grp.List(i, 72)
    TextBox185.Value = CB_Grp.List(i, 73)
    TextBox14.Value = CB_Grp.List(i, 74)
    TextBox184.Value = CB_Grp.List(i, 75)
    TextBox193.Value = CB_Grp.List(i, 76)
    TextBox192.Value = CB_Grp.List(i, 77)
    TextBox194.Value = CB_Grp.List(i, 78)
    TextBox195.Value = CB_Grp.List(i, 79)
    TextBox196.Value = CB_Grp.List(i, 80)
        
               End If
                                       
End Sub

Code:

Private Sub UserForm_Initialize()
     
  With Sheets("NAWlijst")
    sq = .Range(Bereik).Resize(, 200)                        'lees gegevens in array
    CB_Grp.List = sq                                       'vul je combobox
    
    CB_Grp = ""                                            'alles leegmaken
    ComboBox2.Value = ""
    TextBox198.Value = ""
    ComboBox127.Value = ""
    TextBox169.Value = ""
    TextBox166.Value = ""
    TextBox165.Value = ""
    TextBox8.Value = ""
    TextBox9.Value = ""
    TextBox10.Value = ""
    TextBox11.Value = ""
    TextBox186.Value = ""
    TextBox185.Value = ""
    TextBox14.Value = ""
    TextBox168.Value = ""
    TextBox170.Value = ""
    TextBox167.Value = ""
                    
    TextBox184.Value = ""
           
    TextBox193.Value = ""
    TextBox192.Value = ""
    TextBox194.Value = ""
    TextBox195.Value = ""
    TextBox196.Value = ""
                                   
    TextBox161.Value = ""
    ComboBox122.Value = ""
    ComboBox123.Value = ""
    ComboBox124.Value = ""
    ComboBox125.Value = ""
    ComboBox126.Value = ""
    TextBox129.Value = ""
    TextBox130.Value = ""
    
    DTPicker1.Value = Date
                         
             End With

With ComboBox122
.AddItem "Eigen_activiteiten"
.AddItem "Receptieve_activiteiten"
.AddItem "Educatieve_activiteiten"
.AddItem ""

End With

 With ComboBox2
.AddItem "Intern"
.AddItem "Extern"
.AddItem "Extern wijk"
.AddItem "Stad"
.AddItem "BGS"
End With

With ComboBox127
.AddItem "1 dagdeel"
.AddItem "2 dagdelen"
.AddItem "3 dagdelen"
.AddItem ""

End With


With ComboBox117
.AddItem "B3 Taunus - CAT I - 1 dagdeel"
.AddItem "B3 Taunus - CAT I - 2 dagdelen"
.AddItem "B3 Taunus - CAT I - 3 dagdelen"
.AddItem "B3 Taunus - CAT II - 1 dagdeel"
.AddItem "B3 Taunus - CAT II - 2 dagdelen"
.AddItem "B3 Taunus - CAT II - 3 dagdelen"
.AddItem "C1 Consul - CAT I - 1 dagdeel"
.AddItem "C1 Consul - CAT I - 2 dagdelen"
.AddItem "C1 Consul - CAT I - 3 dagdelen"
.AddItem "C1 Consul - CAT II - 1 dagdeel"
.AddItem "C1 Consul - CAT II - 2 dagdelen"
.AddItem "C1 Consul - CAT II - 3 dagdelen"
.AddItem "E0 Capri - CAT I - 1 dagdeel"
.AddItem "E0 Capri - CAT I - 2 dagdelen"
.AddItem "E0 Capri - CAT I - 3 dagdelen"
.AddItem "E0 Capri - CAT II - 1 dagdeel"
.AddItem "E0 Capri - CAT II - 2 dagdelen"
.AddItem "E0 Capri - CAT II - 3 dagdelen"
.AddItem "E1 Cortina - CAT I - 1 dagdeel"
.AddItem "E1 Cortina - CAT I - 2 dagdelen"
.AddItem "E1 Cortina - CAT I - 3 dagdelen"
.AddItem "E1 Cortina - CAT II - 1 dagdeel"
.AddItem "E1 Cortina - CAT II - 2 dagdelen"
.AddItem "E1 Cortina - CAT II - 3 dagdelen"
.AddItem "F1 Leslokaal - CAT I - 1 dagdeel"
.AddItem "F1 Leslokaal - CAT I - 2 dagdelen"
.AddItem "F1 Leslokaal - CAT I - 3 dagdelen"
.AddItem "F1 Leslokaal - CAT II - 1 dagdeel"
.AddItem "F1 Leslokaal - CAT II - 2 dagdelen"
.AddItem "F1 Leslokaal - CAT II - 3 dagdelen"
.AddItem "Auditorium - CAT I - 1 dagdeel"
.AddItem "Auditorium - CAT I - 2 dagdelen"
.AddItem "Auditorium - CAT I - 3 dagdelen"
.AddItem "Auditorium - CAT II - 1 dagdeel"
.AddItem "Auditorium - CAT II - 2 dagdelen"
.AddItem "Auditorium - CAT II - 3 dagdelen"
.AddItem "Foyer - CAT I - 1 dagdeel"
.AddItem "Foyer - CAT I - 2 dagdelen"
.AddItem "Foyer - CAT I - 3 dagdelen"
.AddItem "Foyer - CAT II - 1 dagdeel"
.AddItem "Foyer - CAT II - 2 dagdelen"
.AddItem "Foyer - CAT II - 3 dagdelen"
.AddItem ""

End With

With ComboBox128
.AddItem "CAT I"
.AddItem "CAT II"
.AddItem "CAT III"
.AddItem "GRATIS RESERVATIE"
.AddItem ""

End With

With ComboBox125
.AddItem "Professionele uitvoerders"
.AddItem "Amateurkunstenaars"
.AddItem ""
End With

With ComboBox126
.AddItem "GEBOEKT"
.AddItem "GEANNULEERD"
.AddItem ""

End With

Label260.Font.Bold = True
Label261.Font.Bold = True
Label265.Font.Bold = True
Label267.Font.Bold = True
Label276.Font.Bold = True
Label220.Font.Bold = True
Label221.Font.Bold = True
Label357.Font.Bold = True
Label303.Font.Bold = True

 End Sub

Code:

Private Sub CommandButton15_Click()

Dim Nr       As Integer
  i = CB_Grp.ListIndex
  If i = -1 Then
    MsgBox "er is niets gekozen, dus kan er niets weggeschreven worden"
  Else
    With Sheets("NAWlijst").Range(Bereik)
                                                      'gegevens wegschrijven
      .Cells(i + 1, 2) = DTPicker1
      .Cells(i + 1, 8) = ComboBox2.Value
      .Cells(i + 1, 11) = ComboBox128.Value
      .Cells(i + 1, 12) = TextBox198.Value
      .Cells(i + 1, 17) = ComboBox127.Value
      .Cells(i + 1, 61) = ComboBox126.Value
      
    End With
  End If
  UserForm_Initialize 'nieuwe waarden weer ophalen

End Sub

wampier · 6 feb 2012

Dat klopt toch ook? je roept userform_initialize weer opnieuw aan, das additem wordt ook weer opnieuw uitgevoerd.

Het beste is het "schoonmaken" in een aparte procedure te stoppen. Je kunt dan het schoonmaak deel oproepen in initialise en los.

snb · 6 feb 2012

Gebruik daarom nooit additem, maar .list
bijv.

Code:

ComboBox125.list=split("Professionele uitvoerders|Amateurkunstenaars|","|")

Kwabbernoot1980 · 7 feb 2012

Mensen jullie zijn fantastisch :thumb:
Stom dat ik het niet zelf had gezien, want eingelijk een zeer logische oplossing. Ik keek er over... en als je het niet ziet!

groetjes
Dave

Dubbele waarden in comboboxen na aanpassen gegevens!

Kwabbernoot1980

Gebruiker

wampier

Meubilair

snb

Verenigingslid

Kwabbernoot1980

Gebruiker

Nieuwste berichten

Wij waarderen jouw privacy