Checking if cell has Checkbox

[vegetamaker]

Hello again! And thanks for your time!

Well, I found a very usefull code to add many checkboxes in the Sheet, but I was trying to do something more. I was trying to check if the cell has a checkbox already, if not, then it should add it.

The code is the next: https://stackoverflow.com/questions...d-size-with-cells-for-checkbox-created-in-vba (Isn't the original one, I can't find it again. But it's basically the same). I converted it to this:

Sub CellCheckbox()
    Dim myCell As Range
    Dim CBX As CheckBox


    With ActiveSheet
        .CheckBoxes.Delete 'nice for testing!        
        '#################
        Set myRng1 = .Range("B2:B2, C2:C5")  'change to the range you want
        '#################        
    End With


    For Each myCell In myRng1.Cells
        With myCell            
                Set CBX = .Parent.CheckBoxes.add _
                            (Top:=.Top, _
                            Left:=.Left + 20, _
                            Width:=1, _
                            Height:=.Height)
                CBX.Name = "CheckBox_" & .Address(0, 0)
                CBX.Caption = "" 'Text with Checkbox
                CBX.Value = xlOff
                CBX.Placement = xlFreeFloating 'To set it automatically to NOMOVE
                
                CBX.LinkedCell = .Address(RowAbsolute:=False, _
                                    ColumnAbsolute:=False, _
                                    external:=False)
                .NumberFormat = ";;;"            
        End With
    Next myCell
    
End Sub

Well, after it the next step is learn how to check if a cell has a checkbox, and I find this another code for it: https://stackoverflow.com/questions/35921758/excel-vba-ckeck-if-cell-contains-a-checkbox . I converted it to this:

Public Function HasCheckbox(rng As Range) As Boolean
    If Not Application.Intersect(rng, ActiveSheet.CheckBoxes.TopLeftCell) Is Nothing Then
        HasCheckbox = True
    Else
         HasCheckbox = False
    End If
End Function

But the Function requires a Range and my actual code is using a single Address. So I was trying two things: Or change the Function to ask for an Address or try to convert my Address to Range. And I can't find a solution for it.

I tryed something like this:

Dim myRange$
       myRange = Range(Cells(myCell.Row, myCell.Column), Cells(myCell.Row, myCell.Column)).Address(False, False)
      'But this didn't work :D

The next step is add an "If" inside the "For Each" in the first code that call the 'HasCheckbox' function.

But for now that is my problem. I guess I could find a way to fix it with something of the next (But I can't fix it by myself atm):

- convert my Address to a Range
- Change the HasCheckbox function to need a Address instead a Range
- OR another way to check if a cell has a checkbox already

Btw, I am using Form Controls checkboxes.

Thanks so much for your help!

 

[Logit]

https://stackoverflow.com/questions/35921758/excel-vba-ckeck-if-cell-contains-a-checkbox

 

[vegetamaker]

Thanks for your help, but if you read my first post, you could see that I checked that link already.

 

[Rick Rothstein]

Give this function a try (you can pass it a single cell or a multi-cell range)...

Function HasCheckBox(Rng As Range) As Boolean
  Dim Obj As Object
  For Each Obj In Rng.Parent.OLEObjects
    If Obj.progID Like "*CheckBox*" Then
      If Not Intersect(Obj.TopLeftCell, Rng) Is Nothing Then
        HasCheckBox = True
        Exit Function
      End If
    End If
  Next
End Function

 

[vegetamaker]

Thanks so much Rick! But still I have the same problem. When I try to call the function the parameter is incorrect because I don't know how to convert my Address to Range.

 

[vegetamaker]

I managed to send a range variable correctly, **** hard to learn! Well, I am now trying your code Rick, but for now it always return False. I'll keep trying.

Any tip is always welcome, ofc.

Thanks :3

Edit: I tryed the first code I found and it worked, finally! Thanks to all!

 

[Rick Rothstein]

Thanks so much Rick! But still I have the same problem. When I try to call the function the parameter is incorrect because I don't know how to convert my Address to Range.

If you call my function from other VB code, you would do so similar to this...

MsgBox HasCheckBox(Range("A1"))

or...

MsgBox HasCheckBox(Cells(1, "A"))

or if your cell is stored in a Range variable (named MyRange for this example)...

MsgBox HasCheckBox(MyRange)

If, on the other hand, you call if within an Excel formula on the worksheet...

=HasCheckBox(A1)

 

[vegetamaker]

Very interesting and usefull, Thanks so much for your time!