Programmable Column Sequencing?
in [Programming]
|
From: G on 25 May 2010 17:35 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
From: JLGWhiz on 25 May 2010 17:59 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. "G" <G(a)discussions.microsoft.com> wrote in message news:4F721629-B91C-4DDC-B54C-D19A6239B7B9(a)microsoft.com... >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
From: Hakyab on 27 May 2010 16:01 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 >>>>>> Static Recursed as Booelan >>>>>> if recursed then exit sub >>>>>> recursed = true > '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 >>>>>> recursed = false > End Sub > Best,
From: GS on 27 May 2010 16:43 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 -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc
From: OssieMac on 27 May 2010 16:46
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 -- Regards, OssieMac |