Check box, then add current time to cell

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 here:
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 here:
> 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 here:
> 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,