Auto Skipping and Protecting Worksheet

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.

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" <[email protected]> wrote in message
news:[email protected]...
> 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.

Tom,

Many thanks for your suggestions. However, the workbook is not password
protected only certain cells are locked to enable cells to be skipped where
data entry is not required.

Any suggestions you may have to remidy my original problem would be most
appreciated.


"Tom Ogilvy" wrote:

> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
>
>
>

Tom,

Many thanks for your suggestions. However, the workbook is not password
protected only certain cells are locked to enable cells to be skipped where
data entry is not required.

Any suggestions you may have to remidy my original problem would be most
appreciated.


"Tom Ogilvy" wrote:

> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
>
>
>