linking checkbox to a cell

[carlosz]

Hi, I need to create a form (checkbox type) with 567 checkboxes linked to 567 cells. I want to score a test that has that many questions. I tried using the no VB approach, but the hand cursor becomes a pain and I´ve been told the VB approach would be easier.

I have already designed the checkbox in Forms/UserForm1, it has 18 columns, from numbers 1 to 25 (vertical), 26 to 50, and so on, but I do not know how to link them to a cell. (Spent a long time doing that, (As you can tell, I am a real newby at this...) I am sure there is an easier way...

The idea is to have the value 1 if it is True and 0 if it is False. (if possible, to double click and have the value -1, which would mean that the person did not answer that question).

I´d really appreciate your help.
Carlosz

[anhn]

Have you ever seen a commercial program that has 100 check boxes on the same form? Maybe none or just a few.
567 check boxes on the same form are TOO MANY, split them to multiple groups, each group on each tab is better.

How to link an ActiveX Check box to an Excel cell:

Select a check box on your form, in property box find the item "LinkedCell", enter the address of the cell you want to link to such as A1 or $A$1 or more specific Sheet1!$A$1.

The default values of a checkbox is True and False, if you want triple values then find another item called "TripleState" in the same property box, set it to True.

Now the checkbox will have 3 states with 3 values linked to the cell that are TRUE, FALSE and #N/A.

I can imagine how painful to set 567 checkboxes one by one as above.

Please noted that: In VBA, True = -1 but on Excel cell, TRUE value is equivalent to 1. #N/A indicates an error, that is not easy to auto converted to a numeric value if not manipulated by code and in that case an auto linked cell cannot be set.

Also noted that, you can use ActiveX Checkbox (not Form CheckBox with hand cursor) directly on spreadsheet, instead of on a UserForm, that also can be linked to a cell the same way as above.

[carlosz]

Thank you very much for your help! It sure does make a difference

have a nice Sunday!!!

Carlos

[carlosz]

I found this code

[Sub AddCheckBoxes()
On Error Resume Next
Dim c As Range, myRange As Range
Set myRange = Selection
For Each c In myRange.Cells
ActiveSheet.CheckBoxes.Add(c.Left, c.Top, c.Width, c.Height).Select
With Selection
.LinkedCell = c.Address
.Characters.Text = ""
.Name = c.Address
End With
c.Select
With Selection
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=" & c.Address & "=TRUE"
.FormatConditions(1).Font.ColorIndex = 6 'change for other color when ticked
.FormatConditions(1).Interior.ColorIndex = 6 'change for other color when ticked
.Font.ColorIndex = 2 'cell background color = White
End With
Next
myRange.Select
End Sub]

which allowed me to create chekboxes linked to a cell using the "regular" Form checkbox. Is it possible to create a similar code that would do the same thing but with the VB or ActiveX checkbox? Would that enable me to use the "TripleState" you mentioned?
Thank you again for your patience.
Carlos




have a nice Sunday!!!

Carlos

[NeoDan]

Hey Carlos have you fixed this issue yet?

If not I had to build a similar form for one of my work projects...
the problem I had was too many checkboxes to go through and assign to the "linked cell" values.

I found this helps:

* name all your checkboxes generically... eg. checkbox001, checkbox00, etc.
use the VBA/activex control naming convention to set the "linked cell" values to whatever cell you want...eg.

activex example:
for i = 1 to 999
buttname = "checkbox" & i
Sheet1.OLEObjects(buttname).Object.linkedcell = <whatever cell you want>
next

forms control example:
for i = 1 to 999
buttname = "checkbox" & i
userform.Controls(buttname).linkedcell= <whatever cell you want> Then
next

then you can use whatever properties of the control you want
hope this helps...

but anhn is correct in saying that that many checkboxes on a form is excessive...


Cheers

[carlosz]

thank you, NeoDan.
Your help is greatly appreciated.