Multiple checkboxes, one macro?

[pkohler]

Okay,

So I'm at a loss. I am trying to create a project checklist (why we
don't use MS Project is a mystery to me).

The problem is that I need to put a checkbox for each item (Row) on the
sheet, and when that checkbox is clicked I need it to time stamp the
neighboring cell. I have no problem doing it one cell at a time. The
issue is that using my current method I would have to write a macro for
each checkbox (over 500 total).

I am using the checkbox from the forms toolbar not the control toolbar.
Any suggestions?

Thanks in advance!!!

 

[Sibilia]

May be you could program your macro using a "For each.....Next loop"

I don't know how is your sheet but let me know if any help is neede
for the programming

S

 

[Damon Longworth]

If your checkboxes are linked to a cell, you should be able to use a
worksheet change event for the time stamp.

--
Damon Longworth

Don't miss out on the 2005 Excel User Conference
Sept 16th and 17th
Stockyards Hotel - Ft. Worth, Texas
www.ExcelUserConference.com

 

[pkohler]

I guess what I am trying to do is have 500+ checkboxes tied to the sam
macro. So I want the macro to capture what cell the checkbox that wa
most recently checked was in.

Do you think an on_change event for the linked cell would work

 

[Roman]

What about to use cells in column A as "buttons":

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Me.Range("a2:a500")) Is Nothing Then 'a2:a500
- the button cells
If Cells(ActiveCell.Row, 2).Value = "" Then
Cells(ActiveCell.Row, 2).Value = Now()
End If
End If
End Sub

 

[Roman]

You'd better not lose time by finding what is the position of each of
500 checkboxes. Change column A fon to Wingdings 2 and try this:

Private Sub Worksheet_SelectionChange(ByVa­l Target As Range)
If Not Intersect(Target, Me.Range("a2:a500")) Is Nothing Then 'a2:a500
- the button cells
If Cells(ActiveCell.Row, 2).Value = "" Then
activecell.value = "R"
Cells(ActiveCell.Row, 2).Value = Now()
End If
End If
End Sub

 

[Dave Peterson]

You can use the same macro if you use the checkbox from the forms toolbar:

Option Explicit
Sub CBXClick()

Dim myCBX As CheckBox

Set myCBX = ActiveSheet.CheckBoxes(Application.Caller)
If myCBX.Value = xlOn Then
With myCBX.TopLeftCell.Offset(0, 1)
.Value = Now
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
End With

End If

End Sub

===
And if you want to add those checkboxes (500 is a lot) via a macro, this may
help.

Option Explicit
Sub addCBX()
Dim myCBX As CheckBox
Dim myRng As Range
Dim myCell As Range

With ActiveSheet
.CheckBoxes.Delete 'nice for testing

Set myRng = .Range("a1:a" & .Cells(.Rows.Count, "C").End(xlUp).Row)

For Each myCell In myRng.Cells
With myCell
Set myCBX = .Parent.CheckBoxes.Add _
(Top:=.Top, Width:=.Width, _
Left:=.Left, Height:=.Height)
With myCBX
.LinkedCell = myCell.Address(external:=True)
.Caption = "" 'or whatever you want
.Name = "CBX_" & myCell.Address(0, 0)
If myCell.Address = myRng.Cells(1).Address Then
.OnAction = "CBXClick"
End If
End With
.NumberFormat = ";;;"
End With
Next myCell
End With
End Sub