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!
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.