VBA Excel lussen van combobos namen

[dengeuns]

Hallo allen,

Ik ben pas begonnen met wat te programmeren in Excel maar ben vast gelopen op een probleem. Ik wil in mijn programma afhankelijk van de ingaven in een bepaalde cel van het werkblad een combobox vullen. Aangezien dit redelijk wat keuzes zijn zou ik automatisch willen laten herkennen in welke cel ik zit. Dus wanneer ik mijn combobox gelijk zet met de B cel ( en dus respectievelijk B1, B2,... B100 noem) zou dit het volgende zijn:

Private Sub Worksheet_Change(ByVal Target As Range)
      Dim Row As Long
      Row = Target.Row
            With ("B" & Row)
                .AddItem "Voorraad"
                .AddItem "Frigo"
            End With
End Sub

In een Userform blijkt dit te werken met Me.Controls("B" & Row) maar hoe werkt dit dan in een worksheet?

Alvast bedankt!

 

[HSV]

Gebruik geen 'Row' als variabele naam.
'Row' wordt standaard gebruikt in Vba; je code zal ergens op stuk lopen.

Plaats een Excel voorbeeldbestandje als je wilt met uitleg wat er gewenst is.

 

[dengeuns]

Bedankt voor de snelle reactie, ik had dat deel nog niet erin gezet maar de opmerking staat genoteerd.

Hieronder staat een voorbeeld met daarin wat ik eigenlijk bedoel op een vereenvoudigde manier. Ik heb hier 4 comboboxen geplaatst die vervolgens gevuld worden wanneer test of Test in de A kolom wordt geschreven. Om het geheel te laten werken heb ik nu het variabele gedeelte achterwegen gelaten, zoals ook al te zien is in deze code.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim RowS As Long
    
    If Intersect(Target.Cells(1, 1), Range("A:M")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    If Target.Column = 1 Then
        RowS = Target.Row
        If Target.Value = "test" Or Target.Value = "Test" Then
            B3.Clear
            With B3
                .AddItem "AAA"
                .AddItem "BBB"
                .AddItem "CCC"
                .AddItem "DDD"
                .AddItem "EEE"
            End With
        End If
        If Target.Value = "" Then
            B3.Clear
        End If
    End If
    Application.EnableEvents = True
End Sub

De B3 die ik hierin gebruik zou dus variabel moeten zijn afhankelijk van de variabele RowS.

Bekijk bijlage VB_Combobox.xlsm

 

[HSV]

Test het eens.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim sq As Variant, rw As Long, obj As ComboBox

 If Not Intersect(Target, Columns(1)) Is Nothing Then
   On Error GoTo einde
 Application.EnableEvents = False
  sq = "AAA " & "BBB " & "CCC " & "DDD " & "EEE"  'denk om spatie achteraan behalve de laatste
  rw = Target.Row
  If rw > 2 Then
 If UCase(Target) = "TEST" Then
  OLEObjects("Combobox" & rw - 2).Object.List = Application.Transpose(Split(sq))
    Else
  OLEObjects("Combobox" & rw - 2).Object.Clear
     End If
    End If
   End If
  Application.EnableEvents = True
 Exit Sub
einde: Application.EnableEvents = True
End Sub

 

[dengeuns]

Zo werkt het inderdaad, bedankt daarvoor!

Weet jij toevallig of zo een principe ook toepasbaar is bij de subroutines die controleren of de combobox verandert. Nu heb ik namelijk een hele lijst code:

Private Sub Combobox1_Change()
 ....
End Sub
Private Sub Combobox2_Change()
 ....
End Sub
Private Sub Combobox3_Change()
 ....
End Sub

......

Private Sub Comboboxn_Change()
 ....
End Sub

Dit terwijl de effectieve code eigenlijk voor het grootste deel gelijk blijft. Een lus met een variabelen zal hiervoor niet werken waarschijnlijk?

 

[HSV]

Helaas begrijp ik niet waar je heen wil.
Misschien kun je het iets beter uitleggen, eventueel met een bijlage in .xls of .xlsm extensie.