Macro - ActiveX Checkbox - linked cell

[esauowain]

Hi;

I'm trying to modify a macro that will:

1. create an activeX checkbox in a cell range
2. Link the checkbox to the relevent cell
3. Change the background of the checkbox to transparent
4. Change the object position to "Move and size with cells"

I can only seem to get it to create the basic checkboxes in the cells, every time i try link them to cells i get erros. The code i have so far is:

Sub ActX_Add_Multiple_CheckBox_Ex2()

    'Disable Screen Update
    Application.ScreenUpdating = False
    'Variable Declaration
    Dim Rng As Range
    Dim ShtRng As Range
    Dim WrkSht As Worksheet
    Set ShtRng = Application.Selection
    Set ShtRng = Application.InputBox("Range", "Analysistabs", ShtRng.Address, Type:=8)
    Set WrkSht = Application.ActiveSheet
    
    For Each Rng In ShtRng
        WrkSht.OLEObjects.Add "Forms.CheckBox.1", Left:=Rng.Left, Top:=Rng.Top, Width:=Rng.Width, Height:=Rng.Height
    Next
    
    ShtRng.ClearContents
    ShtRng.Select
    
    'Enable Screen Update
    Application.ScreenUpdating = True
    
End Sub

Any help on this would be greatly appreciated.

Thanks

 

[John_w]

Try this:

Public Sub Add_ActiveX_Checkboxes()

    Dim wks As Worksheet
    Dim cell As Range, checkboxCells As Range
    Dim objOLE As OLEObject
    
    Set wks = ActiveSheet
    
    Set checkboxCells = Application.Selection
    Set checkboxCells = Application.InputBox("Range", "Analysistabs", checkboxCells.Address, Type:=8)
    
    For Each cell In checkboxCells
    
        Set objOLE = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CheckBox.1")

        With objOLE
            .Left = cell.Left
            .Top = cell.Top
            .Width = cell.Width
            .Height = cell.Height
            .name = "Checkbox_" & cell.Address
            .LinkedCell = cell.Worksheet.name & "!" & cell.Address
            '.Placement = xlMove
            .Placement = xlMoveAndSize
            .Object.Value = False
            .Object.BackStyle = fmBackStyleTransparent
            '.Object.BackStyle = fmBackStyleOpaque
            .Object.TripleState = False 'True
            .Object.Caption = ""
        End With
    
    Next

End Sub

 

[esauowain]

Hi;

Thanks for the reply, the script runs but it doesn't link it to the cell. The linked cell (under properties) is still blank.

Also the transparency doesnt work. It does actually set the property value to fmBackStyleTransparent but its still showing as opaque.

Regards

 

[John_w]

You didn't say what the linked cell should be. In the code above the linked cell is the same as the cell in which the checkbox sits, and it works for me. To test, put a simple =A1 formula in another cell (where A1 is a checkbox cell).

The transparent style seems to have a bug - https://social.msdn.microsoft.com/F...tyletransparent-fails-in-excel?forum=exceldev