lundi 15 octobre 2018

VBA - Checkbox for multiple values in a cell in Excel 2016

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