My email address is posted as gorddibbATshawDOTca

Change the AT and DOT to puctuation.


Gord

On Tue, 6 Mar 2007 13:56:28 -0800, JAConnolly
wrote:

as you can probably tell, I am a beginner at this. What does this mean?
Change the AT and DOT to email me.

"Gord Dibben" wrote:

Don't tell your boss but......this is not impossible.

I would have to work on it a while and probably come up with a brute
force(inelegant) solution.

Maybe one of the "loop"ier coders can up with something in the meantime.

I will try to work on it when I can.

If you send me an email with a valid address I can get back to you.

Change the AT and DOT to email me.


Gord

On Tue, 6 Mar 2007 11:14:33 -0800, JAConnolly
wrote:

Ok. Here goes my situation:
If they enter A in C4, I want them to tab to the following cells for data
entry: e4-i4, then m4-o4, then s4-u4, then y4-aa4, then ae4-ah4, then
al4-an4, then ar4-av4, then az4-bc4, then bg4-bj4, then mn4-bq4, then bu4-by4
then cc4-cg4, then ck4-co4, then cs4-cx4, then db4-dd4, then dh4-dj4, then
dn4-dp4, then st4-dv4, then dz4-eb4.

Along with all of this, if they enter P or S in C4, I need them to only
enter data in the following cells: d4, l4, r4, x4, ad4, ak4, aq4, ay4, bf4,
bm4, bt4, cb4, cj4, cr4, da4, dg4, dm4, ds4 and dy4.

At this point when they hit the Tab Key, I want them to the beginning in the
next row. I know this may be impossible, if so, please just say so. I have
spent several days trying to figure out if this can be done, my boss wants it
to be possible.

Thanks for all you have done to help me so far.



"Gord Dibben" wrote:

Do you mean if user enters P or S in C1 you want a certain number of cells to
become available for data entry depending upon value of C1?

If they enter P or S in C2 or C3 or C4 etc. another set of cells becomes
available for data entry?

Possible............yes.

Complex............yes and possibly stretching my limited coding skills.

So far you are the only person who knows what Tab order sequence you need and
which cells would become available for each P or S in whatever cell is selected
in column C.


Gord

On Tue, 6 Mar 2007 08:49:28 -0800, JAConnolly
wrote:

If they enter S or P in Column C, then I want them to tab to specific cells
in sequence for data entry. Is this possible?

"Gord Dibben" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Column < 1 Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False
Select Case LCase(Target.Value)
Case "S"
Target.Offset(0, 2).Select
Case "P"
Target.Offset(0, 5).Select
End Select
endit:
Application.EnableEvents = True
End Sub


Gord

On Mon, 5 Mar 2007 06:29:05 -0800, JAConnolly
wrote:

Thank you. This did work for me. I know nothing about codes. Is it
possible for it to be applied to an entire column rather than a single cell?
If that same column has a different letter such as S or P, I want it to move
to a different cell, is that possible?

"Gord Dibben" wrote:

Cannot be done based upon a cell value without using VBA event code.

Formulas can only return calculated results, not move the cursor.

This event code will do the trick. Enter a or A in C4 and hit ENTER or Tab key
to jump to E4.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("C4")) Is Nothing Then Exit Sub
On Error GoTo CleanUp
Application.EnableEvents = False
With Target
If LCase(.Value) = "a" Then
.Offset(0, 2).Select
End If
End With
CleanUp:
Application.EnableEvents = True
End Sub

Right-click on the sheet tab and "View Code". Copy/paste the code into that
sheet module.


Gord Dibben MS Excel MVP

On Fri, 2 Mar 2007 15:19:45 -0800, JAConnolly
wrote:

How do I move to a specific cell in Excel?

For Example: If C4 = A then move to E4 using the tab key