Check Boxes

A

Adam Kroger

Is there an "easy" way to add check boxes to a worksheet? As it stands, I
have to manually attach it to the underlying cell, and adjust the size. I
would like to add several hundred checkboxes. Am I doing somethig wrong, or
is it just that akward and labor intensive in Excel?
 
N

Norman Jones

Hi Adam,

Try:

'=============>>
Public Sub Tester01()
Dim SH As Worksheet
Dim rng As Range
Dim rCell As Range

Set SH = ActiveSheet '<<==== CHANGE

Set rng = SH.Range("A1:A100") '<<==== CHANGE

Application.ScreenUpdating = False
For Each rCell In rng
With SH.CheckBoxes.Add(rCell.Left + 5, _
rCell.Top - 2, 5, 5)
.Caption = ""
.LinkedCell = rCell.Address(0, 0)
End With
rCell.Font.Color = vbWhite
Next rCell
Application.ScreenUpdating = True

End Sub
'<<=============

If you are new to macros, you may wish to visit David McRitchie's 'Getting
Started With Macros And User Defined Functions' at:

http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
A

Adam Kroger

A couple of questions:

1: It is a macro correct? I manually run it when I am ready?

2: I assume that I should place the check boxes, then run the macro (after
adjusting it for sheet and range). Correct? Then reedit it, and run it for
the next sheet, and so on....

3: Will it link the size of the check boxes to the size of the cell under
it? If not is there a way to do this so that they automatically resize with
their corresponding cells.?
 
N

Norman Jones

Hi Adam,
1: It is a macro correct? I manually run it when I am ready? Yes.

2: I assume that I should place the check boxes
Click to expand...
No. The macro adds a checkbox into each cell in the designated range.
, then run the macro (after adjusting it for sheet and range). Yes.

3: Will it link the size of the check boxes to the size of the cell under
it?
Click to expand...

Yes.


To see just what the macro does, open a new workbook, run the macro and
check the results. You can then close the workbook withot saving.

If you want to apply the macro to different ranges or sheets, you may wish
to change the line:

to:

Set rng = Selection.

In this case, manually select the required range(s) before running the
macro.
 
A

Adam Kroger

I tried to run it as a macro but it hung at teh sheet name. My sheets are
named 'Pilot 1' , 'Pilot 2' , 'Pilot 3', ... , 'Pilot 12' The checkboxes are
in the range W1:W321
 
A

Adam Kroger

What about the line where you set the sheet? Is an edit needed there as
well to insert the checkboxes by selecting cells?
 
N

Norman Jones

Hi Adam,

Firstly, the macro is designed to add checkboxes to each cell in the
designated range

If you want to apply the macro to, say, the Pilot 1 sheet, you could change:

to:

Set SH = Sheets("Pilot1")

However, if your intention is to add checkboxes to various sheets, you could
leave the above line unchanged, in which case the macro will operate on
whichever sheet you select.

Alternatively again, change:

to:

Set rng = Selection

as suggested in my reponse to your preceding post.
 
A

Adam Kroger

I figured out the change on the sheet (and mighty proud of myself for that
as well :)) However, it is droping the checkboxes 2 rows below the linked
cell.
 
N

Norman Jones

Hi Adam,
What about the line where you set the sheet? Is an edit needed there as
well to insert the checkboxes by selecting cells?
Click to expand...

No cahange is required as the selection will always be on the active sheet.
 
N

Norman Jones

Hi Adam.

Try my earlier suggestion of running the macro in a blank (new?) worksheet:
this will show you exactly what the macro does. as written, the macro
assumes no checkboxes exist in the selected range.
 
A

Adam Kroger

Norman:

I have discovered a problem. For some reason, whne I use the function
inside my worksheet, the checkboxes keep dropping lower in the cells as I
add them into the sheet.

when I add it to cells W12:W24 the alignment is good. By the time I am
adding them to W93:W105 they are dropping down to the next row. This was
not happening when I was experimenting on it in a blank workbook, or on a
blank worksheet within this workbook.

Obviously there is some sort of formatting or setting on this worksheet that
is causing this. Any idea what that could be?

Adam
 
Top