Hi,
What I'm trying to achieve is to load a complete list (from a named range "koetswerk") and compare it with the values of an active cell (in row K) after clicking in it. If the values (Ex: SD, P, SUV) are equal to the ones in the complete list, then it should show a "checked" checkbox in Listbox_KW. The listbox can have multiple selections. What I've already found out is how to put the data in the active cell with the Sub cmdADD_Click().
This is what I know is working:
'Me.ListBox_KW.List = rngKWA.Value '<--- LIST ALL FROM NAMED RANGE
'.List = Split(CStr(ActiveCell.Value), ",") '<--- LIST SPLITTED CELL VALUES
I've started out with a For loop. How can the data be compared and .selected ?
Dim i As Integer
With frmKW.ListBox_KW
'.List = rngKWA.Value '<-- List All
'.List = Split(CStr(ActiveCell.Value), ",") '<-- List Cell
For i = 0 To .ListCount - 1
If .List(i) = CStr(ActiveCell.Value) Then .ListIndex = i
Next i
End With
Display More
I'm a vba newbie and I'm struggling for days to find a solution that really works (searched many forums) for this. Thank you when responding.
[ATTACH=JSON]{"alt":"Column K - List All","data-align":"none","data-attachmentid":"1198591","data-size":"custom","height":"367","title":"Koetswerk - Column K - List All.JPG","width":"300"}[/ATTACH]
[ATTACH]n1198610[/ATTACH]
Option Explicit
Private Sub UserForm_Initialize()
Dim oWB As Workbook
Dim oWS5 As Worksheet
Dim rngKWA As Range
'Dim rngKWB As Range '<-- NOT YET USED
On Error GoTo ErrorHandle
Set oWB = Workbooks("BRONBESTAND.xlsm")
Set oWS5 = oWB.Worksheets("KOETSWERK")
Set rngKWA = oWS5.Range("koetswerk")
'Set rngKWB = oWS5.Range("koetswerk_long") '<-- NOT YET USED
With Me.ListBox_KW
'-------- SET USERFORM PROPERTIES ------------
'Enables multiple selections
.MultiSelect = fmMultiSelectMulti
'Selects the list style with small boxes.
.ListStyle = fmListStyleOption
'---------------------------------------------
' .Clear ' remove existing entries from the listbox '<--- TEMP OUT!
' turn screen updating off, prevent the user from seeing the source workbook being opened
Application.ScreenUpdating = False
'----------------------------------
' USE THIS INFO:
'----------------------------------
'http://sitestory.dk/excel_vba/listboxes.htm
'SEARCH TERMS: excel vba userform listbox fmListStyleOption update from cell -rowsource
'http://blog.contextures.com/archives/2009/09/18/select-multiple-items-from-excel-data-validation-list/
'----------------------------------
' THOUGHT:
'----------------------------------
'--> If selected cell values equals value of rngKWA(total list) then checkbox = True '<--
'----------------------------------
' KNOWN INFO:
'----------------------------------
'Me.ListBox_KW.List = rngKWA.Value '<--- LIST ALL FROM NAMED RANGE
'.List = Split(CStr(ActiveCell.Value), ",") '<--- LIST SPLITTED CELL VALUES
'----------------------------------
Dim i As Integer
With frmKW.ListBox_KW
'.List = rngKWA.Value '<-- List All
'.List = Split(CStr(ActiveCell.Value), ",") '<-- List Cell
For i = 0 To .ListCount - 1
If .List(i) = CStr(ActiveCell.Value) Then .ListIndex = i
Next i
End With
'----------------------------------
Application.ScreenUpdating = True
End With
BeforeExit:
' Set rngKWA = Nothing '<--- TEMPORALY DEACTIVATED!
Exit Sub
ErrorHandle:
MsgBox Err.Description
Resume BeforeExit
End Sub
Private Sub cmdClose_Click()
Unload Me
End Sub
Private Sub cmdADD_Click() '<--- PUT VALUES IN CELL: WORKS!
Dim strSelItems As String
Dim lCountList As Long
Dim strSep As String
Dim strAdd As String
Dim bDup As Boolean
On Error Resume Next
strSep = ", "
With Me.ListBox_KW
For lCountList = 0 To .ListCount - 1
If .Selected(lCountList) Then
strAdd = .List(lCountList)
Else
strAdd = ""
End If
If strSelItems = "" Then
strSelItems = strAdd
Else
If strAdd <> "" Then
strSelItems = strSelItems & strSep & strAdd
End If
End If
Next lCountList
End With
With ActiveCell
If .Value <> "" Then
.Value = ActiveCell.Value & strSep & strSelItems
Else
.Value = strSelItems
End If
End With
ListBox_KW.Value = Range("K2").Value
Unload Me
End Sub
Display More