Compare CheckBox names (form control) with column values

[VBAnoob_Corina]

Hello everybody,

this is the first time for me to post a question here and I hope that you can understand the problem that I'm currently facing Furthermore, I'm a total VBA noob (really, I just started a few days ago) and also don't have any programming experience at all. Ok, here I go...

One of my Excel worksheets contains some CheckBoxes (Form Control) which are named automatically (thy have unique names). I need all of these names to be compared with the column entries from another worksheet. If a chechbox name matches the entry, then it should be checked, else it can be left unchecked... Can anyone please help me with the code? The one I have written doesn't work. I'm not even sure whether I understand what it does.

Thank you very much for any advice/help.

Kind regards
Corina

Sub CompareCheckboxNames()

Dim ws As Worksheet
Dim SrchRng
Dim shp As Object
Dim myText As String
Dim c As Range

On Error GoTo ErrHandler

Set ws = Worksheets("Risk Category Checklist")
Set SrchRng = ws.Range("G:G")
myText = shp.OLEFormat.Object.Name.Characters.Text
Set c = SrchRng.Find(myText, LookIn:=xlValues)

With Worksheets("Checklist Structure")

For Each shp In .Shapes
If shp.Type = msoFormControl Then
If shp.FormControlType = xlCheckBox Then
If Not c Is Nothing Then
shp.OLEFormat.Object.Value = False
Else
shp.OLEFormat.Object.Value = True
End If
Exit For
End If
End If
Next
End With

Exit Sub

ErrHandler:
Call MsgBox(Err.Description, vbCritical, "Fehler " & Err.number)

End Sub

 

[Comfy]

I've not tested it but try this:

Sub CompareCheckboxNames()


Dim ws As Worksheet
Dim SrchRng
Dim shp As Object
Dim myText As String
Dim c As Range


On Error GoTo ErrHandler


Set ws = Worksheets("Risk Category Checklist")
Set SrchRng = ws.Range("G:G")


With Worksheets("Checklist Structure")
    For Each shp In .Shapes
        If shp.Type = msoFormControl Then
            If shp.FormControlType = xlCheckBox Then
                myText = shp.OLEFormat.Object.Name.Characters.Text
                Set c = SrchRng.Find(myText, LookIn:=xlValues)
                    If Not c Is Nothing Then
                        shp.OLEFormat.Object.Value = True
                    Else
                        shp.OLEFormat.Object.Value = False
                    End If
                    Exit For
            End If
        End If
    Next shp
End With


Exit Sub


ErrHandler:
Call MsgBox(Err.Description, vbCritical, "Fehler " & Err.Number)


End Sub

everything you need is in your original code I just moved a few things around.

Also, I've formatted the text (just by tabbing some lines) so that it is easier to read at a glance.

 

[MickG]

At the risk of sticking my nose in. The code above works for me when I modify it slightly as shown in red:-

Sub CompareCheckboxNames()
Dim ws As Worksheet
Dim SrchRng
Dim shp As Object
Dim myText As String
Dim c As Range




On Error GoTo ErrHandler




Set ws = Worksheets("Risk Category Checklist")
Set SrchRng = ws.Range("G:G")




With Worksheets("Checklist Structure")
    For Each shp In .Shapes
        If shp.Type = msoFormControl Then
            If shp.FormControlType = xlCheckBox Then
                myText = shp.OLEFormat.Object.Name '.Characters.Text (removed)
                Set c = SrchRng.Find(myText, LookIn:=xlValues)
                    If Not c Is Nothing Then
                        shp.OLEFormat.Object.Value = True
                    Else
                        shp.OLEFormat.Object.Value = False
                    End If
                    'Exit For (removed)
            End If
        End If
    Next shp
End With




Exit Sub
ErrHandler:
Call MsgBox(Err.Description, vbCritical, "Fehler " & Err.Number)




End Sub

 

[Comfy]

I too thought about removing the Exit For but once you have found a match wouldn't it be pointless to check any more checkboxes as checkbox names have to be unique?

Or can you give the same name to multiple checkboxes?

 

[MickG]

Unless I'm misunderstanding the thread (quite possible) , the code should loop through each "CheckBox" and if its "Name" exists in the list on the other sheet, it changes its value to true. If you exit the loop after the first find, then you can't check any more checkboxes. !!!

 

[Comfy]

Unless I'm misunderstanding the thread (quite possible) , the code should loop through each "CheckBox" and if its "Name" exists in the list on the other sheet, it changes its value to true. If you exit the loop after the first find, then you can't check any more checkboxes. !!!

Oops, my bad.

 

[VBAnoob_Corina]

Thank you so much guys (MickG & Comfy)! The posted code works perfectly fine. And the names of the Checkboxes have to be unique, they always refer to categories which do not overlap.

Have a nice day!

Kind regards
Corina

 

[MickG]

You're Welcome