Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / General Excel Questions / May 2008

Tip: Looking for answers? Try searching our database.

Dates

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Ms. Beasley - 06 Feb 2007 15:31 GMT
I'm trying to do a worksheet that will autotmatically enter "today's date" in
a field but will not update when the spreadsheet is opened tomorrow.  Can
anybody help me with this?  Thanks!
Gord Dibben - 06 Feb 2007 17:14 GMT
To enter the date/time in column C whenever data is entered in column B

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
   Application.EnableEvents = False
If Not Application.Intersect(Target, Columns("B:B")) Is Nothing Then
Target.Offset(0, 1).Value = Now()
End If
ws_exit:
   Application.EnableEvents = True
End Sub

'For one cell use
'If Target.Address = "$B$3" Then

'For a range use
'If Not Application.Intersect(Range("A1:A10"), Target) Is Nothing Then

This is sheet event code.  Right-click on the sheet tab and "View Code".

Copy/paste the above into that sheet module.

Adjust to suit or post back with some more specifics about where you want the
date entered.

Gord Dibben  MS Excel MVP

>I'm trying to do a worksheet that will autotmatically enter "today's date" in
>a field but will not update when the spreadsheet is opened tomorrow.  Can
>anybody help me with this?  Thanks!
Ms. Beasley - 12 Feb 2007 14:40 GMT
That worked great.  If I want to do the same thing to columns F, I and L can
I use the "or" and copy and this statement "Application.Intersect(Target,
Columns("B:B"))"?  Thanks so much!!

> To enter the date/time in column C whenever data is entered in column B
>
[quoted text clipped - 26 lines]
> >a field but will not update when the spreadsheet is opened tomorrow.  Can
> >anybody help me with this?  Thanks!
Gord Dibben - 12 Feb 2007 18:27 GMT
Try this one.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
If Intersect(Range(Target(1).Address), _
       Range("B:B, F:F, I:I, L:L")) Is Nothing Then Exit Sub
Application.EnableEvents = False
Target.Offset(0, 1).Value = Now()
ws_exit:
    Application.EnableEvents = True
End Sub

Gord

>That worked great.  If I want to do the same thing to columns F, I and L can
>I use the "or" and copy and this statement "Application.Intersect(Target,
[quoted text clipped - 30 lines]
>> >a field but will not update when the spreadsheet is opened tomorrow.  Can
>> >anybody help me with this?  Thanks!
Gord Dibben - 12 Feb 2007 18:43 GMT
This version may be better.

Checks for blank cells.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
If Intersect(Range(Target(1).Address), _
       Range("B:B, F:F, I:I, L:L")) Is Nothing Then Exit Sub
Application.EnableEvents = False
If Target.Value <> "" Then
Target.Offset(0, 1).Value = Now()
End If
ws_exit:
    Application.EnableEvents = True
End Sub

Gord

>Try this one.
>
[quoted text clipped - 44 lines]
>>> >a field but will not update when the spreadsheet is opened tomorrow.  Can
>>> >anybody help me with this?  Thanks!
Ms. Beasley - 06 Feb 2007 17:35 GMT
What I've been asked to do is create a shared log sheet that will allow users
to click in a blank cell that will automatically populate today's date and
then lock the cell.  Then tomorrow when it is opened it will still show the
date of yesterday's entry into the log sheet.  I am not proficient at Excel
at all!!!  Thanks so much!!

> I'm trying to do a worksheet that will autotmatically enter "today's date" in
> a field but will not update when the spreadsheet is opened tomorrow.  Can
> anybody help me with this?  Thanks!
Gord Dibben - 06 Feb 2007 19:17 GMT
Ms Beasley

Many features are unavailable in Shared Workbooks.

See Help on "shared workbook" for a list.

Changing protection/locking/unlocking cell is one of these features not
available.

What you want to do can easily be done on a non-shared workbook using event
code.

Gord

>What I've been asked to do is create a shared log sheet that will allow users
>to click in a blank cell that will automatically populate today's date and
[quoted text clipped - 5 lines]
>> a field but will not update when the spreadsheet is opened tomorrow.  Can
>> anybody help me with this?  Thanks!
Ms. Beasley - 06 Feb 2007 19:35 GMT
Can you walk me through that?  Thanks!

> Ms Beasley
>
[quoted text clipped - 19 lines]
> >> a field but will not update when the spreadsheet is opened tomorrow.  Can
> >> anybody help me with this?  Thanks!
Gord Dibben - 06 Feb 2007 22:05 GMT
Through what?

The event code for a non-shared worksheet?

NOTE:  will not work on a Shared Workbook as I said earlier.

1.   Select column A and any other range you want unlocked.
2.   Format>Cells>Protection>  uncheck the "locked" box.
3.   Widen column A to about 16 minimum.

Select the sheet tab and "View Code".   Copy/paste this code into that sheet
module.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target _
As Range, Cancel As Boolean)
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 1 Then
ActiveSheet.Unprotect Password:="justme"
   For Each cell In Target
   If cell.Value = "" Then
   With cell
        .Value = Now
        .Locked = True
   End With
   End If
Next
Cancel = True
End If
enditall:
Application.EnableEvents = True
ActiveSheet.Protect Password:="justme"
End Sub

Double-click on any blank cell in column A and get the date/time entered and
cell locked so timestamp cannot be changed.

Does not affect protection on other unlocked cells.

"justme" can be any password you choose.

You will of course want to lock the code from prying eyes so..........

Alt + F11 to open Visual Basic Editor.

Select your workbook/project and right-click>VBAProject
Properties>Protection>Lock for Viewing.  Apply a password and save/close the
workbook.

When re-opened, code will be unviewable or uneditable.

Gord

>Can you walk me through that?  Thanks!
>
[quoted text clipped - 21 lines]
>> >> a field but will not update when the spreadsheet is opened tomorrow.  Can
>> >> anybody help me with this?  Thanks!
Ms. Beasley - 07 Feb 2007 21:06 GMT
I think this is exactly what I needed.  Thanks a bunch!!

> Through what?
>
[quoted text clipped - 74 lines]
> >> >> a field but will not update when the spreadsheet is opened tomorrow.  Can
> >> >> anybody help me with this?  Thanks!
Gord Dibben - 07 Feb 2007 21:13 GMT
Thanks and good luck

Gord

>I think this is exactly what I needed.  Thanks a bunch!!
>
[quoted text clipped - 76 lines]
>> >> >> a field but will not update when the spreadsheet is opened tomorrow.  Can
>> >> >> anybody help me with this?  Thanks!
Ms. Beasley - 08 Feb 2007 17:37 GMT
If I want to add column 8 to do the same thing, where can I put it in the
above formula?  Thanks again!!!

> Thanks and good luck
>
[quoted text clipped - 80 lines]
> >> >> >> a field but will not update when the spreadsheet is opened tomorrow.  Can
> >> >> >> anybody help me with this?  Thanks!
Gord Dibben - 08 Feb 2007 20:27 GMT
Change   If Target.Cells.Column = 1 Then

to

If Target.Cells.Column = 1 Or Target.Cells.Column = 8 Then

BTW.........this is not a "formula" but lines of VBA code, more commonly

referred to as a "macro" or "routine"

In this case the code is "event code" which runs when some event takes place.

Gord

>If I want to add column 8 to do the same thing, where can I put it in the
>above formula?  Thanks again!!!
[quoted text clipped - 83 lines]
>> >> >> >> a field but will not update when the spreadsheet is opened tomorrow.  Can
>> >> >> >> anybody help me with this?  Thanks!
Ms. Beasley - 08 Feb 2007 21:16 GMT
This worked and now they are asking me more questions.  Stay tuned......
Thanks so much for all of your help!!

> Change   If Target.Cells.Column = 1 Then
>
[quoted text clipped - 97 lines]
> >> >> >> >> a field but will not update when the spreadsheet is opened tomorrow.  Can
> >> >> >> >> anybody help me with this?  Thanks!
Ms. Beasley - 09 Feb 2007 20:16 GMT
Here is the next thing they want me to do.  Column 9 is an
"approved/rejected" field.  When it is populated how can I get column 10 to
populate with a date that can't altered?  Once again, I really appreciate
your help!!!                                                   
                                                   
                                                   
                                                   
                                                   

> Change   If Target.Cells.Column = 1 Then
>
[quoted text clipped - 97 lines]
> >> >> >> >> a field but will not update when the spreadsheet is opened tomorrow.  Can
> >> >> >> >> anybody help me with this?  Thanks!
Gord Dibben - 09 Feb 2007 21:58 GMT
With a different type of event code.

Make sure Column 9(I) is unlocked as were A and H columns unlocked for data
entry.

Then copy/paste this to the sheet module.  See it is change event code, not
double-click.  Requires entering something into the cell.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 9 Then
ActiveSheet.Unprotect Password:="justme"
    For Each cell In Target
    If cell.Value <> "" Then
    With cell.Offset(0, 1)
         .Value = Now
         .Locked = True
    End With
    End If
Next
End If
enditall:
Application.EnableEvents = True
ActiveSheet.Protect Password:="justme"
End Sub

NOTE:  multiple types of event code can go into one module as long as no two are
the same type of event.

Gord

>Here is the next thing they want me to do.  Column 9 is an
>"approved/rejected" field.  When it is populated how can I get column 10 to
>populate with a date that can't altered?  Once again, I really appreciate
>your help!!!                                                   

<snipped for brevity>
Josh - 14 May 2008 20:41 GMT
I have a similar request but wanted the code to work on the entire column.  I
used the code you suggested and it put a date stamp on the first cell I
entered data into but then stopped after that.  I need it to be able to do it
for the entire column so if I enter data in cell E4 for example, i need a
stamp at F4 and then data into E5, i need a stamp at F5.  Thanks.

> With a different type of event code.
>
[quoted text clipped - 34 lines]
>
> <snipped for brevity>
Gord Dibben - 14 May 2008 21:26 GMT
Change Column = 9  to Column = 5  in the code below and it should be good to go
for all of Column E

Just make sure columns E and F are unlocked before you Protect the worksheet.

Gord

>I have a similar request but wanted the code to work on the entire column.  I
>used the code you suggested and it put a date stamp on the first cell I
[quoted text clipped - 40 lines]
>>
>> <snipped for brevity>
Josh - 14 May 2008 21:38 GMT
I did that before I made the previous post and it would only post the date by
the first cell I entered data into.  After that, there would be no dates
posted automatically.

> Change Column = 9  to Column = 5  in the code below and it should be good to go
> for all of Column E
[quoted text clipped - 47 lines]
> >>
> >> <snipped for brevity>
Dave Peterson - 14 May 2008 22:45 GMT
Maybe it's time to share the code you're using.

> I did that before I made the previous post and it would only post the date by
> the first cell I entered data into.  After that, there would be no dates
[quoted text clipped - 51 lines]
> > >>
> > >> <snipped for brevity>

Signature

Dave Peterson

Gord Dibben - 14 May 2008 23:14 GMT
In that case, post your amended code as you have it currently.

Gord

>I did that before I made the previous post and it would only post the date by
>the first cell I entered data into.  After that, there would be no dates
[quoted text clipped - 4 lines]
>>
>> Just make sure columns E and F are unlocked before you Protect the worksheet.
Josh - 15 May 2008 21:01 GMT
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 5 Or Target.Cells.Column = 8 Or
Target.Cells.Column = 11 Then
ActiveSheet.Unprotect Password:="justme"
    For Each cell In Target
    If cell.Value <> "" Then
    With cell.Offset(0, 1)
         .Value = Now
         .Locked = True
    End With
    End If
Next
End If
enditall:
Application.EnableEvents = True
'ActiveSheet.Protect Password:="justme"
End Sub

I figured out my previous problem but now I want the cells that the dates
were inserted into to be locked but not having the entire sheet locked.  
Thats why I put the ' in the second to last line of code.  Is there any way I
can lock certain cells that have the date inserted into?  For example, having
the cells in column 6 be locked after the date has appeared on the
spreadsheet.

> In that case, post your amended code as you have it currently.
>
[quoted text clipped - 8 lines]
> >>
> >> Just make sure columns E and F are unlocked before you Protect the worksheet.
Dave Peterson - 15 May 2008 21:27 GMT
The bad news is that you can lock any old cell you want to.  But it really won't
mean much to the user until you protect the sheet.  If the sheet is unprotected,
the user can do anything he/she wants to any cell--whether it's locked or
unlocked.

An alternative to your _change event:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

   Dim myRng As Range
   Dim myCell As Range
   
   Set myRng = Intersect(Target, Me.Range("e:e,h:h,k:k"))
   If myRng Is Nothing Then
       Exit Sub
   End If
   
   On Error GoTo EndItAll:
   For Each myCell In myRng.Cells
       If myCell.Value <> "" Then
           With myCell.Offset(0, 1)
               .Value = Now
               .Locked = True
           End With
       End If
   Next myCell
   
EndItAll:
   Application.EnableEvents = True
   Me.Protect Password:="justme"

End Sub

> Private Sub Worksheet_Change(ByVal Target As Range)
>  On Error GoTo enditall
[quoted text clipped - 35 lines]
> > >>
> > >> Just make sure columns E and F are unlocked before you Protect the worksheet.

Signature

Dave Peterson

Gord Dibben - 16 May 2008 01:49 GMT
You cannot have any cells locked without protecting the worksheet.

You can format them as locked but won't be until you do the protection step.

See Dave's response for good info and some new code.

Gord Dibben  MS Excel MVP

>Private Sub Worksheet_Change(ByVal Target As Range)
> On Error GoTo enditall
[quoted text clipped - 35 lines]
>> >>
>> >> Just make sure columns E and F are unlocked before you Protect the worksheet.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.