One Macro for Many check boxes

cadence

Well-known Member
Joined
Dec 6, 2005
Messages
528
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?
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
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

Well-known Member
Joined
Dec 6, 2005
Messages
528
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

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
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.
Code:
'=====================================================
'- 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

Well-known Member
Joined
Dec 6, 2005
Messages
528
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.

Code:
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
 

Forum statistics

Threads
1,181,607
Messages
5,930,881
Members
436,764
Latest member
avalladarez

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top