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?

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


---
Regards,
Norman


"Adam Kroger @hotmail.com" <adam_kroger<nospam wrote in message
...
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?

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.?


"Norman Jones" wrote in message
...
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


---
Regards,
Norman


"Adam Kroger @hotmail.com" <adam_kroger<nospam wrote in message
...
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?

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
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?

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:

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

to:

Set rng = Selection.

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


---
Regards,
Norman


"Adam Kroger @hotmail.com" <adam_kroger<nospam wrote in message
...
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.?

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


"Norman Jones" wrote in message
...
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


---
Regards,
Norman


"Adam Kroger @hotmail.com" <adam_kroger<nospam wrote in message
...
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?

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


"Norman Jones" wrote in message
...
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
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?

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:

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

to:

Set rng = Selection.

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


---
Regards,
Norman


"Adam Kroger @hotmail.com" <adam_kroger<nospam wrote in message
...
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.?

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:

Set SH = ActiveSheet

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:

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

to:

Set rng = Selection

as suggested in my reponse to your preceding post.


---
Regards,
Norman


"Adam Kroger @hotmail.com" <adam_kroger<nospam wrote in message
...
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

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.

"Norman Jones" wrote in message
...
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
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?

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:

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

to:

Set rng = Selection.

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


---
Regards,
Norman


"Adam Kroger @hotmail.com" <adam_kroger<nospam wrote in message
...
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.?

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?

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

---
Regards,
Norman


"Adam Kroger @hotmail.com" <adam_kroger<nospam wrote in message
...
What about the line where you set the sheet? Is an edit needed there as
well to insert the checkboxes by selecting cells?


"Norman Jones" wrote in message
...
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
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?

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:

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

to:

Set rng = Selection.

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


---
Regards,
Norman


"Adam Kroger @hotmail.com" <adam_kroger<nospam wrote in message
...
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.?

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.

---
Regards,
Norman



"Adam Kroger @hotmail.com" <adam_kroger<nospam wrote in message
...
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.

"Norman Jones" wrote in message
...
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
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?

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:

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

to:

Set rng = Selection.

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


---
Regards,
Norman


"Adam Kroger @hotmail.com" <adam_kroger<nospam wrote in message
...
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.?

I've been playing with it for awhile I LIKE IT :)

thank you

"Norman Jones" wrote in message
...
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:

Set SH = ActiveSheet

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:

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

to:

Set rng = Selection

as suggested in my reponse to your preceding post.


---
Regards,
Norman


"Adam Kroger @hotmail.com" <adam_kroger<nospam wrote in message
...
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

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


"Norman Jones" wrote in message
...
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:

Set SH = ActiveSheet

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:

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

to:

Set rng = Selection

as suggested in my reponse to your preceding post.


---
Regards,
Norman


"Adam Kroger @hotmail.com" <adam_kroger<nospam wrote in message
...
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