Jumping colums in Excel 2000

[Lolly]

In a spreadsheet using about 20 columns, after entering a letter in Column C
or just pressing Enter, I need to jump from Column C to Column E. It has been
brought to my attention that I need to create a Macro for this function, but
unfortunately I cannot find one that works. I think the Macro may relate to
SelectionChange.
There is a vlookup equation in Column D and that works brilliantly.

Help would be greatly appreciated.

[Ed Ferrero]

Hi Lolly,

You don't need a macro.

1) Select the cells that need to be edited, use the menu command
Format - Cells... Click the Protection tab, uncheck the Locked box

2) Then use the menu command Tools - Protection - Protect Sheet
Make sure the 'Select Locked Cells' option is unchecked and click ok.

3) Use menu command Tools - Options, click the Edit tab, change the
move selection after Enter direction to 'Right'

So, for instance, if you unlocked columns A to C and E to F in step 1
then Starting in cell A1 and pressing Enter repeatedly would select
cells A1, B1, C1, E1, F1, A2, B2....

Ed Ferrero
http://edferrero.m6.net/


> In a spreadsheet using about 20 columns, after entering a letter in Column
> C
> or just pressing Enter, I need to jump from Column C to Column E. It has
> been
> brought to my attention that I need to create a Macro for this function,
> but
> unfortunately I cannot find one that works. I think the Macro may relate
> to
> SelectionChange.
> There is a vlookup equation in Column D and that works brilliantly.
>
> Help would be greatly appreciated.

[kjs]

"Ed Ferrero" <mail@edferrero.m6.net> wrote in message
news:O5JTtom7FHA.3804@TK2MSFTNGP14.phx.gbl...

> 2) Then use the menu command Tools - Protection - Protect Sheet
> Make sure the 'Select Locked Cells' option is unchecked and click ok.

Doesn't that only appear in Excel 2002?
Lolly is looking for a 2000 solution, yah?

Kevin

[Gord Dibben]

In Excel 2000 use the Tab key to jump to unprotected cells.

Follow the rest of Ed'd advice about unprotecting cells to jump to.


Gord Dibben Excel MVP

On Mon, 21 Nov 2005 11:26:54 -0000, "kjs" <1@2.3> wrote:

>"Ed Ferrero" <mail@edferrero.m6.net> wrote in message
>news:O5JTtom7FHA.3804@TK2MSFTNGP14.phx.gbl...
>
>> 2) Then use the menu command Tools - Protection - Protect Sheet
>> Make sure the 'Select Locked Cells' option is unchecked and click ok.
>
>Doesn't that only appear in Excel 2002?
>Lolly is looking for a 2000 solution, yah?
>
>Kevin
>

[Lolly]

Hi Ed,
Thank you for your response. Sorry, but I think you misunderstood my query.
I want to be able toautomatically jump from column C to column E without
having to press Enter again in column D or use the Tab key from D.

"Ed Ferrero" wrote:

> Hi Lolly,
>
> You don't need a macro.
>
> 1) Select the cells that need to be edited, use the menu command
> Format - Cells... Click the Protection tab, uncheck the Locked box
>
> 2) Then use the menu command Tools - Protection - Protect Sheet
> Make sure the 'Select Locked Cells' option is unchecked and click ok.
>
> 3) Use menu command Tools - Options, click the Edit tab, change the
> move selection after Enter direction to 'Right'
>
> So, for instance, if you unlocked columns A to C and E to F in step 1
> then Starting in cell A1 and pressing Enter repeatedly would select
> cells A1, B1, C1, E1, F1, A2, B2....
>
> Ed Ferrero
> http://edferrero.m6.net/
>
>
> > In a spreadsheet using about 20 columns, after entering a letter in Column
> > C
> > or just pressing Enter, I need to jump from Column C to Column E. It has
> > been
> > brought to my attention that I need to create a Macro for this function,
> > but
> > unfortunately I cannot find one that works. I think the Macro may relate
> > to
> > SelectionChange.
> > There is a vlookup equation in Column D and that works brilliantly.
> >
> > Help would be greatly appreciated.
>
>
>

[Ed Ferrero]

Hi Lolly,

You could place these two macros in the worksheet code pane.
(open the VBE by pressing Alt-F11, then double click Sheet1 in the list at
the left)

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 5 Then
Target.Offset(0, 2).Select
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 6 Then
Target.Offset(0, 1).Select
End If
End Sub

BUT - you still need to press Enter after typing a number in column D.
I don't know how to do what you want without using the Enter key.

Ed Ferrero
http://edferrero.m6.net/

> Hi Ed,
> Thank you for your response. Sorry, but I think you misunderstood my
> query.
> I want to be able toautomatically jump from column C to column E without
> having to press Enter again in column D or use the Tab key from D.
>
> "Ed Ferrero" wrote:
>
>> Hi Lolly,
>>
>> You don't need a macro.
>>
>> 1) Select the cells that need to be edited, use the menu command
>> Format - Cells... Click the Protection tab, uncheck the Locked box
>>
>> 2) Then use the menu command Tools - Protection - Protect Sheet
>> Make sure the 'Select Locked Cells' option is unchecked and click ok.
>>
>> 3) Use menu command Tools - Options, click the Edit tab, change the
>> move selection after Enter direction to 'Right'
>>
>> So, for instance, if you unlocked columns A to C and E to F in step 1
>> then Starting in cell A1 and pressing Enter repeatedly would select
>> cells A1, B1, C1, E1, F1, A2, B2....
>>
>> Ed Ferrero
>> http://edferrero.m6.net/
>>
>>
>> > In a spreadsheet using about 20 columns, after entering a letter in
>> > Column
>> > C
>> > or just pressing Enter, I need to jump from Column C to Column E. It
>> > has
>> > been
>> > brought to my attention that I need to create a Macro for this
>> > function,
>> > but
>> > unfortunately I cannot find one that works. I think the Macro may
>> > relate
>> > to
>> > SelectionChange.
>> > There is a vlookup equation in Column D and that works brilliantly.
>> >
>> > Help would be greatly appreciated.
>>
>>
>>