Doubleclick, Time and find next row with ...

[autigers]

Hello all ... after weeks of searching and building and copying and pasting etc from this wonderful board that houses some of the sharpest minds I've encountered ... I have an issue reqarding a double click event I wish to envoke ....
I have ws with colums D&E serving as start time and end time respectively.
In column A is a system userid. Each row from rows 3:37 will have only one user id (no duplicates) beginning at row 40:150 is where the second, third, fourth etc .. entries are placed, along with the times that individual performed a job(column C). As seen in the pic below ...
<SCRIPT language=JavaScript src="http://www.interq.or.jp/sun/puremis/colo/popup.js"></SCRIPT><CENTER><TABLE cellSpacing=0 cellPadding=0 align=center><TBODY><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid" bgColor=#0c266b colSpan=10><TABLE width="100%" align=center border=0><TBODY><TR><TD align=left><FONT color=white>Microsoft Excel - TimeTrac.xls</FONT></TD><TD style="FONT-SIZE: 9pt; COLOR: #ffffff; FONT-FAMILY: caption" align=right>___Running: xl2002 XP : OS = Windows XP </FONT></TD></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; HEIGHT: 25px" bgColor=#d4d0c8 colSpan=10><TABLE width="100%" align=center border=0 VALIGN="MIDDLE"><TBODY><TR><TD style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: caption">(<U>F</U>)ile (<U>E</U>)dit (<U>V</U>)iew (<U>I</U>)nsert (<U>O</U>)ptions (<U>T</U>)ools (<U>D</U>)ata (<U>W</U>)indow (<U>H</U>)elp (<U>A</U>)bout</TD><TD vAlign=center align=right><FORM name=formCb755237><INPUT onclick='window.clipboardData.setData("Text",document.formFb078704.sltNb935705.value);' type=button value="Copy Formula" name=btCb873980></FORM></TD></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid" bgColor=white colSpan=10><TABLE border=0><TBODY><TR><FORM name=formFb078704><TD style="WIDTH: 60px" align=middle bgColor=white><SELECT onchange="document.formFb078704.txbFb426622.value = document.formFb078704.sltNb935705.value" name=sltNb935705><OPTION value='=TEXT($I$1,"DDDD")' selected>I2</OPTION></SELECT></TD><TD align=right width="3%" bgColor=#d4d0c8>=</TD><TD align=left bgColor=white><INPUT size=80 value='=TEXT($I$1,"DDDD")' name=txbFb426622></TD></FORM></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%">
</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>A</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>B</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>C</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>D</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>E</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>F</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>G</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>H</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>I</CENTER></TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>1</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 12pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-FAMILY: Bookman; BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center">WMS Logon</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 12pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-FAMILY: Bookman; BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center">OT</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 12pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-FAMILY: Bookman; BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center">Job</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 12pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-FAMILY: Bookman; BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center">Start</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 12pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-FAMILY: Bookman; BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center">Finish</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 12pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-FAMILY: Bookman; BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center">Lunch</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 12pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-FAMILY: Bookman; BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center">Job Hours</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 12pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Bookman; BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center">Total Hours</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 11pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Book Antiqua; BACKGROUND-COLOR: #ff0000; TEXT-ALIGN: left">November 30, 2005</TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>2</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 1.5pt solid; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Book Antiqua; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">APPJCL</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Book Antiqua; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Book Antiqua; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">DC</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Book Antiqua; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">7:45 PM</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Book Antiqua; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right">5:45 AM</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Book Antiqua; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">x</TD><TD style="BORDER-RIGHT: #000000 1.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Book Antiqua; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">9:30</TD><TD style="BORDER-RIGHT: #ffffff 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Book Antiqua; BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center">9:30</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #808080; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Book Antiqua; BACKGROUND-COLOR: #808080; TEXT-ALIGN: center">Wednesday</FONT></TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>3</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Book Antiqua; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">APPJMO3</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Book Antiqua; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Book Antiqua; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">R</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Book Antiqua; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">7:45 PM</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Book Antiqua; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right">2:00 AM</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Book Antiqua; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center"> </TD><TD style="BORDER-RIGHT: #000000 1.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Book Antiqua; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">6:15</TD><TD style="BORDER-RIGHT: #ffffff 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Book Antiqua; BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center">9:30</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Book Antiqua; BACKGROUND-COLOR: #808080; TEXT-ALIGN: right"> </TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #808080 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-BOTTOM: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8" colSpan=10><TABLE width="100%" align=left VALIGN="TOP"><TBODY><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #808080 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; WIDTH: 120pt; BORDER-BOTTOM: #000000 0.5pt solid; BACKGROUND-COLOR: #ffffff" align=left><U>DailyInput</U></TD><TD> </TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
<FONT color=#339966 size=1>[HtmlMaker 2.42] </FONT><FONT color=#339966 size=1>To see the formula in the cells just click on the cells hyperlink or click the Name box</FONT>
<FONT color=red size=1>PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.</FONT></

What I am attempting to achieve is a doubleclick event in column E (Finish Time) and it places the current time in the cell (already able to do). But after that I would like for code to find the next available cell in column A, and place a new entry for that user. The new entry would be the logon from the last row and the start time would equal the finish time that was just entered .... Does this make sense, or I have managed once agin to confuse the !@#$ out of the board again ......

 

[Barrie Davidson]

Is this what you're trying to achieve?

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim NewRecord As Range

Set NewRecord = Range("A65536").End(xlUp).Offset(1)
NewRecord = Target.Offset(0, -4)
NewRecord.Offset(0, 3) = Target

End Sub

 

[autigers]

Very close .... the code does place a correct "logon" entry in column1
however, how can I taylor to begin filling from row 37 down to 150 ?
should the code be xldown versus up ?

 

[Barrie Davidson]

however, how can I taylor to begin filling from row 37 down to 150 ?
should the code be xldown versus up ?

I don't understand what you mean.

Are you saying it must start in a row no higher than 37 and no lower than 150? If that is the case, what action do you want if that range is full of data?

 

[autigers]

There will never be data lower than that. The only data that is above that are the initial entries that establishes a tracking record for that user.
Most nights or days average only 80 to 85 entries, but I built it with the future in mind with new functions being added .... This is simply a data entry sheet. VB takes all data dumps to access after every 12 hours ...


off the subj ... Wife and I just finished reading the 15pg thread on oompaas and rabbits ..... Hafta give me a few to recover from that !!!

 

[Barrie Davidson]

If I understand this correctly (still not sure I do!), would this work:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim NewRecord As Range

Set NewRecord = Range("A65536").End(xlUp).Offset(1)
If NewRecord.Row < 37 Then Set NewRecord = Range("A37")
NewRecord = Target.Offset(0, -4)
NewRecord.Offset(0, 3) = Target

End Sub

 

[Barrie Davidson]

off the subj ... Wife and I just finished reading the 15pg thread on oompaas and rabbits ..... Hafta give me a few to recover from that !!!

and did you add to it?

 

[autigers]

PERRRFECT !!!

Yes we added to it .... Jon left a good tie back ....so it was tough ...
Thank you for your help ...
I hope to be able to help others in here in a couple years ......