I am pretty new to macros and just found out what they were and kind of how
they work. I am trying to make a check list that when the box is checked the
current time is inserted to a different cell. Any advice on how to do this
would be much appreciated.

Thanks,
--
Joe

Hi Joe

There are a few things to consider the first being do you use checkboxes
from the Forms Toolbar or from the Controls Toolbox. If you search
google groups you will probably find a lot of discussions on the pros
and cons of each, I won't go into it all here.

If you use Checkboxes from the Controls Toolbox you can double click on
each checkbox while in design mode which will take you to the click
event for that checkbox. You can then add code similar to this:

Private Sub CheckBox1_Click()
Me.Range("A5").Value = Format(Now(), "hh:mm:ss")
End Sub

You would have to have an individual event macro like this for each
checkbox. There are methods to use one event for multiple controls but
they can get quite complicated. One way is described he
http://j-walk.com/ss/excel/tips/tip44.htm

Alternately you could use checkboxes from the forms toolbar. You could
then right click each checkbox and assign the same macro to each. This
macro would sit in a standard code module and look something like this:

Sub CheckBoxes()
With Sheets("Sheet5")
Select Case Application.Caller
Case "Check Box 1"
.Range("A5").Value = Format(Now(), "hh:mm:ss")
Case "Check Box 2"
.Range("A6").Value = Format(Now(), "hh:mm:ss")
Case "Check Box 3"
.Range("A7").Value = Format(Now(), "hh:mm:ss")
'etc
End Select
End With
End Sub

Doing it this way though, I am not sure how you would check the status
of the checkbox i.e the Time would be set every time the checkbox is
clicked not just when it is clicked to add a check mark.

Anyway, I hope this helps
Rowan

Joe wrote:
I am pretty new to macros and just found out what they were and kind of how
they work. I am trying to make a check list that when the box is checked the
current time is inserted to a different cell. Any advice on how to do this
would be much appreciated.

Thanks,

Thanks, I will try this tomorrow at work.
--
Joe


"Rowan" wrote:

Hi Joe

There are a few things to consider the first being do you use checkboxes
from the Forms Toolbar or from the Controls Toolbox. If you search
google groups you will probably find a lot of discussions on the pros
and cons of each, I won't go into it all here.

If you use Checkboxes from the Controls Toolbox you can double click on
each checkbox while in design mode which will take you to the click
event for that checkbox. You can then add code similar to this:

Private Sub CheckBox1_Click()
Me.Range("A5").Value = Format(Now(), "hh:mm:ss")
End Sub

You would have to have an individual event macro like this for each
checkbox. There are methods to use one event for multiple controls but
they can get quite complicated. One way is described he
http://j-walk.com/ss/excel/tips/tip44.htm

Alternately you could use checkboxes from the forms toolbar. You could
then right click each checkbox and assign the same macro to each. This
macro would sit in a standard code module and look something like this:

Sub CheckBoxes()
With Sheets("Sheet5")
Select Case Application.Caller
Case "Check Box 1"
.Range("A5").Value = Format(Now(), "hh:mm:ss")
Case "Check Box 2"
.Range("A6").Value = Format(Now(), "hh:mm:ss")
Case "Check Box 3"
.Range("A7").Value = Format(Now(), "hh:mm:ss")
'etc
End Select
End With
End Sub

Doing it this way though, I am not sure how you would check the status
of the checkbox i.e the Time would be set every time the checkbox is
clicked not just when it is clicked to add a check mark.

Anyway, I hope this helps
Rowan

Joe wrote:
I am pretty new to macros and just found out what they were and kind of how
they work. I am trying to make a check list that when the box is checked the
current time is inserted to a different cell. Any advice on how to do this
would be much appreciated.

Thanks,

Rowan,

Thanks for the help, I assigned the same macro to each of the check boxes
and the time gets updated into the assigned cells, the only problem is that
the time updates when the boxes get unchecked too. I would like to make it
to where when the check boxes are unchecked that the time is erased out of
the cells.

Thanks again,
--
Joe


"Rowan" wrote:

Hi Joe

There are a few things to consider the first being do you use checkboxes
from the Forms Toolbar or from the Controls Toolbox. If you search
google groups you will probably find a lot of discussions on the pros
and cons of each, I won't go into it all here.

If you use Checkboxes from the Controls Toolbox you can double click on
each checkbox while in design mode which will take you to the click
event for that checkbox. You can then add code similar to this:

Private Sub CheckBox1_Click()
Me.Range("A5").Value = Format(Now(), "hh:mm:ss")
End Sub

You would have to have an individual event macro like this for each
checkbox. There are methods to use one event for multiple controls but
they can get quite complicated. One way is described he
http://j-walk.com/ss/excel/tips/tip44.htm

Alternately you could use checkboxes from the forms toolbar. You could
then right click each checkbox and assign the same macro to each. This
macro would sit in a standard code module and look something like this:

Sub CheckBoxes()
With Sheets("Sheet5")
Select Case Application.Caller
Case "Check Box 1"
.Range("A5").Value = Format(Now(), "hh:mm:ss")
Case "Check Box 2"
.Range("A6").Value = Format(Now(), "hh:mm:ss")
Case "Check Box 3"
.Range("A7").Value = Format(Now(), "hh:mm:ss")
'etc
End Select
End With
End Sub

Doing it this way though, I am not sure how you would check the status
of the checkbox i.e the Time would be set every time the checkbox is
clicked not just when it is clicked to add a check mark.

Anyway, I hope this helps
Rowan

Joe wrote:
I am pretty new to macros and just found out what they were and kind of how
they work. I am trying to make a check list that when the box is checked the
current time is inserted to a different cell. Any advice on how to do this
would be much appreciated.

Thanks,

Hi Joe

If you right click each checkbox, select Format Control and add a cell
link you can then check the value of this cell which will be true if the
box is checked, otherwise false.

In this example I have linked each checkbox to a cell in column Z which
can then be hidden:

Sub CheckBoxes()
With Sheets("Sheet5")
Select Case Application.Caller
Case "Check Box 1"
If .Range("Z5").Value Then
.Range("A5").Value = Format(Now(), "hh:mm:ss")
End If
Case "Check Box 2"
If .Range("Z6").Value Then
.Range("A6").Value = Format(Now(), "hh:mm:ss")
End If
Case "Check Box 3"
If .Range("Z7").Value Then
.Range("A7").Value = Format(Now(), "hh:mm:ss")
End If
'etc
End Select
End With
End Sub

Hope this helps
Rowan

Joe wrote:
Rowan,

Thanks for the help, I assigned the same macro to each of the check boxes
and the time gets updated into the assigned cells, the only problem is that
the time updates when the boxes get unchecked too. I would like to make it
to where when the check boxes are unchecked that the time is erased out of
the cells.

Thanks again,

Hey Joe,

This worked for me when I used the forms menu to create my check box. I
linked my checkbox to F4, and I placed the time in F5.

Sub CheckBox1_Click()

With ActiveSheet
If [F4].Value = True Then
With [F5]
.Value = Now
.NumberFormat = "mm/dd/yyyy h:mm:ss AM/PM"
End With
Else
With [F5]
.Value = Null
End With
End If

End With

End Sub

Hope this helps!

Stephanie

"Joe" wrote:

I am pretty new to macros and just found out what they were and kind of how
they work. I am trying to make a check list that when the box is checked the
current time is inserted to a different cell. Any advice on how to do this
would be much appreciated.

Thanks,
--
Joe