Auto Assign Check boxes

[sixhobbits]

Hi

I am trying to create a worksheet on excel which uses a large amount of checkboxes. I am looking for a way to automatically 'assign' these checkboxes to a cell, e.g. when I put a checkbox in Cell A1, the true/false appears in cell B1; when Checkbox is created in (or moved to)cell A2, true/false appears in cell B2

Can anyone help

Sixhobbits

Windows 7 home basi
Microsoft office Suite 200
Only very basic knowledge of VB

 

[Dave Peterson]

I would use a macro that added the checkboxes from the Forms toolbar (not the
control toolbox toolbar) and assigned the linked cell when the checkboxes were
added.

If you want to try:

Option Explicit
Sub CellCheckbox()
Dim myCell As Range
Dim myRng As Range
Dim CBX As CheckBox

With ActiveSheet
.CheckBoxes.Delete 'nice for testing!
Set myRng = .Range("A1:A10") 'change to the range you want
End With

For Each myCell In myRng.Cells
With myCell
Set CBX = .Parent.CheckBoxes.Add _
(Top:=.Top, _
Left:=.Left, _
Width:=.Width, _
Height:=.Height)
CBX.Name = "CBX_" & .Address(0, 0)
CBX.Caption = "" 'or what you want
CBX.Value = xlOff

'I like to use the same cell as the linked cell
'with a number format of ;;;
'I can't see the true/false in the worksheet, but it
'still appears in the formula bar
CBX.LinkedCell = .Address(external:=True)
.NumberFormat = ";;;"
End With
Next myCell
End Sub

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

 

[Gord Dibben]

Since OP wanted the linked cells in adjacent column B make change to this
line as showm.

CBX.LinkedCell = .Offset(0, 1).Address(external:=True)



Gord

 

[Dave Peterson]

And probably change the numberformat line, too:
cbx.offset(0,1).NumberFormat = ";;;"

But personally, I'd use the same cell--with that hidden formatting (;;, it
seems like a better (to me) solution.

 

[Gord Dibben]

If OP has no reason to link to column B then your original is the way to go.


Gord

 

[sixhobbits]

Thanks guys, exactly what I was looking for

Can't get the ;;; 'hidden formatting' thing right. I have no reason to display the true/false in a different cell. Do I right click on the cells- Format Cells- Custom Format and then Type ";;;". This doesn't seem to display the true/false in the checkbox cell for me

Thanks again

six

 

[sixhobbits]

oh right, I see you added the format ";;;" into the macro. That works great..

One more thing, for aesthetic reasons: How would I have the the checkboxes created with '3-D shading' (Done manually by right clicking on a checkbox and clicking 'Format Control'

Thanks again

Si

 

[Dave Peterson]

You can add another line of code:

CBX.Display3DShading = True

 

[Nick Thomas]

I wanted to do this, but with radial buttons. What would I need to change to make this give back formatted radial buttons and to have them in sets that require one of the group and only one to be chosen?

Thanks!

 

[Jim Cone]

Try replacing ".CheckBoxes" with "OptionButtons"
--
Jim Cone
Portland, Oregon USA
http://tinyurl.com/ExtrasForXL add-in

..
..
..

"Nick Thomas" <[email protected]>
wrote in message
I wanted to do this, but with radial buttons.
What would I need to change to make this give back formatted radial buttons
and to have them in sets that require one of the group and only one to be chosen?
Thanks!

 

[Dave Peterson]

If you're creating a survey form, you may want to look at Debra Dalgleish's
site:
http://contextures.com/xlForm01.html