[SOLVED] Excel VBA generated checkboxes not aligning with cells

Excel VBA generated checkboxes not aligning with cells

I would like to create a column of 100 checkboxes to select rows.
I can create the checkboxes but as they go further down the sheet the checkboxes slowly diverge from the desired rows. Checkbox labeled for row 101 - chkbox101 ends up in row 102. checkbox labeled chkbox101 in row 102

Dim cBox As CheckBox

Dim cell As Range

For Each cell In Range("a2:a101")
  Set cBox = ActiveSheet.CheckBoxes.Add(cell.Left, cell.Top , cell.Width, cell.Height)
     
  cBox.Text = "CHKBX " & cell.Row
                
       
Next cell

My zoom setting is 100% My Row height is 14.4

I've made a standalone spreadsheet, completely blank with just this macro in it.

I have had this happen before in another spreadsheet, basically the same coding technique.

And of course, now I am unable to get it to occur for me either. I will see if I can chase the cause in another way. I will update if/when I find my cause/error.

Solution

The issue was caused by running the spreadsheet on the extended desktop monitor and having fix scaling for apps turned on.

I am using a laptop with the display extended over to a second monitor. The when the spreadsheet is completely on the laptop monitor, the code achieves the expected results (Column A) (why I couldn't get it to happen above). When the code is run with the spreadsheet even partially on the external monitor the issue of non alignment shows up (Column C) - the checkboxes do not stay in the appropriate rows.

Which monitor the code is run on changes the output when fix scaling for apps is turned on.

I also changed the macro a little to allow changing the output column.

Sub InsertCheckboxes()
    
    Dim cBox As CheckBox
    
    Dim ThisCol As String
    ThisCol = Sheet1.Range("d1").Value
    
    Dim cell As Range
    
    For Each cell In Range(ThisCol & "2:" & ThisCol & "101")
        Set cBox = ActiveSheet.CheckBoxes.Add(cell.Left, cell.Top, cell.Width, cell.Height)
        cBox.Text = "CHKBX " & cell.Row
    Next cell

End Sub

and attached here is a screen shot of what that looks like picture of original checkbox alignment issues and with fix scaling turned off

col a checkboxes with fix scaling on, and the spreadsheet on the main display Checkbox labeled for row 101 - chkbox101 ends up in row 101 as expected

col C checkboxes with fix scaling on, and the spreadsheet on the other display Checkbox labeled for row 101 - chkbox101 ends up in row 102

col e checkboxes with fix scaling OFF, and the spreadsheet on the other display Checkbox labeled for row 101 - chkbox101 ends up in row 101 as expected