I need to find a way to display several values in one cell. I also found a solution by the post of 'L42' (https://stackoverflow.com/a/23319627/10506941)
This is the current code I am using:
Option Explicit
Dim fillRng As Range
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Countries As MSForms.ListBox
Dim LBobj As OLEObject
Dim i As Long
Set LBobj = Me.OLEObjects("Countries")
Set Countries = LBobj.Object
If Not Intersect(Target, [AT:BB]) Is Nothing Then
Set fillRng = Target
With LBobj
.Left = fillRng.Left
.Top = fillRng.Top
.Width = fillRng.Width
.Visible = True
End With
Else
LBobj.Visible = False
If Not fillRng Is Nothing Then
With Countries
If .ListCount <> 0 Then
For i = 0 To .ListCount - 1
If fillRng.Value = "" Then
If .Selected(i) Then fillRng.Value = .List(i)
Else
If .Selected(i) Then fillRng.Value = _
fillRng.Value & "," & .List(i)
End If
Next
End If
For i = 0 To .ListCount - 1
.Selected(i) = False
Next
End With
Set fillRng = Nothing
End If
End If
End Sub
This is definitely the way I wanted to do it. But I have some problems:
- The values won't adapt untill I click another cell abroad the column AT to BB.
- Changing cells deletes the selected values. Is there a way to regocnize the values in a cell and mark them as already selected?
- The code is always adding the values after changing to another cell. Is there a way to not allow duplicates?
Can someone help me? I am new to this topic and I have no clues anymore :/
Aucun commentaire:
Enregistrer un commentaire