Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dates
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dates
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 On Tue, 6 Feb 2007 07:31:00 -0800, Ms. Beasley <Ms. wrote: 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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dates
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!! "Ms. Beasley" wrote: 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! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dates
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 On Tue, 6 Feb 2007 09:35:01 -0800, Ms. Beasley wrote: 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!! "Ms. Beasley" wrote: 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! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dates
Can you walk me through that? Thanks!
"Gord Dibben" wrote: 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 On Tue, 6 Feb 2007 09:35:01 -0800, Ms. Beasley wrote: 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!! "Ms. Beasley" wrote: 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! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dates
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. FormatCellsProtection 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-clickVBAProject PropertiesProtectionLock for Viewing. Apply a password and save/close the workbook. When re-opened, code will be unviewable or uneditable. Gord On Tue, 6 Feb 2007 11:35:03 -0800, Ms. Beasley wrote: Can you walk me through that? Thanks! "Gord Dibben" wrote: 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 On Tue, 6 Feb 2007 09:35:01 -0800, Ms. Beasley wrote: 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!! "Ms. Beasley" wrote: 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! |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dates
I think this is exactly what I needed. Thanks a bunch!!
"Gord Dibben" wrote: 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. FormatCellsProtection 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-clickVBAProject PropertiesProtectionLock for Viewing. Apply a password and save/close the workbook. When re-opened, code will be unviewable or uneditable. Gord On Tue, 6 Feb 2007 11:35:03 -0800, Ms. Beasley wrote: Can you walk me through that? Thanks! "Gord Dibben" wrote: 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 On Tue, 6 Feb 2007 09:35:01 -0800, Ms. Beasley wrote: 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!! "Ms. Beasley" wrote: 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! |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dates
Thanks and good luck
Gord On Wed, 7 Feb 2007 13:06:01 -0800, Ms. Beasley wrote: I think this is exactly what I needed. Thanks a bunch!! "Gord Dibben" wrote: 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. FormatCellsProtection 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-clickVBAProject PropertiesProtectionLock for Viewing. Apply a password and save/close the workbook. When re-opened, code will be unviewable or uneditable. Gord On Tue, 6 Feb 2007 11:35:03 -0800, Ms. Beasley wrote: Can you walk me through that? Thanks! "Gord Dibben" wrote: 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 On Tue, 6 Feb 2007 09:35:01 -0800, Ms. Beasley wrote: 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!! "Ms. Beasley" wrote: 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! |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dates
If I want to add column 8 to do the same thing, where can I put it in the
above formula? Thanks again!!! "Gord Dibben" wrote: Thanks and good luck Gord On Wed, 7 Feb 2007 13:06:01 -0800, Ms. Beasley wrote: I think this is exactly what I needed. Thanks a bunch!! "Gord Dibben" wrote: 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. FormatCellsProtection 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-clickVBAProject PropertiesProtectionLock for Viewing. Apply a password and save/close the workbook. When re-opened, code will be unviewable or uneditable. Gord On Tue, 6 Feb 2007 11:35:03 -0800, Ms. Beasley wrote: Can you walk me through that? Thanks! "Gord Dibben" wrote: 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 On Tue, 6 Feb 2007 09:35:01 -0800, Ms. Beasley wrote: 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!! "Ms. Beasley" wrote: 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! |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dates
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 On Thu, 8 Feb 2007 09:37:01 -0800, Ms. Beasley wrote: If I want to add column 8 to do the same thing, where can I put it in the above formula? Thanks again!!! "Gord Dibben" wrote: Thanks and good luck Gord On Wed, 7 Feb 2007 13:06:01 -0800, Ms. Beasley wrote: I think this is exactly what I needed. Thanks a bunch!! "Gord Dibben" wrote: 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. FormatCellsProtection 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-clickVBAProject PropertiesProtectionLock for Viewing. Apply a password and save/close the workbook. When re-opened, code will be unviewable or uneditable. Gord On Tue, 6 Feb 2007 11:35:03 -0800, Ms. Beasley wrote: Can you walk me through that? Thanks! "Gord Dibben" wrote: 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 On Tue, 6 Feb 2007 09:35:01 -0800, Ms. Beasley wrote: 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!! "Ms. Beasley" wrote: 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! |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dates
This worked and now they are asking me more questions. Stay tuned......
Thanks so much for all of your help!! "Gord Dibben" wrote: 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 On Thu, 8 Feb 2007 09:37:01 -0800, Ms. Beasley wrote: If I want to add column 8 to do the same thing, where can I put it in the above formula? Thanks again!!! "Gord Dibben" wrote: Thanks and good luck Gord On Wed, 7 Feb 2007 13:06:01 -0800, Ms. Beasley wrote: I think this is exactly what I needed. Thanks a bunch!! "Gord Dibben" wrote: 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. FormatCellsProtection 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-clickVBAProject PropertiesProtectionLock for Viewing. Apply a password and save/close the workbook. When re-opened, code will be unviewable or uneditable. Gord On Tue, 6 Feb 2007 11:35:03 -0800, Ms. Beasley wrote: Can you walk me through that? Thanks! "Gord Dibben" wrote: 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 On Tue, 6 Feb 2007 09:35:01 -0800, Ms. Beasley wrote: 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!! "Ms. Beasley" wrote: 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! |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dates
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!!! "Gord Dibben" wrote: 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 On Thu, 8 Feb 2007 09:37:01 -0800, Ms. Beasley wrote: If I want to add column 8 to do the same thing, where can I put it in the above formula? Thanks again!!! "Gord Dibben" wrote: Thanks and good luck Gord On Wed, 7 Feb 2007 13:06:01 -0800, Ms. Beasley wrote: I think this is exactly what I needed. Thanks a bunch!! "Gord Dibben" wrote: 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. FormatCellsProtection 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-clickVBAProject PropertiesProtectionLock for Viewing. Apply a password and save/close the workbook. When re-opened, code will be unviewable or uneditable. Gord On Tue, 6 Feb 2007 11:35:03 -0800, Ms. Beasley wrote: Can you walk me through that? Thanks! "Gord Dibben" wrote: 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 On Tue, 6 Feb 2007 09:35:01 -0800, Ms. Beasley wrote: 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!! "Ms. Beasley" wrote: 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! |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dates
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 On Fri, 9 Feb 2007 12:16:00 -0800, Ms. Beasley wrote: 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 |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dates
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!! "Gord Dibben" wrote: 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 On Tue, 6 Feb 2007 07:31:00 -0800, Ms. Beasley <Ms. wrote: 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! |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dates
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 On Mon, 12 Feb 2007 06:40:02 -0800, Ms. Beasley wrote: 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!! "Gord Dibben" wrote: 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 On Tue, 6 Feb 2007 07:31:00 -0800, Ms. Beasley <Ms. wrote: 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! |
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dates
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 On Mon, 12 Feb 2007 10:27:29 -0800, Gord Dibben <gorddibbATshawDOTca wrote: 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 On Mon, 12 Feb 2007 06:40:02 -0800, Ms. Beasley wrote: 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!! "Gord Dibben" wrote: 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 On Tue, 6 Feb 2007 07:31:00 -0800, Ms. Beasley <Ms. wrote: 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! |
#17
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dates
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. "Gord Dibben" wrote: 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 On Fri, 9 Feb 2007 12:16:00 -0800, Ms. Beasley wrote: 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 |
#18
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dates
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 On Wed, 14 May 2008 12:41:00 -0700, Josh wrote: 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. "Gord Dibben" wrote: 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 On Fri, 9 Feb 2007 12:16:00 -0800, Ms. Beasley wrote: 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 |
#19
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dates
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. "Gord Dibben" wrote: 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 On Wed, 14 May 2008 12:41:00 -0700, Josh wrote: 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. "Gord Dibben" wrote: 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 On Fri, 9 Feb 2007 12:16:00 -0800, Ms. Beasley wrote: 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 |
#20
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dates
Maybe it's time to share the code you're using.
Josh wrote: 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. "Gord Dibben" wrote: 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 On Wed, 14 May 2008 12:41:00 -0700, Josh wrote: 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. "Gord Dibben" wrote: 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 On Fri, 9 Feb 2007 12:16:00 -0800, Ms. Beasley wrote: 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 -- Dave Peterson |
#21
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dates
In that case, post your amended code as you have it currently.
Gord On Wed, 14 May 2008 13:38:02 -0700, Josh wrote: 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. "Gord Dibben" wrote: 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. |
#22
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dates
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. "Gord Dibben" wrote: In that case, post your amended code as you have it currently. Gord On Wed, 14 May 2008 13:38:02 -0700, Josh wrote: 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. "Gord Dibben" wrote: 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. |
#23
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dates
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 Josh wrote: 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. "Gord Dibben" wrote: In that case, post your amended code as you have it currently. Gord On Wed, 14 May 2008 13:38:02 -0700, Josh wrote: 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. "Gord Dibben" wrote: 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. -- Dave Peterson |
#24
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dates
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 On Thu, 15 May 2008 13:01:11 -0700, Josh wrote: 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. "Gord Dibben" wrote: In that case, post your amended code as you have it currently. Gord On Wed, 14 May 2008 13:38:02 -0700, Josh wrote: 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. "Gord Dibben" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
calculating number of three month periods between two dates... | Excel Discussion (Misc queries) | |||
working out quarters (three-month periods) between two dates | Excel Worksheet Functions | |||
Calculating number of days between two dates that fall between two other dates | Excel Discussion (Misc queries) | |||
Automatic shading of cells based on dates??? | Excel Worksheet Functions | |||
2 digit year in dates return 19xx not 20xx | Excel Discussion (Misc queries) |