Private Sub Worksheet_Change(ByVal Target As Range)
me.protect UserInterfaceOnly:=True
Application.EnableEvents = False
On Error GoTo ws_exit
With Target
If .Column = 8 Then
With .Offset(0, 1)
.Value = Date
.NumberFormat = "dd mmm yy"
End With
With .Offset(0, 2)
.Value = Now
.NumberFormat = "hh:mm AM/PM"
End With
End If
End With
ws_exit:
Application.EnableEvents = True
End Sub
If the sheet protection has a password and you are using xl2002 or later,
then you would change to
me.protect Password:="ABCD", UserInterfaceOnly:=True
replace ABCD with your password.
--
Regards,
Tom Ogilvy
"Pank Mehta" wrote in message
...
I have a workbook that contains 14 sheets. I have a sheet for each month
followed by 2 sheets for information.
Each Month sheet has the following column headings associated from columns
A
through J:-
Owner; from date; number of days; to date, address, ID, month, input by;
date; time.
I have to input data in columns A, B, C E, H, I and J.
Columns A and H are pick lists.
The following VBA is present to allow automatic population of columns I
and J.
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
On Error GoTo ws_exit
With Target
If .Column = 8 Then
With .Offset(0, 1)
.Value = Date
.NumberFormat = "dd mmm yy"
End With
With .Offset(0, 2)
.Value = Now
.NumberFormat = "hh:mm AM/PM"
End With
End If
End With
ws_exit:
Application.EnableEvents = True
End Sub
To allow tabbing to the next cell I have locked cells that don't require
input and then protected the sheet (i.e. Columns D, F, G, I and J).
The problem I have is that once an item is picked up from the drop down to
populate column H, then only the date is populated.
If I unprotect the sheet and select an item from the drop down list in
column H then both the date and time are populated.
Is there any way that I can have columns I and J un-locked (to allow date
and time to be populated by the VBA) and when I tab from column H it
automatically takes me to the next row and in column A?
Any help would be most appreciated.
|