Need event macro to tab past a (calculated) cell
 

I have an Excel 2003 List (aka Table in Excel 2007). There are some
protected (calculated) columns that I don't want users to enter data into.
But I can't protect the sheet, because that prevents users from entering a
new record at the bottom of the List.

Does anyone have a suggestion on how to deal with this? I'm thinking of
using an event macro invoked when user tabs out of say column C, which makes
the selection skip the cell in column D and go directly to the cell in
column E. I guess I'd have to use Worksheet.SelectionChange. But I don't
know how to do this..

Any help much appreciated!

Need event macro to tab past a (calculated) cell
 

Jeremy
I don't know much about what you want to do, but the following event
macro will select the cell in Column E whenever a cell in Column D is
selected. Does this help? HTH Otto
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count 1 Then Exit Sub
If Target.Column = 4 Then
Application.EnableEvents = False
Target.Offset(, 1).Select
Application.EnableEvents = True
End If
End Sub


"Jeremy" wrote in message
...
I have an Excel 2003 List (aka Table in Excel 2007). There are some
protected (calculated) columns that I don't want users to enter data into.
But I can't protect the sheet, because that prevents users from entering a
new record at the bottom of the List.

Does anyone have a suggestion on how to deal with this? I'm thinking of
using an event macro invoked when user tabs out of say column C, which
makes the selection skip the cell in column D and go directly to the cell
in column E. I guess I'd have to use Worksheet.SelectionChange. But I
don't know how to do this..

Any help much appreciated!

Need event macro to tab past a (calculated) cell
 

On 11 Jan, 20:15, "Otto Moehrbach"
wrote:
Jeremy
* * I don't know much about what you want to do, but the following event
macro will select the cell in Column E whenever a cell in Column D is
selected. *Does this help? *HTH *Otto
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
* * If Target.Count 1 Then Exit Sub
* * If Target.Column = 4 Then
* * * * Application.EnableEvents = False
* * * * * * Target.Offset(, 1).Select
* * * * Application.EnableEvents = True
* * End If
End Sub

"Jeremy" wrote in message

...



I have an Excel 2003 List (aka Table in Excel 2007). There are some
protected (calculated) columns that I don't want users to enter data into.
But I can't protect the sheet, because that prevents users from entering a
new record at the bottom of the List.

Does anyone have a suggestion on how to deal with this? I'm thinking of
using an event macro invoked when user tabs out of say column C, which
makes the selection skip the cell in column D and go directly to the cell
in column E. I guess I'd have to use Worksheet.SelectionChange. But I
don't know how to do this..

Any help much appreciated!– Skjul sitert tekst –

– Vis sitert tekst –

hi jeremy

goto

http://www.erlandsendata.no/english/...ventsworksheet

hope that can help you

Regards Yngve

Need event macro to tab past a (calculated) cell
 

Many thanks... this is exactly what I needed. What is the purpose of the
first statement "If Target.Count 1 Then Exit Sub"? Does it just make the
routine stop each time a cell change happens?

Jeremy
==================================================
"Otto Moehrbach" wrote in message
...
Jeremy
I don't know much about what you want to do, but the following event
macro will select the cell in Column E whenever a cell in Column D is
selected. Does this help? HTH Otto
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count 1 Then Exit Sub
If Target.Column = 4 Then
Application.EnableEvents = False
Target.Offset(, 1).Select
Application.EnableEvents = True
End If
End Sub


"Jeremy" wrote in message
...
I have an Excel 2003 List (aka Table in Excel 2007). There are some
protected (calculated) columns that I don't want users to enter data
into. But I can't protect the sheet, because that prevents users from
entering a new record at the bottom of the List.

Does anyone have a suggestion on how to deal with this? I'm thinking of
using an event macro invoked when user tabs out of say column C, which
makes the selection skip the cell in column D and go directly to the cell
in column E. I guess I'd have to use Worksheet.SelectionChange. But I
don't know how to do this..

Any help much appreciated!

Need event macro to tab past a (calculated) cell
 

No. That simply says to do nothing if a multiple cell selection is made.
HTH Otto

"Jeremy" wrote in message
...
Many thanks... this is exactly what I needed. What is the purpose of the
first statement "If Target.Count 1 Then Exit Sub"? Does it just make the
routine stop each time a cell change happens?

Jeremy
==================================================
"Otto Moehrbach" wrote in message
...
Jeremy
I don't know much about what you want to do, but the following event
macro will select the cell in Column E whenever a cell in Column D is
selected. Does this help? HTH Otto
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count 1 Then Exit Sub
If Target.Column = 4 Then
Application.EnableEvents = False
Target.Offset(, 1).Select
Application.EnableEvents = True
End If
End Sub


"Jeremy" wrote in message
...
I have an Excel 2003 List (aka Table in Excel 2007). There are some
protected (calculated) columns that I don't want users to enter data
into. But I can't protect the sheet, because that prevents users from
entering a new record at the bottom of the List.

Does anyone have a suggestion on how to deal with this? I'm thinking of
using an event macro invoked when user tabs out of say column C, which
makes the selection skip the cell in column D and go directly to the
cell in column E. I guess I'd have to use Worksheet.SelectionChange. But
I don't know how to do this..

Any help much appreciated!