macros - navigating

[Jeff]

How do I make a macro force my spreadsheet to jump to the next cell after I
hit enter. For ex If i'm in cell a2 and I hit enter I want my cursor to go
to b5. After I hit enter when I'm in b5 I want the cursor to go to c7 etc etc?
Thanks
Jeff

[GCF]

Protecting all other cells will accomplish what you are describing. It will tab you from a2 to b5, to c7 and back to a2.

[Dick Kusleika]

Jeff

If you unlock those cells and protect the sheet, then you can tab from
unprotected cell to unprotected cell. Enter still doesn't work like that,
though.

If that isn't something you want to do, try this
http://www.*****-blog.com/archives/2...rder-of-cells/

--
**** Kusleika
Excel MVP
Daily Dose of Excel
www.*****-blog.com

Jeff wrote:
> How do I make a macro force my spreadsheet to jump to the next cell
> after I hit enter. For ex If i'm in cell a2 and I hit enter I want
> my cursor to go to b5. After I hit enter when I'm in b5 I want the
> cursor to go to c7 etc etc? Thanks
> Jeff

[Jeff]

Thanks,
I used the link you provided and that's exactly what I needed.
Thanks

"**** Kusleika" wrote:

> Jeff
>
> If you unlock those cells and protect the sheet, then you can tab from
> unprotected cell to unprotected cell. Enter still doesn't work like that,
> though.
>
> If that isn't something you want to do, try this
> http://www.*****-blog.com/archives/2...rder-of-cells/
>
> --
> **** Kusleika
> Excel MVP
> Daily Dose of Excel
> www.*****-blog.com
>
> Jeff wrote:
> > How do I make a macro force my spreadsheet to jump to the next cell
> > after I hit enter. For ex If i'm in cell a2 and I hit enter I want
> > my cursor to go to b5. After I hit enter when I'm in b5 I want the
> > cursor to go to c7 etc etc? Thanks
> > Jeff
>
>
>

[Jeff]

On second thought I need a little more help.
Protecting the sheet and unprotecting cells doesn't do it since the tab
order doesn't mesh with left to right top to bottom on my form. The link that
has the change function looks like it would be perfect but I couldn't get it
to work. Here's the code from the link below. I copied this into a module in
my spreadsheet. It doesn't seem to execute at all. Not sure if the module is
wrong, or more likely how I put it into my excel spreadsheet in the modules
that is wrong. Any additional assistance would be appreciated.
Jeff

Private Sub Worksheet_Change(ByVal Target As Range)

Dim aTabOrder As Variant
Dim i As Long

‘Set the tab order of input cells
aTabOrder = Array(�A1″, “C1″, “G3″, “B5″, “E1″, “F4″)

‘Loop through the array of cell address
For i = LBound(aTabOrder) To UBound(aTabOrder)
‘If the changed cell is in the array
If aTabOrder(i) = Target.Address(0, 0) Then
‘If the changed cell is the last array element
If i = UBound(aTabOrder) Then
‘Select the first cell in the array
Me.Range(aTabOrder(LBound(aTabOrder))).Select
Else
‘Select the next cell in the array
Me.Range(aTabOrder(i + 1)).Select
End If
End If
Next i

End Sub













"Jeff" wrote:

> Thanks,
> I used the link you provided and that's exactly what I needed.
> Thanks
>
> "**** Kusleika" wrote:
>
> > Jeff
> >
> > If you unlock those cells and protect the sheet, then you can tab from
> > unprotected cell to unprotected cell. Enter still doesn't work like that,
> > though.
> >
> > If that isn't something you want to do, try this
> > http://www.*****-blog.com/archives/2...rder-of-cells/
> >
> > --
> > **** Kusleika
> > Excel MVP
> > Daily Dose of Excel
> > www.*****-blog.com
> >
> > Jeff wrote:
> > > How do I make a macro force my spreadsheet to jump to the next cell
> > > after I hit enter. For ex If i'm in cell a2 and I hit enter I want
> > > my cursor to go to b5. After I hit enter when I'm in b5 I want the
> > > cursor to go to c7 etc etc? Thanks
> > > Jeff
> >
> >
> >

[Shawn O'Donnell]

"Jeff" wrote:
> Protecting the sheet and unprotecting cells doesn't do it since the tab
> order doesn't mesh with left to right top to bottom on my form.

Have you tried using OnKey? The following call:

Application.OnKey "~", "YourNavigationProcedure"

will hijack the enter key. After you call this method,
YourNavigationProcedure will execute when someone hits Enter, rather than
doing the normal Enter thing (however you have that configured.)

Application.OnKey "~"

Resets the default behavior of the Enter key.

Then all you'd have to do is write YourNavigationProcedure and figure out
how you know when it's time to set the Enter key back to doing its normal
thing.

This seems like a rather risky approach, though. You can really frustrate
your users by messing with the basic functionality of the application.

On the other hand, April 1 is approaching...

[Dick Kusleika]

Jeff

That sub goes into the worksheet's module, not a standard module. In the
VBE's Project Explorer, you should have a module called ThisWorkbook, a
module for every sheet named Sheet1, Sheet2, etc. (unless you changed them)
and any standard or class modules you added. The sheet module that
corresponds to your form is the module for this code.

--
**** Kusleika
Excel MVP
Daily Dose of Excel
www.*****-blog.com

Jeff wrote:
> On second thought I need a little more help.
> Protecting the sheet and unprotecting cells doesn't do it since the
> tab order doesn't mesh with left to right top to bottom on my form.
> The link that has the change function looks like it would be perfect
> but I couldn't get it to work. Here's the code from the link below. I
> copied this into a module in my spreadsheet. It doesn't seem to
> execute at all. Not sure if the module is wrong, or more likely how I
> put it into my excel spreadsheet in the modules that is wrong. Any
> additional assistance would be appreciated. Jeff
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> Dim aTabOrder As Variant
> Dim i As Long
>
> 'Set the tab order of input cells
> aTabOrder = Array("A1?, "C1?, "G3?, "B5?, "E1?, "F4?)
>
> 'Loop through the array of cell address
> For i = LBound(aTabOrder) To UBound(aTabOrder)
> 'If the changed cell is in the array
> If aTabOrder(i) = Target.Address(0, 0) Then
> 'If the changed cell is the last array element
> If i = UBound(aTabOrder) Then
> 'Select the first cell in the array
> Me.Range(aTabOrder(LBound(aTabOrder))).Select
> Else
> 'Select the next cell in the array
> Me.Range(aTabOrder(i + 1)).Select
> End If
> End If
> Next i
>
> End Sub
>
>
>
>
>
>
>
>
>
>
>
>
>
> "Jeff" wrote:
>
>> Thanks,
>> I used the link you provided and that's exactly what I needed.
>> Thanks
>>
>> "**** Kusleika" wrote:
>>
>>> Jeff
>>>
>>> If you unlock those cells and protect the sheet, then you can tab
>>> from unprotected cell to unprotected cell. Enter still doesn't
>>> work like that, though.
>>>
>>> If that isn't something you want to do, try this
>>> http://www.*****-blog.com/archives/2...rder-of-cells/
>>>
>>> --
>>> **** Kusleika
>>> Excel MVP
>>> Daily Dose of Excel
>>> www.*****-blog.com
>>>
>>> Jeff wrote:
>>>> How do I make a macro force my spreadsheet to jump to the next cell
>>>> after I hit enter. For ex If i'm in cell a2 and I hit enter I want
>>>> my cursor to go to b5. After I hit enter when I'm in b5 I want the
>>>> cursor to go to c7 etc etc? Thanks
>>>> Jeff

[JulieD]

or to get there a shorter way (IMHO) right mouse click on the sheet tab
where you want to use the code on and choose view code - you'll be in the
right place then.

Cheers
JulieD

"**** Kusleika" <[email protected]> wrote in message
news:[email protected]...
> Jeff
>
> That sub goes into the worksheet's module, not a standard module. In the
> VBE's Project Explorer, you should have a module called ThisWorkbook, a
> module for every sheet named Sheet1, Sheet2, etc. (unless you changed
> them) and any standard or class modules you added. The sheet module that
> corresponds to your form is the module for this code.
>
> --
> **** Kusleika
> Excel MVP
> Daily Dose of Excel
> www.*****-blog.com
>
> Jeff wrote:
>> On second thought I need a little more help.
>> Protecting the sheet and unprotecting cells doesn't do it since the
>> tab order doesn't mesh with left to right top to bottom on my form.
>> The link that has the change function looks like it would be perfect
>> but I couldn't get it to work. Here's the code from the link below. I
>> copied this into a module in my spreadsheet. It doesn't seem to
>> execute at all. Not sure if the module is wrong, or more likely how I
>> put it into my excel spreadsheet in the modules that is wrong. Any
>> additional assistance would be appreciated. Jeff
>>
>> Private Sub Worksheet_Change(ByVal Target As Range)
>>
>> Dim aTabOrder As Variant
>> Dim i As Long
>>
>> 'Set the tab order of input cells
>> aTabOrder = Array("A1?, "C1?, "G3?, "B5?, "E1?, "F4?)
>>
>> 'Loop through the array of cell address
>> For i = LBound(aTabOrder) To UBound(aTabOrder)
>> 'If the changed cell is in the array
>> If aTabOrder(i) = Target.Address(0, 0) Then
>> 'If the changed cell is the last array element
>> If i = UBound(aTabOrder) Then
>> 'Select the first cell in the array
>> Me.Range(aTabOrder(LBound(aTabOrder))).Select
>> Else
>> 'Select the next cell in the array
>> Me.Range(aTabOrder(i + 1)).Select
>> End If
>> End If
>> Next i
>>
>> End Sub
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>> "Jeff" wrote:
>>
>>> Thanks,
>>> I used the link you provided and that's exactly what I needed.
>>> Thanks
>>>
>>> "**** Kusleika" wrote:
>>>
>>>> Jeff
>>>>
>>>> If you unlock those cells and protect the sheet, then you can tab
>>>> from unprotected cell to unprotected cell. Enter still doesn't
>>>> work like that, though.
>>>>
>>>> If that isn't something you want to do, try this
>>>> http://www.*****-blog.com/archives/2...rder-of-cells/
>>>>
>>>> --
>>>> **** Kusleika
>>>> Excel MVP
>>>> Daily Dose of Excel
>>>> www.*****-blog.com
>>>>
>>>> Jeff wrote:
>>>>> How do I make a macro force my spreadsheet to jump to the next cell
>>>>> after I hit enter. For ex If i'm in cell a2 and I hit enter I want
>>>>> my cursor to go to b5. After I hit enter when I'm in b5 I want the
>>>>> cursor to go to c7 etc etc? Thanks
>>>>> Jeff
>
>

[Dick Kusleika]

Good one, JulieD. Thanks.

--
**** Kusleika
Excel MVP
Daily Dose of Excel
www.*****-blog.com

JulieD wrote:
> or to get there a shorter way (IMHO) right mouse click on the sheet
> tab where you want to use the code on and choose view code - you'll
> be in the right place then.
>
> Cheers
> JulieD
>
> "**** Kusleika" <[email protected]> wrote in message
> news:[email protected]...
>> Jeff
>>
>> That sub goes into the worksheet's module, not a standard module. In the
>> VBE's Project Explorer, you should have a module called
>> ThisWorkbook, a module for every sheet named Sheet1, Sheet2, etc.
>> (unless you changed them) and any standard or class modules you
>> added. The sheet module that corresponds to your form is the module
>> for this code. --
>> **** Kusleika
>> Excel MVP
>> Daily Dose of Excel
>> www.*****-blog.com
>>
>> Jeff wrote:
>>> On second thought I need a little more help.
>>> Protecting the sheet and unprotecting cells doesn't do it since the
>>> tab order doesn't mesh with left to right top to bottom on my form.
>>> The link that has the change function looks like it would be perfect
>>> but I couldn't get it to work. Here's the code from the link below.
>>> I copied this into a module in my spreadsheet. It doesn't seem to
>>> execute at all. Not sure if the module is wrong, or more likely how
>>> I put it into my excel spreadsheet in the modules that is wrong. Any
>>> additional assistance would be appreciated. Jeff
>>>
>>> Private Sub Worksheet_Change(ByVal Target As Range)
>>>
>>> Dim aTabOrder As Variant
>>> Dim i As Long
>>>
>>> 'Set the tab order of input cells
>>> aTabOrder = Array("A1?, "C1?, "G3?, "B5?, "E1?, "F4?)
>>>
>>> 'Loop through the array of cell address
>>> For i = LBound(aTabOrder) To UBound(aTabOrder)
>>> 'If the changed cell is in the array
>>> If aTabOrder(i) = Target.Address(0, 0) Then
>>> 'If the changed cell is the last array element
>>> If i = UBound(aTabOrder) Then
>>> 'Select the first cell in the array
>>> Me.Range(aTabOrder(LBound(aTabOrder))).Select
>>> Else
>>> 'Select the next cell in the array
>>> Me.Range(aTabOrder(i + 1)).Select
>>> End If
>>> End If
>>> Next i
>>>
>>> End Sub
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> "Jeff" wrote:
>>>
>>>> Thanks,
>>>> I used the link you provided and that's exactly what I needed.
>>>> Thanks
>>>>
>>>> "**** Kusleika" wrote:
>>>>
>>>>> Jeff
>>>>>
>>>>> If you unlock those cells and protect the sheet, then you can tab
>>>>> from unprotected cell to unprotected cell. Enter still doesn't
>>>>> work like that, though.
>>>>>
>>>>> If that isn't something you want to do, try this
>>>>> http://www.*****-blog.com/archives/2...rder-of-cells/
>>>>>
>>>>> --
>>>>> **** Kusleika
>>>>> Excel MVP
>>>>> Daily Dose of Excel
>>>>> www.*****-blog.com
>>>>>
>>>>> Jeff wrote:
>>>>>> How do I make a macro force my spreadsheet to jump to the next
>>>>>> cell after I hit enter. For ex If i'm in cell a2 and I hit
>>>>>> enter I want my cursor to go to b5. After I hit enter when I'm
>>>>>> in b5 I want the cursor to go to c7 etc etc? Thanks
>>>>>> Jeff

[Jeff]

Thanks,

I got it to work. (Actually, you guys did).
Thanks
Jeff


"JulieD" wrote:

> or to get there a shorter way (IMHO) right mouse click on the sheet tab
> where you want to use the code on and choose view code - you'll be in the
> right place then.
>
> Cheers
> JulieD
>
> "**** Kusleika" <[email protected]> wrote in message
> news:[email protected]...
> > Jeff
> >
> > That sub goes into the worksheet's module, not a standard module. In the
> > VBE's Project Explorer, you should have a module called ThisWorkbook, a
> > module for every sheet named Sheet1, Sheet2, etc. (unless you changed
> > them) and any standard or class modules you added. The sheet module that
> > corresponds to your form is the module for this code.
> >
> > --
> > **** Kusleika
> > Excel MVP
> > Daily Dose of Excel
> > www.*****-blog.com
> >
> > Jeff wrote:
> >> On second thought I need a little more help.
> >> Protecting the sheet and unprotecting cells doesn't do it since the
> >> tab order doesn't mesh with left to right top to bottom on my form.
> >> The link that has the change function looks like it would be perfect
> >> but I couldn't get it to work. Here's the code from the link below. I
> >> copied this into a module in my spreadsheet. It doesn't seem to
> >> execute at all. Not sure if the module is wrong, or more likely how I
> >> put it into my excel spreadsheet in the modules that is wrong. Any
> >> additional assistance would be appreciated. Jeff
> >>
> >> Private Sub Worksheet_Change(ByVal Target As Range)
> >>
> >> Dim aTabOrder As Variant
> >> Dim i As Long
> >>
> >> 'Set the tab order of input cells
> >> aTabOrder = Array("A1?, "C1?, "G3?, "B5?, "E1?, "F4?)
> >>
> >> 'Loop through the array of cell address
> >> For i = LBound(aTabOrder) To UBound(aTabOrder)
> >> 'If the changed cell is in the array
> >> If aTabOrder(i) = Target.Address(0, 0) Then
> >> 'If the changed cell is the last array element
> >> If i = UBound(aTabOrder) Then
> >> 'Select the first cell in the array
> >> Me.Range(aTabOrder(LBound(aTabOrder))).Select
> >> Else
> >> 'Select the next cell in the array
> >> Me.Range(aTabOrder(i + 1)).Select
> >> End If
> >> End If
> >> Next i
> >>
> >> End Sub
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >> "Jeff" wrote:
> >>
> >>> Thanks,
> >>> I used the link you provided and that's exactly what I needed.
> >>> Thanks
> >>>
> >>> "**** Kusleika" wrote:
> >>>
> >>>> Jeff
> >>>>
> >>>> If you unlock those cells and protect the sheet, then you can tab
> >>>> from unprotected cell to unprotected cell. Enter still doesn't
> >>>> work like that, though.
> >>>>
> >>>> If that isn't something you want to do, try this
> >>>> http://www.*****-blog.com/archives/2...rder-of-cells/
> >>>>
> >>>> --
> >>>> **** Kusleika
> >>>> Excel MVP
> >>>> Daily Dose of Excel
> >>>> www.*****-blog.com
> >>>>
> >>>> Jeff wrote:
> >>>>> How do I make a macro force my spreadsheet to jump to the next cell
> >>>>> after I hit enter. For ex If i'm in cell a2 and I hit enter I want
> >>>>> my cursor to go to b5. After I hit enter when I'm in b5 I want the
> >>>>> cursor to go to c7 etc etc? Thanks
> >>>>> Jeff
> >
> >
>
>
>