I've been trying for some time to do the following:
- Check which checkboxes/weight in Userform are selected.
- Compare checkboxes captions/weight against range.
- If checkboxes captions/weight in range: do nothing.
- 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 与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…