Auto Assign Check boxes

  • Thread starter Thread starter sixhobbits
  • Start date Start date
S

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
 
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.)
 
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
 
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.
 
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
 
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
 
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!
 
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!
 
Back
Top