A Whole Slew of CheckBoxes
 

Good afternoon --
I'm creating a timesheet to record time spent on different projects. Over
the course of a day, an individual might have over 10 entries, so the sheet
might have couple hundred lines by the end of a month -- one entry to a line.

I've written a little checkbox to do a few simple things with the info on
the line, and now need to copy that checkbox down to the couple hundred lines
below.

Do I have to do that manually (assisted liberally with cut-and-paste) or
have the many people more clever than I found an easier way to do it?

Thanks in advance.

A Whole Slew of CheckBoxes
 

This sounds like a management nightmare, with multiple users and hundreds of
check boxes. I would probably do the following:

1. Adjust the width of an appropriate column so that the cells are square.
2. Unlock the cells.
3. Adjust the vertical and horizontal alignment to Center.
4. Change the font name to Marlett.
5. Change the font size to, say, 10.
6. Add a rectangle to the worksheet from the Drawing toolbar.
7. Adjust the shape of the rectangle so that it is very long and narrow. It
must fit inside the above range of cells. It should be noticeably narrower
than the range of cells so that there is a little margin on both sides.
8. Make the rectangle invisible by selecting No Fill and No Line options
(Format Autoshape dialog).
9. Hide the column and row headers so that they don't blink (ToolsOptions).
10. Paste the following code to a standard module.
11. Assign the below "TogCheckBox" macro to the rectangle.
12. Protect the worksheet. All entry cells must be unlocked.

You should be able to toggle a checkmark by clicking over the (square) cells
in the above column. You will need additional code to do whatever it is you
want. You need only one rectangle to simulate any amout of check boxes.

This method is typically done using the Selection_Change event which I don't
like. Using the rectangle allows execution without selecting the cells and
executes with the left mouse button the same as normal check boxes. One
problem is that the black border of the selected cell blinks when toggling
the ckeckmarks. If you don't want this, you can hide it by formating a range
of cells dark gray and adding code that selects a cell inside this range.
Alternatively, suppress screen updating and select outside of the visible
range. The code should only do this if the cell is not already selected in
order to limit flicker caused by toggling the screen updating.

Code follows:-


Declare Function GetCursorPos Lib "user32.dll" (ByRef lpPoint As POINTAPI)
As Long
Type POINTAPI
x As Long
Y As Long
End Type

Sub TogCheckMark()
Dim c As Range
Dim cp As POINTAPI

GetCursorPos cp
With ActiveSheet.Shapes(Application.Caller)
.Visible = False
Set c = ActiveWindow.RangeFromPoint(cp.x, cp.Y)
.Visible = True
End With
If c.Value = "a" Then c.Value = "" Else c.Value = "a"
Set c = Nothing
End Sub

Greg

A Whole Slew of CheckBoxes
 

On Nov 29, 9:33 pm, Greg Wilson
wrote:
This sounds like a management nightmare, with multiple users and hundreds of
check boxes. I would probably do the following:

1. Adjust the width of an appropriate column so that the cells are square.
2. Unlock the cells.
3. Adjust the vertical and horizontal alignment to Center.
4. Change the font name to Marlett.
5. Change the font size to, say, 10.
6. Add a rectangle to the worksheet from the Drawing toolbar.
7. Adjust the shape of the rectangle so that it is very long and narrow. It
must fit inside the above range of cells. It should be noticeably narrower
than the range of cells so that there is a little margin on both sides.
8. Make the rectangle invisible by selecting No Fill and No Line options
(Format Autoshape dialog).
9. Hide the column and row headers so that they don't blink (ToolsOptions).
10. Paste the following code to a standard module.
11. Assign the below "TogCheckBox" macro to the rectangle.
12. Protect the worksheet. All entry cells must be unlocked.

You should be able to toggle a checkmark by clicking over the (square) cells
in the above column. You will need additional code to do whatever it is you
want. You need only one rectangle to simulate any amout of check boxes.

This method is typically done using the Selection_Change event which I don't
like. Using the rectangle allows execution without selecting the cells and
executes with the left mouse button the same as normal check boxes. One
problem is that the black border of the selected cell blinks when toggling
the ckeckmarks. If you don't want this, you can hide it by formating a range
of cells dark gray and adding code that selects a cell inside this range.
Alternatively, suppress screen updating and select outside of the visible
range. The code should only do this if the cell is not already selected in
order to limit flicker caused by toggling the screen updating.

Code follows:-

Declare Function GetCursorPos Lib "user32.dll" (ByRef lpPoint As POINTAPI)
As Long
Type POINTAPI
x As Long
Y As Long
End Type

Sub TogCheckMark()
Dim c As Range
Dim cp As POINTAPI

GetCursorPos cp
With ActiveSheet.Shapes(Application.Caller)
.Visible = False
Set c = ActiveWindow.RangeFromPoint(cp.x, cp.Y)
.Visible = True
End With
If c.Value = "a" Then c.Value = "" Else c.Value = "a"
Set c = Nothing
End Sub

Greg

Hello pdberger,

Here is alternate method. This will add checkboxes to each cell in
your selection , center the control, and assign a macro to it. Copy
this code into a Standrd VBA module. Seelct the cells you check boxes
in (they don't have to be contiguous), and run the macro by using ALT
+F8 to display the Macro Run dialog. Select AddCheckBoxesToSelection
and click OK.

'Written: November 29, 2007
'Author: Leith Ross
'Summary: Adds a Forms Checkbox to a cell, centers the control, and
assigns a macro to it.

Sub AddCheckBoxToCell(Ref_cell As Range, Macro_name As String,
Optional Caption As String)

Dim ChkBox As CheckBox
Dim Wks As Worksheet

With Ref_cell.Cells(1, 1)
refLeft = .Left
refTop = .Top
refHeight = .Height
End With

Set Wks = Worksheets(Ref_cell.Parent.Name)
Set ChkBox = Wks.CheckBoxes.Add(15, 15, 15, 15)

N = (refHeight - ChkBox.Height) / 2

With ChkBox
.Caption = Caption
.Top = refTop + N
.Left = refLeft
.OnAction = Macro_name
End With

End Sub

Sub AddCheckBoxesToSelection()

Dim RefCell As Range

For Each RefCell In Selection
AddCheckBoxToCell RefCell, "MyMacro"
Next RefCell

End Sub

Sincerely,
Leith Ross