New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

How to automate Time in & out; and detect double entries

Macros, VBA, Excel Automation, etc.

Re: How to automate Time in & out; and detect double entries

Postby NoSparks » Tue Dec 08, 2015 1:49 pm

I believe the altered code in this file will look after your 4 scans.

For the scrolling of the sheet to see the row being dealt with, I've tried to have the row about the fifth one down of the rows in view, I'm sure you will be able to adjust that in the code if need be.

In order to keep the scan to cell C3 always visible you can Freeze the Panes.
Select cell A7 then
on the View Ribbon > Freeze Panes > Freeze Panes

Hopefully this will do the job.
Good Luck
NoSparks
  • 0

You do not have the required permissions to view the files attached to this post.
NoSparks
Excel Hobbyist
 
Posts: 637
Joined: May 28, 2014
Reputation: 103
Excel Version: 2010

Re: How to automate Time in & out; and detect double entries

Postby mhyven » Fri Dec 11, 2015 4:51 am

NoSparks,

please see attached file.

The very first time a particular barcode is scanned in yellow cell (D2)
--> the barcode number itself is written into column C
--> the current time is entered in column D
--> if the same barcode is scanned again in the yellow cell (D2), a pop-up will say “already timed-in”


The very first time a particular barcode is scanned in blue cell (D5)
--> if the barcode was scanned as “timed in” (or scanned in yellow cell – D2), then the current time is entered in column E; if not, then a pop-up will say “not yet timed-in”
--> the Lapsed time is calculated and entered into column F
--> if the same barcode is scanned again in the blue cell (D5), a pop-up will say “already timed-out”


The very first time a particular barcode is scanned in green cell (D8)
--> if the barcode was scanned as “timed in” (or scanned in yellow cell – D2) and “timed out” (or scanned in blue cell – D5), then a series of control number is entered in column B (the control number starts depending on the value entered in A2; must be numerically and ascending); if not, then a pop-up will say either “not yet timed-in and timed-out” or “not yet timed-out” whichever the case may be
--> the current date is entered in column G
--> if the same barcode is scanned again in the green cell (D8), a pop-up will say “process completed”


With the active cell will be shown in the window..

Thank you.
  • 0

You do not have the required permissions to view the files attached to this post.
mhyven
Regular
 
Posts: 29
Joined: Sep 21, 2015
Reputation: 0
Excel Version: 2013

Re: How to automate Time in & out; and detect double entries

Postby NoSparks » Fri Dec 11, 2015 4:17 pm

Hopefully this looks after the adjustments indicated.
Please post back and let me know.
Thanks
  • 0

You do not have the required permissions to view the files attached to this post.
NoSparks
Excel Hobbyist
 
Posts: 637
Joined: May 28, 2014
Reputation: 103
Excel Version: 2010

Re: How to automate Time in & out; and detect double entries

Postby mhyven » Sat Dec 12, 2015 5:06 am

concern #1

It seems that every time we scan a barcode, the selected cell always goes to the column A...

Can you make the selected cell stay on d2, d5, or d8 (whichever is selected)? Like, if we want to scan 3 barcodes consecutively on the yellow cell (d2), the selected cell will always stay on the yellow cell (d2) unless we selected or clicked the blue cell (d5) or green cell (d8).

concern #2

first scan on blue cell (d5) that are not yet timed-in, does not pop-up a message saying "not yet timed-in"

concern #3

b17 always enters "1" on the control number. this is an issue because it is not always the rows on 17 that are scanned first on green cell (d8) which prompts to issue a control number, sometimes the rows on 18 or 19 or 20 are the once that's being scanned first in the green cell (d8) which prompts to enter the first control number which is "1" or whatever is indicated in A2 that signifies the start of the series.
  • 0

mhyven
Regular
 
Posts: 29
Joined: Sep 21, 2015
Reputation: 0
Excel Version: 2013

Re: How to automate Time in & out; and detect double entries

Postby mhyven » Sat Dec 12, 2015 5:24 am

additional request:

is it possible for the control numbers in column B to be filtered from smallest to largest automatically once entered?
if yes, can you please make two attachments, one that can automatically filter the control numbers and another that does not filters.

Thank you.
  • 0

mhyven
Regular
 
Posts: 29
Joined: Sep 21, 2015
Reputation: 0
Excel Version: 2013

Re: How to automate Time in & out; and detect double entries

Postby NoSparks » Sat Dec 12, 2015 12:11 pm

Try this.

For #3 changed A2 to be NEXT control number, increments by code, can be set to anything numerical, which will give what the next D2 scans start at.

additional request -- if you don't want the sort, go to the code and put a ' (that's an apostrophe) in front of Call SortMacro
  • 2

You do not have the required permissions to view the files attached to this post.
NoSparks
Excel Hobbyist
 
Posts: 637
Joined: May 28, 2014
Reputation: 103
Excel Version: 2010

Re: How to automate Time in & out; and detect double entries

Postby mhyven » Sun Dec 13, 2015 1:34 am

NoSparks,

as always you did it!! works perfectly.

Thank you very much!!!
:D :D :D :D :D :D
  • 0

mhyven
Regular
 
Posts: 29
Joined: Sep 21, 2015
Reputation: 0
Excel Version: 2013

Re: How to automate Time in & out; and detect double entries

Postby mhyven » Mon Dec 14, 2015 3:42 am

NoSparks,

Please refer the thread below.

The very first time a particular barcode is scanned in green cell (D8)
--> if the barcode was scanned as “timed in” (or scanned in yellow cell – D2) and “timed out” (or scanned in blue cell – D5), then a series of control number is entered in column B (the control number starts depending on the value entered in A2; must be numerically and ascending); if not, then a pop-up will say either “not yet timed-in and timed-out” or “not yet timed-out” whichever the case may be
--> the current date is entered in column G
--> if the same barcode is scanned again in the green cell (D8), a pop-up will say “process completed”

concern:
--> please refer to the underlined phrase.

situation:
--> the barcode was scanned as "timed-in" but not yet as "timed-out", there should be a pop-up saying "not yet timed-in".
--> barcodes that are not scanned as "timed-in" and "timed-out" is working well
  • 0

mhyven
Regular
 
Posts: 29
Joined: Sep 21, 2015
Reputation: 0
Excel Version: 2013

Re: How to automate Time in & out; and detect double entries

Postby mhyven » Mon Dec 14, 2015 3:48 am

Also, will you please transfer the next control number from A2 to B2

Thank you.
  • 0

mhyven
Regular
 
Posts: 29
Joined: Sep 21, 2015
Reputation: 0
Excel Version: 2013

Re: How to automate Time in & out; and detect double entries

Postby mhyven » Mon Dec 14, 2015 4:09 am

Im sorry, it should be "net yet timed-out"


situation:
--> the barcode was scanned as "timed-in" but not yet as "timed-out", there should be a pop-up saying "not yet timed-in".
--> barcodes that are not scanned as "timed-in" and "timed-out" is working well
  • 0

mhyven
Regular
 
Posts: 29
Joined: Sep 21, 2015
Reputation: 0
Excel Version: 2013

PreviousNext

Return to Macros and VBA Questions

Who is online

Users browsing this forum: No registered users and 52 guests