I am trying to create a checklist with automatic dates

[dwhite612]

I would like to create a checklist with boxes that when checked will insert a date and time into a separate cell which cell thereafter cannot be changed. I hope I have explained myself well enough to be understood.

 

[Smitty]

Welcome to the Board!

Using a check box from the Control Toolbox this might be a start:

<font face=tahoma><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CheckBox1_Click()
    <SPAN style="color:#00007F">If</SPAN> CheckBox1.Value = <SPAN style="color:#00007F">True</SPAN> <SPAN style="color:#00007F">Then</SPAN>
        <SPAN style="color:#007F00">'   When checked, add the Date three columns to the left of Check Box</SPAN>
        <SPAN style="color:#007F00">'   Lock and Protect</SPAN>
        <SPAN style="color:#00007F">With</SPAN> CheckBox1.TopLeftCell.Offset(, 3)
            .Value = <SPAN style="color:#00007F">Date</SPAN>
            .Locked = <SPAN style="color:#00007F">True</SPAN>
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
        ActiveSheet.Protect
        <SPAN style="color:#007F00">'   Otherwise clear that cell</SPAN>
    Else:
        ActiveSheet.Unprotect
        <SPAN style="color:#00007F">With</SPAN> CheckBox1.TopLeftCell.Offset(, 3)
            .Value = ""
            .Locked = <SPAN style="color:#00007F">False</SPAN>
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

Once you've drawn the check box, double-click it and the VB Editor will open to its Click event. Just add the code there. You can repeat that sequence for additional controls.

Or did you want to create a User Form?

Hope that helps,

Smitty

 

[dwhite612]

I did what you said.... Thank you so much for your time. Now how do I make it so that I can click the box....also I want the time and date to appear 7 cells to the right of the checkbox. Thanks again!!!!

 

[Smitty]

Just a slight change:

        With CheckBox1.TopLeftCell.Offset(, 7)
            .Value = Format(Now, "mm/dd/yy hh:mm am/pm")

Smitty

EDIT: to make it so that you can interact with the Check Box, you need to exit Edit mode. Deactivate it by clicking the Speed Square/Pencil icon in the upper left-hand corner of the Control Toolbox.

 

[dwhite612]

Sorry that I am so slow with this but how do I click the box to make it appear. When I click nothing happens. When I double click the VB editor pops up....Thanks sooo much for your time

 

[Smitty]

You need to exit edit mode:

to make it so that you can interact with the Check Box, you need to exit Edit mode. Deactivate it by clicking the Speed Square/Pencil icon in the upper left-hand corner of the Control Toolbox.

That's the toolbar that appears when you goto View-->Toolbars-->Control Toolbox.

Smitty