0

I'm attempting to make a reader friendly Excel spreadsheet from a bunch of different data sources. One part of this is adding checkbox's to every row in a certain column. I believe that this can't be done by "cell" per say but by location on the spreadsheet from what I've read.

I've attempted...

Dim cb As ICheckBox = protoWorksheet.CheckBoxes.AddCheckBox(4, 2, 15, 100)
cb.CheckState = CheckState.Checked
cb.Text = "Test"

But received the following error:

An unhandled exception of type System.MissingMemberException' occurred in Microsoft.VisualBasic.dll Additional information: Public member 'AddCheckBox' on type 'CheckBoxes' not found.

Any help is much appreciated!

14
  • 1
    According to the error-message, "CheckBoxes.AddCheckBox" doesn't seem to exist. I think the method you may be looking for is Checkboxes.Add Commented Jun 13, 2017 at 18:16
  • @soohoonigan thanks for the reply! I tried your way and it still gave me and error... An unhandled exception of type 'System.InvalidCastException' occurred in MITRE Project Commander.exe Additional information: Unable to cast COM object of type 'System.__ComObject' to interface type 'Microsoft.Office.Interop.Excel.ICheckBox'. Commented Jun 13, 2017 at 18:29
  • @soohoonigan this is what you meant correct? Dim cb As ICheckBox = protoWorksheet.CheckBoxes.Add(4, 2, 15, 100) Commented Jun 13, 2017 at 18:29
  • Are you reading this into a form or writing to excel? Commented Jun 13, 2017 at 18:37
  • Writing to excel @codeMonger123 Commented Jun 13, 2017 at 18:44

1 Answer 1

2

The add method is meant for a normal Checkbox, not an ICheckbox. Here is an example of how to add ordinary checkboxes to each row:

    Dim xlApp As Excel.Application = New Microsoft.Office.Interop.Excel.Application
    xlApp.Visible = True
    Dim xlWorkbooks As Excel.Workbooks = xlApp.Workbooks
    Dim xlWorkbook As Excel.Workbook = xlWorkbooks.Add
    Dim xlWorksheet As Excel.Worksheet = CType(xlWorkbook.Worksheets(1), Excel.Worksheet)

    'Here, we get the rowHeight so we can position each textbox within each row
    Dim rowHeight As Integer = CInt(xlWorksheet.Range("A1").RowHeight)
    Dim cbWidth As Integer = 100
    Dim cbHeight As Integer = rowHeight
    Dim cbLeft As Integer = 5

    'Adds ten checkboxes to the page, one on each line
    For i As Integer = 0 To 9
        Dim cbTest As Microsoft.Office.Interop.Excel.CheckBox = xlWorksheet.CheckBoxes.Add(cbLeft, (i * rowHeight), cbWidth, cbHeight)
        With cbTest
            .Value = True
            .Text = "TestText" & i.ToString
            .Name = "Checkbox" & i.ToString
        End With
    Next i

    'To check the values of each checkbox, you can iterate the worksheet's checkboxes and check their name/value properties
    For Each cb As Microsoft.Office.Interop.Excel.CheckBox In xlWorksheet.CheckBoxes
        MsgBox(cb.Name & " : " & If(CType(cb.Value, Boolean) = True, "Checked", "Unchecked"))
    Next cb

The code above does several things to address each of the further questions in the comments. First, is uses the CheckBoxes.Add method to add checkboxes to the spreadsheet. Next, since as you mentioned, checkboxes can't be added to rows and must be positioned using a point, I get rowheight and save it for later (In a spreadsheet where the rows may have different heights, you would want to move that rowheight logic inside the loop and check each row as you get to it, but a single value is sufficient for this example). Then, just using a simple for loop to put a checkbox in each row, using the rowheight multiplied by row# to get the correct position. I've also given each checkbox a name for reference later. Lastly, there is an example of a loop that can be used to iterate all the spreadsheet's checkboxes and check their checked/unchecked values.

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

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.