I wish to create a spreadsheet that whenever data is entered in a
row/column, the actual date is entered automatically in a date column. I do
not want the date to be overridden.
I wish to create a spreadsheet that whenever data is entered in a
row/column, the actual date is entered automatically in a date column. I do
not want the date to be overridden.
Mark
Right-click your sheet tab and "View Code".
Copy/paste this event code in that sheet module.
Enter anything in any cell in column A and B will get a static date.
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'Col B time will not change if data in Col A is edited
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 1 Then
n = Target.Row
If Excel.Range("A" & n).Value <> "" _
And Excel.Range("B" & n).Value = "" Then
Excel.Range("B" & n).Value = Format(Now, "dd mmm yyyy")
End If
End If
enditall:
Application.EnableEvents = True
End Sub
Gord Dibben Excel MVP
On Tue, 8 Feb 2005 11:05:03 -0800, "mark" <[email protected]>
wrote:
>I wish to create a spreadsheet that whenever data is entered in a
>row/column, the actual date is entered automatically in a date column. I do
>not want the date to be overridden.
Gord
Many Thanks
How can I now protect the range of cells with date in it? When I try to
protect the column the formula disappears.
Mark
"Gord Dibben" wrote:
> Mark
>
> Right-click your sheet tab and "View Code".
>
> Copy/paste this event code in that sheet module.
>
> Enter anything in any cell in column A and B will get a static date.
>
> Private Sub Worksheet_Change(ByVal Target As Excel.Range)
> 'Col B time will not change if data in Col A is edited
> On Error GoTo enditall
> Application.EnableEvents = False
> If Target.Cells.Column = 1 Then
> n = Target.Row
> If Excel.Range("A" & n).Value <> "" _
> And Excel.Range("B" & n).Value = "" Then
> Excel.Range("B" & n).Value = Format(Now, "dd mmm yyyy")
> End If
> End If
> enditall:
> Application.EnableEvents = True
> End Sub
>
>
> Gord Dibben Excel MVP
>
> On Tue, 8 Feb 2005 11:05:03 -0800, "mark" <[email protected]>
> wrote:
>
> >I wish to create a spreadsheet that whenever data is entered in a
> >row/column, the actual date is entered automatically in a date column. I do
> >not want the date to be overridden.
>
>
Mark
One more step........
Copy/paste the code below into the ThisWorkbook module.
Right-click on the Excel logo left of "File" on menu bar or on logo at top
left corner of Window if not maximized.
Select "View Code" and paste into that module.
Private Sub Workbook_Open()
Dim sh As Worksheet
Application.ScreenUpdating = False
Sheets("Sheet1").Protect "justme", , , userinterfaceonly:=True
Application.ScreenUpdating = True
End Sub
"Sheet1" is the name of your sheet. Adjust to suit.
What this code does is allow the code to run on a protected sheet.
Unlock column A so's data can be entered. Lock Column B so's the dates cannot
be deleted.
Save the workbook then re-open.
Gord
On Wed, 9 Feb 2005 01:37:04 -0800, "mark" <[email protected]>
wrote:
>Gord
>
>Many Thanks
>
>How can I now protect the range of cells with date in it? When I try to
>protect the column the formula disappears.
>
>Mark
>
>"Gord Dibben" wrote:
>
>> Mark
>>
>> Right-click your sheet tab and "View Code".
>>
>> Copy/paste this event code in that sheet module.
>>
>> Enter anything in any cell in column A and B will get a static date.
>>
>> Private Sub Worksheet_Change(ByVal Target As Excel.Range)
>> 'Col B time will not change if data in Col A is edited
>> On Error GoTo enditall
>> Application.EnableEvents = False
>> If Target.Cells.Column = 1 Then
>> n = Target.Row
>> If Excel.Range("A" & n).Value <> "" _
>> And Excel.Range("B" & n).Value = "" Then
>> Excel.Range("B" & n).Value = Format(Now, "dd mmm yyyy")
>> End If
>> End If
>> enditall:
>> Application.EnableEvents = True
>> End Sub
>>
>>
>> Gord Dibben Excel MVP
>>
>> On Tue, 8 Feb 2005 11:05:03 -0800, "mark" <[email protected]>
>> wrote:
>>
>> >I wish to create a spreadsheet that whenever data is entered in a
>> >row/column, the actual date is entered automatically in a date column. I do
>> >not want the date to be overridden.
>>
>>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks