How to give checkboxes a specific size or alignment?

[Kimberley]

Hi,

I'm facing a little problem with a code that creates checkboxes. As you can see on the following picture, my data validation arrows are hidden by the checkboxes created by my code.

\1

    For lign = 1 To [Q6].Value
            
        posX1 = Cells(lign + 1, 7).Left
        posY1 = Cells(lign + 1, 7).Top
        width1 = Cells(lign + 1, 7).Width
        height1 = Cells(lign + 1, 7).Height
        With ActiveSheet.CheckBoxes.Add(posX1, posY1, width1, height1)
            .LinkedCell = Cells(lign + 1, 13).Address
            .Characters.Text = "Accompagnied by"
            .Name = "Accompagnied " & lign
        End With

    Next lign

In this code, the checkboxes are created to fit the G col. How can I move it a little to the right so I can have access to the list arrow, and reduce its size to the left of the same amount so it doesn't overlap on the H column?

Thanks in advance.

PS: you may have to click on "create list" and then "reset" twice or 3 times to get this overlapping issue.

[StephenR]

If you increase the xpos by 13 seems to do the trick?

[romperstomper]

Try this - you can tweak the lOffset value as needed:

Private Sub CreateTheList_Click()

    Dim n As Integer
    Dim cel As Range
    Const lOffset As Double = 10
    
    On Error Resume Next
    
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
    For n = 1 To [Q6].Value - 1
        Range("F" & n + 1).AutoFill Destination:=Range("F" & n + 1 & ":F" & n + 2), Type:=xlFillDefault
        Range("H" & n + 1).AutoFill Destination:=Range("H" & n + 1 & ":H" & n + 2), Type:=xlFillDefault
    Next n
    
    For lign = 1 To [Q6].Value
            
        posX1 = Cells(lign + 1, 7).Left + lOffset
        posY1 = Cells(lign + 1, 7).Top
        width1 = Cells(lign + 1, 7).Width - lOffset
        height1 = Cells(lign + 1, 7).Height
        With ActiveSheet.CheckBoxes.Add(posX1, posY1, width1, height1)
            .LinkedCell = Cells(lign + 1, 13).Address
            .Characters.Text = "Accompagnied by"
            .Name = "Accompagnied " & lign
        End With

    Next lign
    
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

End Sub

[Kimberley]

Thanks!