excel - VBA - Check selected checkboxes against sheet range

Question

I've been trying for some time to do the following:

1. Check which checkboxes/weight in Userform are selected.
2. Compare checkboxes captions/weight against range.
3. If checkboxes captions/weight in range: do nothing.
4. If checkboxes captions/weight not in range: delete values in range that DO NOT match currently selected checkboxes.

Is there any way to achieve this? I've though about dictionaries or arrays as you can see in the ilustration above, but I have no idea how to implement these things. My VBA skills are very rudimental still. Please let me know if something doesn't make sense, I'll be happy to help!

This is the range (range size may vary).

These are the metrics (in the Userform):

Dictionary is working well:

Dim dict As Object
Dim i As Long, j As Long
Dim mBox As CheckBox, wBox As TextBox

Set dict = CreateObject("Scripting.Dictionary")

dict.RemoveAll

For i = 1 To 12
    Set mBox = frmSeg.Controls("seg_cb_" & i)
    Set wBox = frmSeg.Controls("seg_tb_W_" & i)
    
    If cBox.Value = True Then
        dict.Add Key:=mBox.Caption, Item:=wBox.Value
    End If
Next i

Attempt at deleting the values (not working):

For i = 0 To dict.Count - 1
    j = 15
        If dict.Keys()(i) <> Sheets("HCP_UI").Cells(j, 9) And dict.Items()(i) <> Sheets("HCP_UI").Cells(j, 10) Then
            Sheets("HCP_UI").Range(Cells(j, 9), Cells(j, 10)).Delete
            
        End If
    j = j + 1
Next i

question from:https://stackoverflow.com/questions/65904121/vba-check-selected-checkboxes-against-sheet-range

Answer 1

This is working pretty well for me:

' Delete entire range
Dim uSheet As Worksheet
Dim dict As Object
Dim i As Long, j As Long
Dim mBox As CheckBox, wBox As TextBox
Dim key1 As String
Dim item1 As String
Dim item2 As Long
Dim aKey As Variant

Set nmMetric = New Collection
Set uSheet = ThisWorkbook.Sheets("HCP_UI")

' Delete old values in Tmetrics + Weights

Set dict = CreateObject("Scripting.Dictionary")

dict.RemoveAll

For i = 1 To 9
    If frmSeg.Controls("seg_cb_" & i).Value = True And frmSeg.Controls("seg_ob_T_" & i) = True Then
        dict.Add Key:=frmSeg.Controls("seg_cb_" & i).Caption, Item:=frmSeg.Controls("seg_tb_W_" & i).Value
    End If
Next i

uSheet.Range("I3:J" & uSheet.Range("J" & uSheet.Rows.Count).End(xlUp).Row).Clear

j = 3
For i = 0 To dict.Count - 1
    uSheet.Cells(j, 9) = dict.Keys()(i)
    uSheet.Cells(j, 10) = dict.Items()(i)
    
    j = j + 1
Next i

However for a different case, I'd need my dictionary to have keys with 2 items. Is that possible?