One Macro for Many check boxes

[cadence]

Hello

I am trying to devise a macro so that one a check box is activated its Value in A1 = C1, and when uncheck its empty.

eg: A1=IF(checkbox1= True,C1,"")

How can I write this so that I have a two ranges of cell each with its own check box so
CheckBox1(A1 = C1), CheckBox2 (A2 = C2) ..... CheckBox10 (A10=C10) and then onther range
CheckBox1 (E1 = G1), CheckBox2 (E2 = G2) ..... CheckBox1 (E10=G10)

All so they operate from the same Macro, but with there own check box?

Thanks, if a single macro is not possible perhaps someone could show simple way of setting it up for many checkboxes.

PS is there any limitation on check box, does excel slow down if you have too many or somthing like that?

 

[BrianB]

1. If you right click the check box then click Format Control you get the option to set a cell link that will change depending on the state of the control. You can then use this in your formulas.

eg. using column Z (which can be hidden)........
(A1) =IF(Z1= True,C1,"")
(E1) =IF(Z1= True,G1,"") -I think this is what you mean (?)
'--------------------------------------------------------------------
2. Re limitations
I suggest you use check boxes from the Forms Toolbar if possible.
Lots of controls from the Controls Toolbar can sometimes give problems (more features,more bugs)

As far as I know the only thing that might be affected is how long the workbook takes to open & close.
'--------------------------------------------------------------------
3. You might like to have a look at my macro code here, which saves making lots of checkboxes.

http://www.mrexcel.com/board2/viewtopic.php?t=154562&highlight=


To change it for ticks in column Z change the line
If (ActiveCell.Column = 2 Or ActiveCell.Column = 5) _
to
If (ActiveCell.Column = 26) _

You probably do not need to bother with the "Check for Ticks" macro which would need slight amendment. To change your formulas, the tick cells are either empty (no tick) or contain text (tick), so :

(A1) =IF(Z1= "","",C1)
(E1) =IF(Z1= "","",G1)

 

[cadence]

Thanks Brian B

I have thought about doing it that way, but just thought it would be better to get something a little more cleaner adn a little more universal.

I was experimetning with something along these lines, (yet to get it so work)

Private Sub CheckBox1_Click()
If CheckBox1 = True Then
Range("A1").Value = Range("C1").Value
Else
Range("A1").Value = ""
End If
End Sub

but obviously is needs a lot more work![/quote]

 

[BrianB]

That way you have to write a separate macro for each box.
Here is some code to add checkboxes and link to the cell underneath.

'=====================================================
'- MACRO TO ADD CHECKBOX TO WORKSHEET ROWS
'- LINKED TO UNDERLYING CELL
'- Brian Baulsom July 2007
'=======================================================

Sub ADD_CHECKBOXES()
    Dim ToRow As Long
    Dim MyCell As Range
    '--------------------------------------------------------------------------
    Application.ScreenUpdating = False
    '- worksheet
    With ActiveSheet.Cells
        .Font.Name = "Arial"
        .Font.Size = 8
        .Columns(1).ColumnWidth = 4
        .Rows.RowHeight = 15
    End With
    '---------------------------------------------------------------------------
    For ToRow = 2 To 20
        '- Format Cell
        Set MyCell = ActiveSheet.Cells(ToRow, 1)
        With MyCell                         ' make text invisible
            .Interior.ColorIndex = xlNone   ' no colour background
            .Font.ColorIndex = 2            ' white text
        End With
        '-----------------------------------------------------------------------
        '- Format CheckBox
        ActiveSheet.checkboxes. _
            Add(MyCell.Left, MyCell.Top, MyCell.Width, MyCell.Height).Select
            n = "CB" & CStr(ToRow)
        With Selection
            .Name = n
            .Caption = ""               'no caption
            .Value = xlOff
            .LinkedCell = MyCell.Address
        End With
    Next
    '----------------------------------------------------------------------------
    Range("a1").Select
    Application.ScreenUpdating = True
    MsgBox ("Done")
 End Sub
 '------------------------------------------------------------------------------

 

[cadence]

You must be Psychic , I was just going to see about and check box maker and then you replied.


I have amended your code to lose the formatting of the cells and added some line to stop it from printing and make the text boax as small as possible.

Sub ADD_CHECKBOXES()
Dim ToRow As Long
Dim MyCell As Range
'--------------------------------------------------------------------------
Application.ScreenUpdating = False

For ToRow = 5 To 26
'- Format Cell
Set MyCell = ActiveSheet.Cells(ToRow, 32)
With MyCell ' make text invisible
.Interior.ColorIndex = xlNone   ' no colour background
.Font.ColorIndex = 2    ' white text
End With

'- Format CheckBox
ActiveSheet.CheckBoxes. _
Add(MyCell.Left, MyCell.Top, MyCell.Width, MyCell.Height).Select
n = "CB" & CStr(ToRow)
With Selection
'.Name = ""
.Characters.Text = ""
.ShapeRange.ScaleWidth 0.25, msoFalse, msoScaleFromTopLeft
.PrintObject = False
.Caption = "" 'no caption
.Value = xlOff
.LinkedCell = MyCell.Address
End With
Next

Range("AF5").Select
Application.ScreenUpdating = True
MsgBox ("Done")
End Sub

I just have two questions

How can I make the Cell Link Worskhseet Rate!$C$5,Rate!$C$6, ... and not the cell that the check box is going in.

also how can I make teh checkbox Right Align in the cell, I have been trying but only get errors.

this will save me loads of hassle and good to know that its a common annoyance!

thanks