Checking whether a CheckBox control is checked or unchecked

Question

i'm trying to coding vb.net and i'm in a hard situation beacuse i don't know why cant running my code Purpose is Checking CheckBox Control(Form Control in Excel) is Checked Or UnChecked

Here is Code and error is BC30512 String,Integer convert Error in IF condition

Dim Message As String
Dim xlApp As Microsoft.Office.Interop.Excel.Application
Dim xlWb As Microsoft.Office.Interop.Excel.Workbook
xlWb = xlApp.Workbooks.Open(my_excel_path)
Dim xlSt = As Microsoft.Office.Interop.Excel.Worksheet = CType(xlWb.Worksheets(sheet_name),Worksheet)

**If (xlSt.Shapes("Check Box 1").OLEFormat.Object.Value = 1) Then**
Message = "is Checked"
Else
Message = "is UnChecked"
End If

MsgBox(Message)

plz help me

I need a correct grammar i tried xlSt to ActiveSheet.Shapes() and this isn't work (it worked in vba Excel, so i tried)

and my new Code is

Dim excelApp As Microsoft.Office.Interop.Excel.Application = New Microsoft.Office.Interop.Excel.Application
Dim xlWorkbook As Microsoft.Office.Interop.Excel.Workbook = Nothing
Dim xlWorksheet As Microsoft.Office.Interop.Excel.Worksheet = Nothing
Dim shape As Microsoft.Office.Interop.Excel.Shape = Nothing

xlWorkbook = excelApp.Workbooks.Open(FilePath)

'Get the worksheet
xlWorksheet = CType(xlWorkbook.Sheets("Sheet1"), Microsoft.Office.Interop.Excel.Worksheet)

Dim checkBoxNames() As String = {"Check Box 1", "Check Box 2", "Check Box 3", "Check Box 4","Check Box 5","Check Box 6","Check Box 7"} 'Add more names here as needed
Dim isChecked As Boolean

For Each checkBoxName In checkBoxNames
    For Each shape In xlWorksheet.Shapes
        If shape.Name = checkBoxName Then
            isChecked = (CInt(shape.ControlFormat.Value) = 1)
            Console.WriteLine(checkBoxName & ": " & isChecked.ToString())
            Exit For
        End If
    Next
Next

xlWorkbook.Close()

Jeremy Thompson · Answer 1 · 2023-02-23T03:45:17.270

1

With VB.Net its a bit different to VBA, this is what you're missing:

Dim checkboxControl As Microsoft.Office.Interop.Excel.CheckBox

Insert your code here.

And use these two lines to detect if the CheckBox is checked:

checkboxControl = CType(xlWorksheet.CheckBoxes("CheckBox1"), Microsoft.Office.Interop.Excel.CheckBox)

If checkboxControl.Value = Microsoft.Office.Interop.Excel.XlTriState.xlOn Then

edited Feb 23 '23 at 03:45

answered Feb 23 '23 at 03:39

Jeremy Thompson

61,933
36
195
321

Thank you for answering!! I tried your code, but can't find Excel.XlTriState at Ms document how can i use that?? – HOONS Feb 23 '23 at 04:16
You need to reference the PIA's (the Primary Interop Assemblies), and here is the catch you reference the COM ActiveX for Unit Testing. See https://stackoverflow.com/questions/45121396/vsto-unit-testing-office-addin-in-c-sharp-net-via-requestcomaddinautomationserv – Jeremy Thompson Feb 23 '23 at 04:54
Is there a way to do it without PIA? cause, My env is not VSTO – HOONS Feb 27 '23 at 05:23
Sorry, can you say that in another way, it doesn't make sense. The PIA's are needed for the Interop between .Net using a VSTO Project and calls to the native Office DLLs. Your Environment is definitely VSTO, `Dim xlApp As Microsoft.Office.Interop.Excel.Application`. At this point I think you're better off getting a book on the topic if you're trying to work-around things like the core requirements its obviously not going to fly and you'll get deterred. – Jeremy Thompson Feb 27 '23 at 05:34
Thanks for helping me. I tried xlCheckBox but this is not working in my office version, so i change Excel.Shape and it worked – HOONS Mar 07 '23 at 08:40
That means you've tagged your question wrong, you're using VBA, not VB.NET. – Jeremy Thompson Mar 07 '23 at 11:18
oh.. Thanks I update my code Is This Code VBA? Not VB.NET?? – HOONS Mar 07 '23 at 23:58

Checking whether a CheckBox control is checked or unchecked

1 Answers1