Tabing or Offset function with Excel

In both of my spread sheets I have the same problem. Both sheets are the same except for how I present the calendar (1 with the calendar control, the other with VB Scripting). The problem is after the operators choose a date in column A and it is inserted, I offset 2 columns to column C (Hidden) where I wait for them to scan in a data string. This column remains hidden because of page size for printing and because I need to strip 2 characters off of the front of the data string to get the format I want in the column B. I needed the formula in column B to get desired data and present nothing (#VALUE) if nothing was entered in column C , Which would mess up my 'Print me' macro that sets print area to last cell with real data. My issue is that once the operators scan in the string in column C the active cell gets 'stuck' there. The data posts to column B as it should but the active cell remains C. The scanner appends a Tab command that should move the active cell over to column D but it doesnt (it works on cells with out the formula...) Even the Tab key on the keyboard will not Tab over from that cell. You have to mouse click to get out of it to column D. Is there some scripting or a macro I could use to get this over to column D? I am trying to automize this as much as possible for my users. I hope the explanation was not to long winded and made sense....


Erik


SDOM-1-F01--Part-Shipping-Log-Fo.xls
SDOM-1-F01--Part-Shipping-Log-Fo.xls
FastFordAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

patrickabCommented:
FastFord,

Put this in the code pane of Sheet1:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C:C")) Is Nothing Then
  Cells(Target.Row, "A").Select
End If
End Sub

As soon as a cell is changed in column in column C it will automatically select column A on the same row.

It's in the attached file.

Hope that helps

Patrick
SDOM-1-F01--Part-Shipping-Log-Fo.xls
0
Saqib Husain, SyedEngineerCommented:
This is not for points

I thought you want to move the selection to column D. If this is the case then change

  Cells(Target.Row, "A").Select

to

  Cells(Target.Row, "D").Select
0
patrickabCommented:
FastFord,

BTW may I suggest that for files that you upload here on EE, that you always reduce them in size to the absolute minimum so as to minimise the time taken to down and upload them. That also minimises everyone's bandwidth usage.

I have drastically reduced the size of your file as all the formatting was not needed to illustrate the problem - nor the solution for that matter. The file-size reduction was from 2.4GB to 53kB.

Patrick
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

patrickabCommented:
ssaqibh,

My mis-reading of the question. My solution should have read:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C:C")) Is Nothing Then
  Cells(Target.Row, "D").Select
End If
End Sub

Thanks for catching that.

Patrick
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Saqib Husain, SyedEngineerCommented:
Patrick, that was huge...... 2.4 GB?   :)

Saqib
0
patrickabCommented:
Saqib,

It was all formatting and absolutely zero data.

Patrick
0
Saqib Husain, SyedEngineerCommented:
2.4 GB ---> 2.4 MB
0
patrickabCommented:
Correct... it's still a 97.79% reduction
0
patrickabCommented:
FastFord - Thanks for the grade - Patrick
0
FastFordAuthor Commented:
Thanks for the help. @ 2.4Mb I did not think I was going to crash any servers or plug up the wire in between. Next time I will see if I can trim it down some. However then someone would say that I did not post enough information.....
0
patrickabCommented:
>Next time I will see if I can trim it down some. However then someone would say that I did not post enough information.....

It's very rare than anyone complains about lack of data. Usually far more is provided than is need to solve a problem. Usually a small amount of representative data is all that's needed.

I usually avoid downloading large files, as I think it's such a waste of resources. In fact I have just decided to give one question a miss because the file is 2.4MB.  I have a reasonable internet connection and a 5Gb monthly allowance but I still avoid downloading large files.

Patrick
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.