Er zijn meerdere comboboxen, wanneer ik nieuwe tekst hebt toegevoegd moet hij eigenlijk de comboboxen vernieuwen zodat ik dan ook de nieuwe tekst in de andere comboboxen terug zien/kiezen vinden.
de comboboxen worden geladen vanuit thisworkbook.
dit zijn de comboboxen (en meerdere) die na het opslaan of verwijderen opnieuw gevuld moeten worden.
de comboboxen worden geladen vanuit thisworkbook.
Code:
Private Sub Workbook_Open()
Dim Co1 As Long
Dim Co2 As Long
Dim Co3 As Long
Dim Co4 As Long
Application.DisplayAlerts = True
With GetObject("C:\Dropbox\documenten\Excel omschrijving.xlsm")
''With GetObject("C:\Users\jdukel\Downloads\Excel omschrijving.xlsm")
For Co1 = 1 To 21
Sheets("InvoerSheet").OLEObjects("ComboBox" & Co1).Object.List = .Sheets("Blad2").Range("a2:a500").Value
Next Co1
For Co2 = 21 To 90
Sheets("InvoerSheet").OLEObjects("ComboBox" & Co2).Object.List = .Sheets("Blad2").Range("c2:f300").Value
Next Co2
Sheets("InvoerSheet").ComboBox91.List = .Sheets("Blad2").Range("h2:h100").Value
Sheets("InvoerSheet").ComboBox92.List = .Sheets("Blad2").Range("j2:j30").Value
For Co3 = 1 To 100
Sheets("Rapportage").OLEObjects("ComboBox" & Co3).Object.List = .Sheets("Subonderwerp").Range("b2:b120").Value
Next Co3
For Co4 = 101 To 110
Sheets("Rapportage").OLEObjects("ComboBox" & Co4).Object.List = .Sheets("Onderwerp").Range("b2:b60").Value
Next Co4
UserForm2.ComboBox1.List = .Sheets("Blad2").Range("l2:l50").Value
.Close
End With
End Sub
dit zijn de comboboxen (en meerdere) die na het opslaan of verwijderen opnieuw gevuld moeten worden.
Code:
Private Sub ComboBox2_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Dim f As Range, Wb As Worksheet, msg As Long
msg = MsgBox("Ja is toevoegen, Nee is Verwijderen?", vbCritical + vbYesNoCancel)
Application.DisplayAlerts = False
If msg <> vbNo And msg <> vbYes Then
Exit Sub
Else: End If
With Workbooks.Open("C:\Dropbox\documenten\Excel omschrijving.xlsm").Sheets("blad2")
If msg = vbYes And ComboBox2.ListIndex = -1 Then
.Cells(Rows.Count, 1).End(xlUp).Offset(1) = ComboBox2.Value
.Range("A3:A" & .Cells(Rows.Count, 1).End(xlUp).Row).Sort .Range("A3")
ElseIf msg = vbNo Then
Set f = .Columns(1).Find(ComboBox2.Value, , , 1)
If Not f Is Nothing Then .Rows(f.Row).Delete
End If
.Parent.Close -1
Application.DisplayAlerts = True
End With
End Sub
Private Sub ComboBox3_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Dim f As Range, Wb As Worksheet, msg As Long
msg = MsgBox("Ja is toevoegen, Nee is Verwijderen?", vbCritical + vbYesNoCancel)
Application.DisplayAlerts = False
If msg <> vbNo And msg <> vbYes Then
Exit Sub
Else: End If
With Workbooks.Open("C:\Dropbox\documenten\Excel omschrijving.xlsm").Sheets("blad2")
If msg = vbYes And ComboBox3.ListIndex = -1 Then
.Cells(Rows.Count, 1).End(xlUp).Offset(1) = ComboBox3.Value
.Range("A3:A" & .Cells(Rows.Count, 1).End(xlUp).Row).Sort .Range("A3")
ElseIf msg = vbNo Then
Set f = .Columns(1).Find(ComboBox3.Value, , , 1)
If Not f Is Nothing Then .Rows(f.Row).Delete
Application.DisplayAlerts = False
End If
.Parent.Close -1
Application.DisplayAlerts = True
End With
End Sub