asked on

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

patrickab

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

Saqib Husain

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

patrickab

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

ASKER CERTIFIED SOLUTION

patrickab

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Saqib Husain

Patrick, that was huge...... 2.4 GB? :)

Saqib

patrickab

Saqib,

It was all formatting and absolutely zero data.

Patrick

Saqib Husain

2.4 GB ---> 2.4 MB

patrickab

Correct... it's still a 97.79% reduction

patrickab

FastFord - Thanks for the grade - Patrick

FastFord

ASKER

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.....

patrickab

>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