Using .NET how would you set a CheckBox to true within an array?

Question

I have populated a column of check boxes, and attempted to set the value of them to true like so:

worksheet.CheckBoxes("Check").Value = True

This is my code:

Public Sub AddCheckboxColumnToSpreadsheet(filePath As String)
    ' Create Excel application
    Dim excelApp As New Excel.Application
    excelApp.Visible = False

    ' Open the workbook
    Dim workbook As Excel.Workbook = excelApp.Workbooks.Open("C:\Desktop\Test.xlsx")
    Dim worksheet As Excel.Worksheet = workbook.Sheets(1)

    ' Find the last used column and row
    Dim lastCol As Integer = worksheet.Cells(1, worksheet.Columns.Count).End(Excel.XlDirection.xlToLeft).Column
    Dim lastRow As Integer = worksheet.Cells(worksheet.Rows.Count, 1).End(Excel.XlDirection.xlUp).Row

    ' Insert a header for the checkbox column
    worksheet.Cells(1, lastCol + 1).Value = "Check"

    ' Loop through each row and add a checkbox
    For row As Integer = 2 To lastRow
      Dim leftPos As Double = worksheet.Cells(row, lastCol + 1).Left + 2
      Dim topPos As Double = worksheet.Cells(row, lastCol + 1).Top + 2
      Dim cbWidth As Double = worksheet.Cells(row, lastCol + 1).Width - 4
      Dim cbHeight As Double = worksheet.Cells(row, lastCol + 1).Height - 4

      worksheet.CheckBoxes.Add(leftPos, topPos, cbWidth, cbHeight).Select()

      worksheet.CheckBoxes("Check").Value = True


      excelApp.Selection.Caption = ""

    Next

    ' Save and close
    workbook.Save()
    workbook.Close(False)
    excelApp.Quit()

    ' Release COM objects
    ReleaseComObject(worksheet)
    ReleaseComObject(workbook)
    ReleaseComObject(excelApp)
End Sub

This part of code does not work, the checkbox is un-check and I would like it checked: worksheet.CheckBoxes("Check").Value = True — Lloyd Thomas
– Lloyd Thomas, Commented Aug 18 at 10:49

jkpieterse · Accepted Answer · 2025-08-18 12:14:22Z

0

For one box, use:

'Check box:
worksheet.CheckBoxes("Check").Value = 1
'Uncheck box:
worksheet.CheckBoxes("Check").Value = -4146

But you are adding multiple boxes, so:

    ' Find the last used column and row
    Dim lastCol As Integer = worksheet.Cells(1, worksheet.Columns.Count).End(Excel.XlDirection.xlToLeft).Column
    Dim lastRow As Integer = worksheet.Cells(worksheet.Rows.Count, 1).End(Excel.XlDirection.xlUp).Row

    ' Insert a header for the checkbox column
    worksheet.Cells(1, lastCol + 1).Value = "Check"

    ' Loop through each row and add a checkbox
    For row As Integer = 2 To lastRow
      Dim leftPos As Double = worksheet.Cells(row, lastCol + 1).Left + 2
      Dim topPos As Double = worksheet.Cells(row, lastCol + 1).Top + 2
      Dim cbWidth As Double = worksheet.Cells(row, lastCol + 1).Width - 4
      Dim cbHeight As Double = worksheet.Cells(row, lastCol + 1).Height - 4

      With worksheet.CheckBoxes.Add(leftPos, topPos, cbWidth, cbHeight)
          .Value = 1
      End With


      excelApp.Selection.Caption = ""

    Next

    ' Save and close
    workbook.Save()
    workbook.Close(False)
    excelApp.Quit()

    ' Release COM objects
    ReleaseComObject(worksheet)
    ReleaseComObject(workbook)
    ReleaseComObject(excelApp)
End Sub

answered Aug 18 at 12:14

jkpieterse

3,0491 gold badge11 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Lloyd Thomas Aug 18 at 14:46

Thank you @jkpieterse this does work. However, to the right of the checkbox in every cell is the text 'check b' do you know why this is?

Lloyd Thomas Aug 18 at 15:03

I have added the following to remove 'check b': .Characters.Text = ""

Collectives™ on Stack Overflow

Using .NET how would you set a CheckBox to true within an array?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related