Programmable Column Sequencing?

[G]

I am currently using Excel 2003 and 2007 if necessary.

I would like the ability to configure the excel sheet column sequencing when
I hit the enter key or tab key. Is this possible?? Any suggestions???


Thank You,

G

 

[JLGWhiz]

Can you illustrate what you mean by column sequencing? Do you mean
rearrange the column header designation or do you mean sort the data in the
columns or do you mean move the data in the columns from one column to
another? Examples help.

 

[Hakyab]

OssieMac, your code will surely render the workbook inoperable, as each
select statement will trigger the change event, resulting in an infinite loop.

May I offer a modification:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim strCol As String

'Assign the column Alpha Id to variable
strCol = Split(Columns(Target.Column) _
.Address(, 0), ":")(1)

Select Case strCol
Case "A"
Cells(Target.Row, "D").Select

Case "B"
Cells(Target.Row, "E").Select

Case "C"
Cells(Target.Row, "K").Select

Case "AB"
Cells(Target.Row, "AZ").Select

End Select
End Sub

Best,

 

[GS]

Adding to JGLWhiz's suggestion...

I would insert a 'program row' (ie: hidden) at the top of the sheet
(Rows(1)), where you can enter the column label for the target cell.
Then use a single statement inside an If construct as follows:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Cells(1, Target.Column) = "" Then
Cells(Target.Row, Cells(1, Target.Column).Text).Select
End If
End Sub

HTH

 

[OssieMac]

Hi,

I don't think there is any way of just setting a tab order on the worksheet
but if you are actually entering and/or changing data on the worksheet then
the following code will select the cell in the next required column on the
same row.

Not sure how savvy you are with VBA but to install the code, right click on
the worksheet tab name and select View Code. Copy the code and paste into the
VBA editor. Click the red cross at top right of VBA editor to close the
editor.

Just edit the case statements and the following line for the required order.
Add more case statements as required before the End Select line.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim strCol As String

'Assign the column Alpha Id to variable
strCol = Split(Columns(Target.Column) _
.Address(, 0), ":")(1)

Select Case strCol
Case "A"
Cells(Target.Row, "D").Select

Case "B"
Cells(Target.Row, "E").Select

Case "C"
Cells(Target.Row, "K").Select

Case "AB"
Cells(Target.Row, "AZ").Select

End Select

End Sub

 

[G]

Lets say we have 5 columns (A,B,C,D,E). Normally, When you are in column A,

when you hit the Tab Key it goes to column B, then Column C. I like a way
to rearrange the order. For example if I am in Column A, and hit a tab, I
may want to go to Column D, and then Column E.

I hope that helps.


G

 

[GS]

G explained on 5/27/2010 :

Lets say we have 5 columns (A,B,C,D,E). Normally, When you are in column A,

when you hit the Tab Key it goes to column B, then Column C. I like a way
to rearrange the order. For example if I am in Column A, and hit a tab, I
may want to go to Column D, and then Column E.

I hope that helps.


G

My suggestion works when you leave edit mode in the cell, however that
happens. That means whether you use the Tab, Enter, or arrow keys it
selects whatever column a label has been entered in the first row. For
example, to have the active cell move to ColD from ColA, enter D in the
first row of ColA. Otherwise, tab behavior will behave normally if
nothing is entered in the first row of any column.

 

[OssieMac]

Hakyab,

I've been away and hense did not get back to this sooner. However, I think
you are confusing Worksheet_SelectionChange with Worksheet_Change. The code I
posted only fires if an actual change is made to the data in the cell. I
included the following to clarify this "if you are actually entering and/or
changing data on the worksheet"

I tested the code before posting.