Programmically setting Excel checkboxs(Forms Shape object)

[Alpha1]

I have an existing Excel spreadsheet created by a 3rd party that
contains multiple checkboxs created with forms toolbox as a shape
object. I would appear I cannot set the value of the check box so it
gets checked programically. I can call the value of the checkbox using
objshape.controlformat.value to see if it has a value of 1 for checked
or -4146 for unchecked, but not set it.

I have been searching for an answer to this but to date the resolution
has evaded me.
I'm hoping somebody has a solution to this either directly with a
vbscript or calling a vba function embedded in the excel worksheet.

The preferred option is a vbscript checking the boxes directly since I
had additional code running on the spreadsheet but any solution would
be appreciated.

many thanks in advance..

 

[Leith Ross]

Hello Alpah1,

To Set the CheckBox...
Worksheets("Sheet1").Shapes("Check Box 1").ControlFormat.Value = True

To Clear the CheckBox...
Worksheets("Sheet1").Shapes("Combo Box 1").ControlFormat.Value = False

Change the Worksheet name and checkbox name if they are different in
your code.

Sincerely,
Leith Ross

 

[Robert Bruce]

I have an existing Excel spreadsheet created by a 3rd party that
contains multiple checkboxs created with forms toolbox as a shape
object. I would appear I cannot set the value of the check box so it
gets checked programically. I can call the value of the checkbox using
objshape.controlformat.value to see if it has a value of 1 for checked
or -4146 for unchecked, but not set it.

I have been searching for an answer to this but to date the resolution
has evaded me.
I'm hoping somebody has a solution to this either directly with a
vbscript or calling a vba function embedded in the excel worksheet.

The preferred option is a vbscript checking the boxes directly since I
had additional code running on the spreadsheet but any solution would
be appreciated.

What is the code that is not working?

To check a Forms checkbox I do something like this:

thisworkbook.Worksheets("Form").CheckBoxes("chkMyCheckbox").value = xlon

where xlOn is a built-in Excel constant. In order to convert from the
somewhat confusing Excel constants to boolean values, I use the following
functions:

Public Function CheckedToBool(Checked As Long) As Boolean
CheckedToBool = (Checked = xlOn)
End Function
Public Function BoolToChecked(Checked As Boolean) As Long
If Checked Then BoolToChecked = xlOn Else BoolToChecked = xlOff
End Function

so that the code becomes:

thisworkbook.Worksheets("Form").CheckBoxes("chkMyCheckbox").value =
BoolToChecked(true)

HTH

Rob

 

[Alpha1]

What is the code that is not working?

To check a Forms checkbox I do something like this:

thisworkbook.Worksheets("Form").CheckBoxes("chkMyCheckbox").value = xlon

where xlOn is a built-in Excel constant. In order to convert from the
somewhat confusing Excel constants to boolean values, I use the following
functions:

Public Function CheckedToBool(Checked As Long) As Boolean
CheckedToBool = (Checked = xlOn)
End Function
Public Function BoolToChecked(Checked As Boolean) As Long
If Checked Then BoolToChecked = xlOn Else BoolToChecked = xlOff
End Function

so that the code becomes:

thisworkbook.Worksheets("Form").CheckBoxes("chkMyCheckbox").value =
BoolToChecked(true)

HTH

Rob

First many thanks for the response and I'm now able to check the box,
but like all good programming you fix one issue to find another. The
code validates a task and for each task there are 3 checkboxes called
Checked,Passed & Exempt. if the Passed is checked then the Exempted is
greyed out and the oposite applies.

Now that I can check the box with help you guys provided but it appears
that there is a macro that runs behind the check boxes that verifies
the onclick event. However I don't seem to be able to access the
properties of the checkboxes on the form object to remove the on click
event, despite disabling the macro it still attempts to call the Macro
named "AnyCheckedBoxClick."

(I wish who ever contructed the code had just used the checkbox from
the Tools and not the Form shape object.)

If any body has time to respond to this I would appreciate the help. I
need to disable the onclick event preferably not by reassigning a dummy
macro that does nothing. I have 150 checkboxes that calls this event if
clicked on.


Once again to Ross & Robert for their time in responding to my original
posting. Great work Guys.

 

[Robert Bruce]

Now that I can check the box with help you guys provided but it
appears that there is a macro that runs behind the check boxes that
verifies the onclick event. However I don't seem to be able to access
the properties of the checkboxes on the form object to remove the on
click event, despite disabling the macro it still attempts to call
the Macro named "AnyCheckedBoxClick."

Create a global boolean variable. Call it something like
blnValidationDisabled.

Add a check for the status of the variable at the start of your
AnyCheckedBoxClick code:

Sub AnyCheckedBoxClick()
if not blnValidationDisabled then
<do validation>
end if
end sub

Now when you programatically check or unckeck the checkbox, you can set the
varaible:

blnValidationDisabled = true
<code to alter the checked state of the checkbox>
blnValidationDisabled = false

HTH

Rob