checkbox code makes excel sloowwwww...
 

I have 20 checkboxes that I do not want users to be able to change.
They are grouped with about 130 other checkboxes that users can
change; the checked-or-unchecked status of the 20 checkboxes is
controlled elsewhere by setting the linked cell values to TRUE or
FALSE. However, I want the checkboxes to have the same appearance of a
normal checkbox.

I have tried the following code with one of the checkboxes:

Private Sub cBoxGR1_Click()

cBoxGR1.Value = Not cBoxGR1.Value

End Sub

It works, but when I check on the checkbox (whether checked or
unchecked), the box becomes grayed out, and a message in the status
bar says "Calculating Cells: 100%" - this lasts for several seconds
and the workbook is inaccessible. Then the workbook comes back to
life, and the grayed out appearance goes away. I've tried an
if..then...else variation with the same result. I don't have any
Worksheet_Calculate code, or anything else like that.

Any ideas why the worksheet is taking so long to recalculate? Other
options to accomplish the same goal?

Please reply to the group, the email I posted with is <ahem designed
to gather SPAM.

Thanks,
Scott

checkbox code makes excel sloowwwww...
 

Hi Scott

Edit it to this

Private Sub cBoxGR1_Click()
cBoxGR1.Value = Not cBoxGR1.Value
MsgBox "I am clicked and my value is " & cBoxGR1.Value
End Sub

and test it. You may see why it's slow. It runs itself.

HTH. Btst wishes Harald

"Scott" skrev i melding
om...
I have 20 checkboxes that I do not want users to be able to change.
They are grouped with about 130 other checkboxes that users can
change; the checked-or-unchecked status of the 20 checkboxes is
controlled elsewhere by setting the linked cell values to TRUE or
FALSE. However, I want the checkboxes to have the same appearance of a
normal checkbox.

I have tried the following code with one of the checkboxes:

Private Sub cBoxGR1_Click()

cBoxGR1.Value = Not cBoxGR1.Value

End Sub

It works, but when I check on the checkbox (whether checked or
unchecked), the box becomes grayed out, and a message in the status
bar says "Calculating Cells: 100%" - this lasts for several seconds
and the workbook is inaccessible. Then the workbook comes back to
life, and the grayed out appearance goes away. I've tried an
if..then...else variation with the same result. I don't have any
Worksheet_Calculate code, or anything else like that.

Any ideas why the worksheet is taking so long to recalculate? Other
options to accomplish the same goal?

Please reply to the group, the email I posted with is <ahem designed
to gather SPAM.

Thanks,
Scott

checkbox code makes excel sloowwwww...
 

Why struggle. Set the enabled property to false and let it look grayed out.
Why toy with the user? If they see it is grayed out, they will know they
can't select it.

--
Regards,
Tom Ogilvy

"Harald Staff" wrote in message
...
Hi Scott

Edit it to this

Private Sub cBoxGR1_Click()
cBoxGR1.Value = Not cBoxGR1.Value
MsgBox "I am clicked and my value is " & cBoxGR1.Value
End Sub

and test it. You may see why it's slow. It runs itself.

HTH. Btst wishes Harald

"Scott" skrev i melding
om...
I have 20 checkboxes that I do not want users to be able to change.
They are grouped with about 130 other checkboxes that users can
change; the checked-or-unchecked status of the 20 checkboxes is
controlled elsewhere by setting the linked cell values to TRUE or
FALSE. However, I want the checkboxes to have the same appearance of a
normal checkbox.

I have tried the following code with one of the checkboxes:

Private Sub cBoxGR1_Click()

cBoxGR1.Value = Not cBoxGR1.Value

End Sub

It works, but when I check on the checkbox (whether checked or
unchecked), the box becomes grayed out, and a message in the status
bar says "Calculating Cells: 100%" - this lasts for several seconds
and the workbook is inaccessible. Then the workbook comes back to
life, and the grayed out appearance goes away. I've tried an
if..then...else variation with the same result. I don't have any
Worksheet_Calculate code, or anything else like that.

Any ideas why the worksheet is taking so long to recalculate? Other
options to accomplish the same goal?

Please reply to the group, the email I posted with is <ahem designed
to gather SPAM.

Thanks,
Scott

checkbox code makes excel sloowwwww...
 

Oops. Thanks Tom. I missed the "I have 20 checkboxes that I do not want
users to be able to change" part, probably because it's meaningless to me.
Imo those boxes shouldn't be there. But enabled = false is definitely a good
option if "this is for the big guys and you're definitely not one of them"
is an important message for the users.

Best wishes Harald

"Tom Ogilvy" skrev i melding
...

Why struggle. Set the enabled property to false and let it look grayed
out.
Why toy with the user? If they see it is grayed out, they will know they
can't select it.

checkbox code makes excel sloowwwww...
 

Thanks, Harald and Tom.

I'll probably go with Harald's suggestion - remove the checkboxes and
use some other visual indicator. I wanted to retain a consistent
appearance within the table of checkboxes (where a user selects one or
more practices, via the checkboxes, that apply to up to 10 distinct
land areas), but for these particular 2 practices, the user provides
input elsewhere in the workbook that amounts to selecting the
practice, and I want to report it somehow in the checkbox table. Not
sure if this makes sense without going into great, boring detail.
Anyway, imho the appearance of the grayed-out checkboxes can be
confusing to an audience who isn't used to seeing them. Thanks again
for your input. Yeah....recursive code without end does slow things
down a bit! <duh

-Scott



"Harald Staff" wrote in message ...
Oops. Thanks Tom. I missed the "I have 20 checkboxes that I do not want
users to be able to change" part, probably because it's meaningless to me.
Imo those boxes shouldn't be there. But enabled = false is definitely a good
option if "this is for the big guys and you're definitely not one of them"
is an important message for the users.

Best wishes Harald