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
Visual Basic ClassicVB Script
Last Comment
aviveros
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
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
'(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
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
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
HI
In VB you are aware that you need to bind to Excel first?
See below
Krystian
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..
ASKER
The last example from Paul worked!!, the problem is solved, thank you very much to all.
ASKER
Thank you very much.
To work with CheckBoxes in Excel its the following
Regards
Krystian
Open in new window