I'm confused about what happens in columns I and J.

Do you have all the cells locked (then the macro would not do much) or do you
have some of the cells locked (and some unlocked)? So writing to a cell would
cause an error and the code would pass to ws_exit and get out.

I'm not quite sure...

But maybe just unprotecting the worksheet, doing the writing, then reprotecting
the worksheet would be sufficient:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ws_exit
With Target
If .Column = 8 Then
Me.Unprotect
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
Me.Protect
End Sub

Pank Mehta wrote:

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 (i.e. Columns D, F, G, I and J). and then protected the sheet without
a password.

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.

--

Dave Peterson

I have columns D,F G,I and H locked, as they get popoulated automatically. I
want to move from column C to column D automatically once data is entered
into column A, B and C (and hence unlocked). Column E is unlocked as data is
required. Column F and H are locked as these are automaticall populated once
column E is populated. Column H is a pick list and once a selection is made I
want the date and time to be populated in columns I and J automatically
(hence the VBA) and after an entry in the picklist in column H is selected I
want to be able to be positioned in the next row in column A.

I was led to believe that is one wants to automatically skip to columns one
has to lock/unlock cells and then protect the worksheet.

"Dave Peterson" wrote:

I'm confused about what happens in columns I and J.

Do you have all the cells locked (then the macro would not do much) or do you
have some of the cells locked (and some unlocked)? So writing to a cell would
cause an error and the code would pass to ws_exit and get out.

I'm not quite sure...

But maybe just unprotecting the worksheet, doing the writing, then reprotecting
the worksheet would be sufficient:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ws_exit
With Target
If .Column = 8 Then
Me.Unprotect
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
Me.Protect
End Sub

Pank Mehta wrote:

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 (i.e. Columns D, F, G, I and J). and then protected the sheet without
a password.

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.

--

Dave Peterson

I'm even more confused.

This part of the code:

With Target
If .Column = 8 Then

Looks to see if the change was made in column 8 (aka H). If H is locked, then
this event won't do anything--since the user can't change stuff in column H.

But you say Column H is a pick list. (I'm not sure what you mean--I guessed
data|validation, but maybe not.)

But in any case, didn't the unprotecting the sheet/doing the work/reprotecting
the sheet work for you?





Pank Mehta wrote:

I have columns D,F G,I and H locked, as they get popoulated automatically. I
want to move from column C to column D automatically once data is entered
into column A, B and C (and hence unlocked). Column E is unlocked as data is
required. Column F and H are locked as these are automaticall populated once
column E is populated. Column H is a pick list and once a selection is made I
want the date and time to be populated in columns I and J automatically
(hence the VBA) and after an entry in the picklist in column H is selected I
want to be able to be positioned in the next row in column A.

I was led to believe that is one wants to automatically skip to columns one
has to lock/unlock cells and then protect the worksheet.

"Dave Peterson" wrote:

I'm confused about what happens in columns I and J.

Do you have all the cells locked (then the macro would not do much) or do you
have some of the cells locked (and some unlocked)? So writing to a cell would
cause an error and the code would pass to ws_exit and get out.

I'm not quite sure...

But maybe just unprotecting the worksheet, doing the writing, then reprotecting
the worksheet would be sufficient:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ws_exit
With Target
If .Column = 8 Then
Me.Unprotect
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
Me.Protect
End Sub

Pank Mehta wrote:

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 (i.e. Columns D, F, G, I and J). and then protected the sheet without
a password.

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.

--

Dave Peterson


--

Dave Peterson

Dave,

To answer your questions, the code I got was from a gentleman on the
discussion groups. The code works as expected if the worksheet is not
proctected (i.e if I select from the pick list in column H then Cols I and J
get populated with the date and time).

Using your recommendation to unprotect the sheet and then protecting the
sheet within the VBA gave a funny result in that it only populates the date
in column I, column J does not get populated with the date.

Any suggestions??


"Dave Peterson" wrote:

I'm even more confused.

This part of the code:

With Target
If .Column = 8 Then

Looks to see if the change was made in column 8 (aka H). If H is locked, then
this event won't do anything--since the user can't change stuff in column H.

But you say Column H is a pick list. (I'm not sure what you mean--I guessed
data|validation, but maybe not.)

But in any case, didn't the unprotecting the sheet/doing the work/reprotecting
the sheet work for you?





Pank Mehta wrote:

I have columns D,F G,I and H locked, as they get popoulated automatically. I
want to move from column C to column D automatically once data is entered
into column A, B and C (and hence unlocked). Column E is unlocked as data is
required. Column F and H are locked as these are automaticall populated once
column E is populated. Column H is a pick list and once a selection is made I
want the date and time to be populated in columns I and J automatically
(hence the VBA) and after an entry in the picklist in column H is selected I
want to be able to be positioned in the next row in column A.

I was led to believe that is one wants to automatically skip to columns one
has to lock/unlock cells and then protect the worksheet.

"Dave Peterson" wrote:

I'm confused about what happens in columns I and J.

Do you have all the cells locked (then the macro would not do much) or do you
have some of the cells locked (and some unlocked)? So writing to a cell would
cause an error and the code would pass to ws_exit and get out.

I'm not quite sure...

But maybe just unprotecting the worksheet, doing the writing, then reprotecting
the worksheet would be sufficient:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ws_exit
With Target
If .Column = 8 Then
Me.Unprotect
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
Me.Protect
End Sub

Pank Mehta wrote:

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 (i.e. Columns D, F, G, I and J). and then protected the sheet without
a password.

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.

--

Dave Peterson


--

Dave Peterson

In my first reply, I added a couple of lines to Bob's code:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ws_exit
With Target
If .Column = 8 Then
Me.Unprotect '<-----------
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
Me.Protect '<-----------
End Sub

Did you try it with those changes?

If you did and it still failed, I'm at a loss. I don't see anything that would
cause column 9 (I) to write, but column 10 (J) to fail.

If you didn't try it, then trying to change a locked cell on that protected
sheet would cause an error. And Bob's code dumps to the ws_exit routine if
there's an error like that.



Pank Mehta wrote:

Dave,

To answer your questions, the code I got was from a gentleman on the
discussion groups. The code works as expected if the worksheet is not
proctected (i.e if I select from the pick list in column H then Cols I and J
get populated with the date and time).

Using your recommendation to unprotect the sheet and then protecting the
sheet within the VBA gave a funny result in that it only populates the date
in column I, column J does not get populated with the date.

Any suggestions??

"Dave Peterson" wrote:

I'm even more confused.

This part of the code:

With Target
If .Column = 8 Then

Looks to see if the change was made in column 8 (aka H). If H is locked, then
this event won't do anything--since the user can't change stuff in column H.

But you say Column H is a pick list. (I'm not sure what you mean--I guessed
data|validation, but maybe not.)

But in any case, didn't the unprotecting the sheet/doing the work/reprotecting
the sheet work for you?





Pank Mehta wrote:

I have columns D,F G,I and H locked, as they get popoulated automatically. I
want to move from column C to column D automatically once data is entered
into column A, B and C (and hence unlocked). Column E is unlocked as data is
required. Column F and H are locked as these are automaticall populated once
column E is populated. Column H is a pick list and once a selection is made I
want the date and time to be populated in columns I and J automatically
(hence the VBA) and after an entry in the picklist in column H is selected I
want to be able to be positioned in the next row in column A.

I was led to believe that is one wants to automatically skip to columns one
has to lock/unlock cells and then protect the worksheet.

"Dave Peterson" wrote:

I'm confused about what happens in columns I and J.

Do you have all the cells locked (then the macro would not do much) or do you
have some of the cells locked (and some unlocked)? So writing to a cell would
cause an error and the code would pass to ws_exit and get out.

I'm not quite sure...

But maybe just unprotecting the worksheet, doing the writing, then reprotecting
the worksheet would be sufficient:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ws_exit
With Target
If .Column = 8 Then
Me.Unprotect
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
Me.Protect
End Sub

Pank Mehta wrote:

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 (i.e. Columns D, F, G, I and J). and then protected the sheet without
a password.

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.

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Dave,

Firstly, apologies, I hadn't noticed the Me.Unprotect after the 'If .Column
= 8 Then', having inserted it and run it it works like a treat as I wanted it.

Again many thanks for your perseverance and assistance.

Regards

Pank


"Dave Peterson" wrote:

In my first reply, I added a couple of lines to Bob's code:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ws_exit
With Target
If .Column = 8 Then
Me.Unprotect '<-----------
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
Me.Protect '<-----------
End Sub

Did you try it with those changes?

If you did and it still failed, I'm at a loss. I don't see anything that would
cause column 9 (I) to write, but column 10 (J) to fail.

If you didn't try it, then trying to change a locked cell on that protected
sheet would cause an error. And Bob's code dumps to the ws_exit routine if
there's an error like that.



Pank Mehta wrote:

Dave,

To answer your questions, the code I got was from a gentleman on the
discussion groups. The code works as expected if the worksheet is not
proctected (i.e if I select from the pick list in column H then Cols I and J
get populated with the date and time).

Using your recommendation to unprotect the sheet and then protecting the
sheet within the VBA gave a funny result in that it only populates the date
in column I, column J does not get populated with the date.

Any suggestions??

"Dave Peterson" wrote:

I'm even more confused.

This part of the code:

With Target
If .Column = 8 Then

Looks to see if the change was made in column 8 (aka H). If H is locked, then
this event won't do anything--since the user can't change stuff in column H.

But you say Column H is a pick list. (I'm not sure what you mean--I guessed
data|validation, but maybe not.)

But in any case, didn't the unprotecting the sheet/doing the work/reprotecting
the sheet work for you?





Pank Mehta wrote:

I have columns D,F G,I and H locked, as they get popoulated automatically. I
want to move from column C to column D automatically once data is entered
into column A, B and C (and hence unlocked). Column E is unlocked as data is
required. Column F and H are locked as these are automaticall populated once
column E is populated. Column H is a pick list and once a selection is made I
want the date and time to be populated in columns I and J automatically
(hence the VBA) and after an entry in the picklist in column H is selected I
want to be able to be positioned in the next row in column A.

I was led to believe that is one wants to automatically skip to columns one
has to lock/unlock cells and then protect the worksheet.

"Dave Peterson" wrote:

I'm confused about what happens in columns I and J.

Do you have all the cells locked (then the macro would not do much) or do you
have some of the cells locked (and some unlocked)? So writing to a cell would
cause an error and the code would pass to ws_exit and get out.

I'm not quite sure...

But maybe just unprotecting the worksheet, doing the writing, then reprotecting
the worksheet would be sufficient:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ws_exit
With Target
If .Column = 8 Then
Me.Unprotect
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
Me.Protect
End Sub

Pank Mehta wrote:

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 (i.e. Columns D, F, G, I and J). and then protected the sheet without
a password.

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.

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

I feel better now.

<vbg

Pank Mehta wrote:

Dave,

Firstly, apologies, I hadn't noticed the Me.Unprotect after the 'If .Column
= 8 Then', having inserted it and run it it works like a treat as I wanted it.

Again many thanks for your perseverance and assistance.

Regards

Pank


"Dave Peterson" wrote:

In my first reply, I added a couple of lines to Bob's code:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ws_exit
With Target
If .Column = 8 Then
Me.Unprotect '<-----------
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
Me.Protect '<-----------
End Sub

Did you try it with those changes?

If you did and it still failed, I'm at a loss. I don't see anything that would
cause column 9 (I) to write, but column 10 (J) to fail.

If you didn't try it, then trying to change a locked cell on that protected
sheet would cause an error. And Bob's code dumps to the ws_exit routine if
there's an error like that.



Pank Mehta wrote:

Dave,

To answer your questions, the code I got was from a gentleman on the
discussion groups. The code works as expected if the worksheet is not
proctected (i.e if I select from the pick list in column H then Cols I and J
get populated with the date and time).

Using your recommendation to unprotect the sheet and then protecting the
sheet within the VBA gave a funny result in that it only populates the date
in column I, column J does not get populated with the date.

Any suggestions??

"Dave Peterson" wrote:

I'm even more confused.

This part of the code:

With Target
If .Column = 8 Then

Looks to see if the change was made in column 8 (aka H). If H is locked, then
this event won't do anything--since the user can't change stuff in column H.

But you say Column H is a pick list. (I'm not sure what you mean--I guessed
data|validation, but maybe not.)

But in any case, didn't the unprotecting the sheet/doing the work/reprotecting
the sheet work for you?





Pank Mehta wrote:

I have columns D,F G,I and H locked, as they get popoulated automatically. I
want to move from column C to column D automatically once data is entered
into column A, B and C (and hence unlocked). Column E is unlocked as data is
required. Column F and H are locked as these are automaticall populated once
column E is populated. Column H is a pick list and once a selection is made I
want the date and time to be populated in columns I and J automatically
(hence the VBA) and after an entry in the picklist in column H is selected I
want to be able to be positioned in the next row in column A.

I was led to believe that is one wants to automatically skip to columns one
has to lock/unlock cells and then protect the worksheet.

"Dave Peterson" wrote:

I'm confused about what happens in columns I and J.

Do you have all the cells locked (then the macro would not do much) or do you
have some of the cells locked (and some unlocked)? So writing to a cell would
cause an error and the code would pass to ws_exit and get out.

I'm not quite sure...

But maybe just unprotecting the worksheet, doing the writing, then reprotecting
the worksheet would be sufficient:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ws_exit
With Target
If .Column = 8 Then
Me.Unprotect
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
Me.Protect
End Sub

Pank Mehta wrote:

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 (i.e. Columns D, F, G, I and J). and then protected the sheet without
a password.

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.

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson