asked on

How to change a checkbox value in Excel

Hi, I am designing a program in VB6, this program reads an employees database, opens a Excel file and fills many fields in a designed format. The program doesn't have problems to write data in the cells, but I can't change the value in the checkboxs the form has. For example, in the form there are 2 checkboxs which indicate the sex: one for indicate if the person is masculine and other for indicate if the person is feminine. So, depending on the values the program reads from the database, it has to turn on one check box and turn off the other one. I created a macro in Excel to see the code, but it doesn't work in VB. I copy the code of the macro below. Thanks.

Range("B28").Select
    ActiveSheet.Shapes("Check Box 6").Select
    With Selection
        .Value = xlOn
    End With

Open in new window

Krys_K

Hi There
To work with CheckBoxes in Excel its the following
Regards
Krystian

If Sheet1.CheckBox1.Value = True Then
MsgBox "True"
Else
MsgBox "False"
End If

Open in new window

PaulHews

Assuming your application variable is xlApp and assuming it's a checkbox drawn with the Control Toolbox, you would call it like this:

xlApp.Sheets("SheetName").CheckBox6.Value = 1

Open in new window

aviveros

ASKER

I tried with the 2 options, but an error appears saying the object can't support this method. This is the code:

'(Sheet1 is a variable assigned to the sheet1 in the workbook)
If Sheet1.CheckBox6.Value = 1 Then
MsgBox "True"
Else
MsgBox "False"
End If

Into the program I have a reference to the Microsoft Excel 10.0 Object Library; I don't know if I have to add some additional reference or component

PaulHews

Since your macro code generated code for the shapes collection, try it this way instead. (How did you create the checkbox?)

Sheet1.Shapes("Check Box 6").Select
Sheet1.Selection.Value = 1

Open in new window

ASKER CERTIFIED SOLUTION

PaulHews

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Krys_K

HI
In VB you are aware that you need to bind to Excel first?
See below
Krystian

Dim xlApp
Set xlApp = CreateObject("Excel.Application")
 
xlApp.ActiveSheet.
xlApp.ActiveWorkbook.
 
etc etc..

Open in new window

aviveros

ASKER

The last example from Paul worked!!, the problem is solved, thank you very much to all.

aviveros

ASKER

Thank you very much.