MACRO QUESTION ????

[JAYC099]

HI, I NEED TO KNOW HOW TO MOVE FROM : C5 TO D8 BY PRESSING TAB A SINGLE TIME

 

[Tim]

if it is always just those two, you could lock all the other cells (ie,
without using VBA)

 

[sbitaxi]

HI, I NEED TO KNOW HOW TO MOVE FROM : C5 TO D8 BY PRESSING TAB A SINGLE TIME

Macros have a built in feature for setting hotkeys, while Tab has a
hardcoded function in Excel and I don't think it can be over-ridden.

Do you want the macro to move only to D8 if you press the hot-key, and
only to work while in cell C5 or do you want it to displace 1 column
and 3 rows?

If the latter -

ActiveCell.Offset(3,1).Select

Or, the other, which will only work if you are in cell C5 -

If ActiveCell.Address = Range("C5") Then
Range("D8").Select
End If

 

[JAYC099]

No, it was an example, I have several cells on my document that I need to
jump from left to right and top to bottom...
Thx

 

[sbitaxi]

No, it was an example, I have several cells on my document that I need to
jump from left to right and top to bottom...
Thx

JAYC099, I'd take Tim's advice and lock the cells/protect the
worksheet except for the cells you need to access. It would be faster
and more efficient. Sounds like you are working in a form or report.
Pressing Tab or Return in those cases would only move you to unlocked
cells.


Steven

 

[JAYC099]

O.K
How do I secure the worksheet ???
thx

JAYC099, I'd take Tim's advice and lock the cells/protect the
worksheet except for the cells you need to access. It would be faster
and more efficient. Sounds like you are working in a form or report.
Pressing Tab or Return in those cases would only move you to unlocked
cells.


Steven

 

[Tim]

select the cells you want to access (you can press CTRL + click each of the
individual cells), right-click in one of the selected cells, choose format
cells. choose Protection, untick locked.

then go to Tools on the menu bar, Protection, Protect Sheet. untick the
option to select locked cells (keep 'select unlocked cells' ticked).

you should then only be able to choose the cells you unlocked.

i'm sure there's a better explanation of this somewhere on the web though!

 

[JAYC099]

Thank you very much

select the cells you want to access (you can press CTRL + click each of the
individual cells), right-click in one of the selected cells, choose format
cells. choose Protection, untick locked.

then go to Tools on the menu bar, Protection, Protect Sheet. untick the
option to select locked cells (keep 'select unlocked cells' ticked).

you should then only be able to choose the cells you unlocked.

i'm sure there's a better explanation of this somewhere on the web though!

 

[Gord Dibben]

Private Sub Worksheet_Change(ByVal Target As Range)
'Anne Troy's taborder event code
Dim aTabOrd As Variant
Dim i As Long

'Set the tab order of input cells
aTabOrd = Array("A5", "B2", "C5", "A10", "B3", "C10")

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

End Sub


Gord Dibben MS Excel MVP

 

[sbitaxi]

Private Sub Worksheet_Change(ByVal Target As Range)
'Anne Troy's taborder event code
    Dim aTabOrd As Variant
    Dim i As Long

     'Set the tab order of input cells
    aTabOrd = Array("A5", "B2", "C5", "A10", "B3", "C10")

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

End Sub

Gord Dibben  MS Excel MVP

Gord:

How do you run this code? I can't seem to activate it.


Steven

 

[Gord Dibben]

Steven

It is sheet event code and runs when data is entered into the cells in the
aTabOrd Array

Right-click on the sheet tab and "View Code"

Copy/paste the code into that sheet module.

Adjust ranges to suit then Alt + q to return to the Excel window.


Gord

 

[sbitaxi]

Steven

It is sheet event code and runs when data is entered into the cells in the
aTabOrd Array

Right-click on the sheet tab and "View Code"

Copy/paste the code into that sheet module.

Adjust ranges to suit then Alt + q to return to the Excel window.

Gord

Great, thank you Gord. I see how it works. I've never used a Private
Sub before.

S