Can't check ActiveX checkbox on worksheet

  • Thread starter Thread starter onedaywhen
  • Start date Start date
O

onedaywhen

I'm trying to set the values of ActiveX checkboxes that are on a
worksheet.

Here's my code:

For Each ctl In Sheet1.OLEObjects
If TypeOf ctl.Object Is MSForms.CheckBox Then
ctl.Object.Value = True
End If
Next

If I go back and programmatically check the value properties (i.e. in
the Immediate window) they are confirmed as being set to true. The
problem is the checkboxes looks unchecked i.e. there are no ticks!

Anyone know what's going wrong? I'm using Excel2002 and Excel2003
beta.

Thanks.
 
If you close (and save) your workbook, then reopen, does it look ok?

How about the same closing the application?

I've seen some display problems (not often and not with checks in checkboxes)
that seem to resolve themselves with as little as a windows minimize/maximize
(or close/reopen).

And are you sure you're getting the correct checkboxes?

Maybe you could double check by adding something like this in your loop.

MsgBox ctl.TopLeftCell.Address
ctl.Visible = True
 
Dave,
Thanks for the reply. I take you point but I'm sure I'm looking at the
correct checkboxes. I must be suffering the same display problems as
you have done.

What I'm ultimately doing is dynamically populating the checkboxes
with boolean data from a remote source when the user selects a row in
a combobox. Therefore, closing and opening
workbooks/windows/applications isn't an option.

I've switched to using the other variety of checkboxes i.e. from the
Forms toolbox. The nice things is that you get the proper link between
cell and control i.e. changing the cell value actually checks the
checkbox as well as checking the checkbox updating the cell value.
The down side is the Worksheet_Change event doesn't fire in the latter
case and I'm having to link to a public sub in a standard module -
yuk!
 
I really meant to just try it once (manually) to see if it had any affect on the
display. It might have been useful to verify that it wasn't your code's
problem.

And I've seen DoEvents peppered in some code--but that never seemed to help me
with my display problems.

But it sounds like you got a solution, so that's good.
 
Back
Top