Please Help - Checkbox Data Validation question!!!

[Guest]

Good Afternoon,

I working on an Excel Sheet that I’m setting up as a Test. I have set-up
Checkboxes using the Tools/Forms option that I’m linking to corresponding
cells (the cells reflect a True or False value dependant on if they are
checked or not).

I found a formula which I’ve pasted below that will Clear all the check
boxes and works great, but I also need a formula that will set up a Data
Validation/ Constraint for each group of questions. Since I cannot use the
typical Data Validation because no one is actually clicking the cells itself
– is there a way to monitor just the checkboxes themselves? For example, I
have a group of 5 checkboxes in Cells B15, B16, B17, F15 and F16 which all
have different answers to a related question. What I need is a way to make
sure the User does NOT check 2 Boxes.


Any ideas would be greatly appreciated and thank you in advance for your
thoughts – Jenny B.

 

[Guest]

Sub Uncheckboxes()
Dim wks As Worksheet
Dim ckbx As OLEObject
Set wks = Worksheets("background")
With wks
.CheckBoxes.Value = False 'Form type clear
For Each ckbx In wks.OLEObjects
ckbx.Object.Value = False 'ActiveX type clear
Next ckbx
End With
End Sub

 

[Rick Rothstein \(MVP - VB\)]

It appears your CheckBoxes came from the Forms toolbar (as opposed to them
being ActiveX controls). Given that, the Uncheckboxes subroutine can be
replaced with this single line...

Worksheets("background").CheckBoxes.Value = False

or, if you code is executing from the same worksheet as the CheckBoxes are
on...

ActiveSheet.CheckBoxes.Value = False

This solution is modeled after a post Dave Peterson offered on Wed, 18 Jul
2007 11:07:30 (-0500) on the microsoft.public.excel.programming newsgroup
(Subject: Visual Basic code). In that posting, Dave was deleting Form
Toolbar generated Option buttons. He also warned that the deletions wouldn't
work if there were too many (without specifying how many that might be)
OptionButtons on the worksheet. I am not sure whether this limit would apply
to the above or not, but I test it out for 100 CheckBoxes and it worked
fine.

I would have provided a link to the posting, but Google isn't showing any
posts after July 17th right now. Didn't Google used to be only several hours
behind physical postings in the past? Is Google broken or, perhaps, just
over-extended? Anyone else seeing these kind of delays in Google?

Rick

 

[Rick Rothstein \(MVP - VB\)]

What I need is a way to make

sure the User does NOT check 2 Boxes.

Why aren't you using Option Buttons then?

Rick

 

[Guest]

Hi Rick,

I’m not using the Option Button because I’m not that familiar with it (also
it does not have a simpleton link to each cell like the checkbox).
Additionally, I’m trying to capture what each cell/checkbox reads out after
selected. This means if I have a group of 4 answers – I’m looking to note the
answer selected to another page. So if someone chooses answer (checkbox 2),
I’ve also set-up a formula on another page that captures that data for later
review. That’s why I was looking to put the constraints on the question
group. If someone checks more than one box, it pulls over multiple answers
and that screws up my data collection.

Is there any quick Macro you could help me with (still using the checkboxes)
that could assign each check box a value (for instance checkbox1 = 1 etc)?
That way, I could set up a validation Macro that would somehow state that >1
would bring up a msgbox letting them know they can only select one from the
group.

Thanks so much for your help and appreciate any further advice – Jenny B.