Lege cellen verwijderen uit listbox

[Rso]

Ik zit met een probleem dat ik zelf niet opgelost krijg.

Ik heb een kolom waar kabelnummers in gezet worden. Deze kolom wordt dus gedurende het project geupdate en aangepast. Het bereik is in principe niet vooraf bepaald. Ik wil in mijn listbox (met gegevens validatie) alleen de cellen zien binnen het bereik die zijn ingevuld. Er is een optie waar je dat kan aanduiden dat hij enkel de ingevulde boxen moet laten doen, ik heb al gemerkt dat dat niet werkt.
Ik stel mijn bereik dus in van A8:A1000 en ik vul wat waardes in. ik laat wat cellen open, vul dan weer een waarde in. Als ik dan de listbox open klik zie ik alle lege cellen die er tussen staan.

kan iemand mij hierbij helpen?
Bekijk bijlage Voorbeeld listbox.xlsx

 

[loek010]

Met een ActiveX combobox is dit de bedoeling

 

[Rso]

Beste.

Bedankt voor uw antwoord

Ik nu zijn de lege cellen inderdaad verwijderd maar als ik de lijst verder aanvul komt het niet in de listbox. Ik heb twee kabelnummers bijgevoegd en ze komen er niet bij.

 

[loek010]

Beste Rso

Misschien dat het zo beter werkt nu past hij het wel aan.

 

[Rso]

Beste.

Bedankt voor uw antwoord.

Dit is helemaal wat ik bedoel.

Ik weet alleen niet hoe ik het moet klaarspelen, kan u eens uitleggen hoe u dit gedaan heeft?
zo'n combobox plaatsen en dan?

 

[SjonR]

Loek010(Willem) heeft een stukje code achter Blad1 gezet:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With CreateObject("System.Collections.ArrayList")
        For Each cl In Blad1.Range("A1:A10050")
            If Trim(cl) <> "" And Not .contains(cl.Value) Then .Add Trim(cl)
            Next cl
            '.Sort
            Blad1.ComboBox1.List = .ToArray()
        End With
End Sub

@Loek010

Waarom een SelectionChange en geen Change?

 

[loek010]

Rso

Hierbij enige uitleg, Open in het Lint het tabblad Ontwikkelaars kies dan invoegen, kies in ActiveX besturingselementen : keuzelijst met invoervak
en plaats de keuzelijst op het werkblad.

Open de visueel basic editor met de toetsen Alt F11 klik daarna met je rechtermuisknop op Blad1 en selecteer programmacode weergeven
Kopieer daar de onderstaande code in:

Private Sub Worksheet_Change(ByVal Target As Range)
With CreateObject("System.Collections.ArrayList")
        For Each cl In Blad1.Range("A1:A10050")
            If Trim(cl) <> "" And Not .contains(cl.Value) Then .Add Trim(cl)
            Next cl
            ComboBox1.List = .ToArray()
        End With
End Sub

Doe dit hetzelfde met Thisworkbook

Private Sub Workbook_Open()
With CreateObject("System.Collections.ArrayList")
        For Each cl In Blad1.Range("A1:A10050")
            If Trim(cl) <> "" And Not .contains(cl.Value) Then .Add Trim(cl)
            Next cl           
            Blad1.ComboBox1.List = .ToArray()
        End With
End Sub

 

[loek010]

@SjonR

Inderdaad het kan ook met Private Sub Worksheet_Change(ByVal Target As Range)
had code in me vba kastje zitten en niet verder gekeken dat het ook met Change ook mogelijk was.

Maar je hebt gelijk

 

[snb]

Omdat deze gebeurteniscode alleen geaktiveerd wordt in het aktieve werkblad is de verwijzing naar Blad1 overbodig en kan zelfs onverwachte effekten hebben als de codename van het werkblad anders is/wordt.

 

[VenA]

Alternatief zonder lus. Al ontgaat mij het doel om de cellen leeg te maken en eventueel weer te vullen.

In een module

Sub VenA()
  With Blad1
    .Columns(1).SpecialCells(2).Copy .Cells(1, 52)
    .ComboBox1.List = .Cells(1, 52).CurrentRegion.Value
    .Cells(1, 52).CurrentRegion.Clear
  End With
End Sub

In de module van Blad1

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Column = 1 Then VenA
End Sub

In de module van ThisWorkbook

Private Sub Workbook_Open()
  VenA
End Sub

 

[Rso]

Beste.

Ik zit met nog een klein probleem.

Mijn listbox staat op een andere sheet. dus de gegevens staat op sheet 1 en de listbox op sheet 2.
Als ik de excel open dan laad hij de waarden op sheet 1 in en is de listb ox up to date. Als ik dan op sheet 1 waardes bij in vul, komen ze niet in sheet twee in de listbox. de listbox wordt dus niet geupdate zonder de excel op te slagen, te sluiten en terug te openen. Is hiervoor een oplossing.

Ook zou ik graag hebben dat voor iedere keer dat er in de listbox een andere waarde wordt geselecteerd, dat de waarde van de listbox wordt weggeschreven op sheet 3 cel C1

Alvast bedankt voor jullie zeer waardevolle hulp!

 

[VenA]

Komt dit in de richting?

 

[Rso]

Perfect!

Jullie zijn super hard bedankt voor de onschatbare hulp!

Thanks!