Excel experts.?

christin, Feb 1, 10:11pm
I'm far from an expert so wondering if someone can help.

I have a spreadsheet that is used by multiple people. What is does is scan a barcode with a scanner each time something leaves. It is used to track time and date of when it's left,

It takes this full barcode and puts it in the left hand column. It then splits out the last eight characters and put into the second column. And I'm the third column it puts the current date and time.

Each scan is with a return so each time the above is done, it then goes to the next row, first column.

What is happening is often the cursor is moved accidentally (busy desk, people not checking before scanning). Is there any way of making it stay in the first column as the stripping and date don't work unless it's in there, and keeps it tidy.

Sorry for the novel, hopefully that's enough info. ?

I will play with it tomorrow but was hoping a step in the right direction. Did the above so have basic knowledge so hopefully :-)

Thanks!

christin, Feb 1, 10:13pm
We did have a wireless keyboard and mouse turned off to stop accidental, but it still managed to move and once it was moved the next entries,would follow in the wrong column until corrected.

Oh and someone stole the mouse so that's now wired!


nice_lady, Feb 2, 6:16am

gyrogearloose, Feb 2, 7:42am
You'd have more control over the interface if you migrated the code from Excel to Access

floydbloke, Feb 2, 7:46am
This may require some fairly heavy-duty VBA to start automatically moving the cursor
May be a 'quick-and-dirty' would be to look at turning protection on and locking all cells except the first column.

christin, Feb 2, 8:01am
tried locking the cells, from memory it stopped other stuff working.

it was a while ago i did the spreadsheet but thats the first thing i tried and rmemeber having issues ;-( ill have another look today and see what i got stuck on .

nice_lady, Feb 2, 8:10am
Easiest way I found to lock cells is to use "data validation" . You can set it up very simply and add an errror message popup if someone tries to use the locked cell. Works very well.

In excell (I'm using 2003 for this but later versions should be very similar)

Choose a cell or range of cells, (drag mouse over them to highlight).
click data on the top menu item row (alt + D brings it up also)
click validation
In the "Allow" box choose custom
put "" into the formula box on the first tab, (double speech marks)
goto the third tab "error alert"
type an error message title - for instance "Problem ! "
then in error message box type something like
"This cell has been LOCKED. Click on CANCEL to continue" (which is what they'll have to do to continue !). You could add "Users MUST only use column A to input data" Or whatever other message will help your. They WON'T be able to use any locked cells.

If/when a user tries to input/modify any info in a locked cell they'll get your Error message popup in a box and will have to choose Cancel to continue. It's pretty simple.

christin, Feb 2, 8:13am
that was an option in the beginning but wanted quick and simple as the users using it just want to scan, walk away. Its a very busy time critical department. Also all sorts of people using it.

May look into that though. But at th etime it was a 20 min job as a "trial" on a temp computer etc. it seems to have stuck (previously it was a book they had to fill out and people were fudging times or not filling out book) so i guess now is the time to refine it. .

was down there fixing some monitors yesterday and a guy came up and told me it was all wrong, it needed to be fixed and to talk to the programmer and it was an easy bit of code. should have told him to fix it. lol. or to stop putting the cursor in the wrong place in the first place!

christin, Feb 2, 8:16am
except they dont want the keyboard and mouse to be on. due to space and people fiddling. they just wnat the spreadsheet to work by scanning and then simply walking away.

the keyboard and mouse is off to the side and only used by one person when transferring the file every few days. theyd have to rearrange the desk to fit it there permanently.

will talk to them and see what they say, but the less interaction with errors etc the better for them :-(

i think i may have gone down that track in the beginning, but like i said will try agian today. its been about six months since i did it so cant remember what i tried and what i didnt! Just remmeber having issues with what i thought would work.

christin, Feb 2, 8:24am
just tried that again. comes up with an error despite bein gin the left most cell.

think thats the issue i had originally. :-/

nice_lady, Feb 2, 8:27am
What comes up with WHAT error when you do WHAT ?
You know how things work around here: Please be specific for us.

christin, Feb 2, 8:28am
It fails on the date part. the left column is entered, so is the second truncated one, but the date part is blank and the error message is up

worked okay till data validation was put on.

christin, Feb 2, 8:29am
the error i put in data valudation. thought that bit was obvious lol.
when i type in left column as mentioned the error i typed in comes up

christin, Feb 2, 8:31am

nice_lady, Feb 2, 8:33am
Then you must have set the validation to the left cell because the validation error box will only popup when someone tried to modify any cell it's set to 'watch' . it's easy to remove from any cell or range of cells though.

christin, Feb 2, 8:35am
happens whether the left cell is included or not.

christin, Feb 2, 8:38am
ill go talk to the people using it first, all this is irrelevant unfortunately if they dont have a keyboard or mouse always available as they cant clear any errors.

christin, Feb 2, 8:39am
or if they dont look at the screen when they scan (i think they just scan and assume it works and walk away. very busy)

think need to work on educating the users first! :-/

christin, Feb 2, 8:45am
ignore that! i had cleared validation except for one cell, the cell i was intering in!

need to see if this is an option due to space for they keyboard and mouse, to be honest i think the user that was moaning was more worried about it than the manager.

it only happens occassionally, but once it does it screws up the spreadsheet for then on! not sure how the cursor is moving if they keyboard is turned off etc.

duncb, Feb 2, 3:58pm
The way to fix this in Excel is to write write a few lines of vba code that activates when the cursor moves to column 1. Check that the the previous row column 3 is not empty. If it is go up a row

christin, Feb 9, 1:04am
Would that work when it goes to say column 15 on the correct row?

Share this thread

Buy me a coffee :)Buy me a coffee :)