This event code is giving me fits.

If I change B5 it does its work just fine.
Then if I change J5 it does its work and sends B5's called code into a continual loop for about 20 reps then errors out and the workbook freezes up.

Close, reopen repeat. Freeze.

I have used a few other exclusion lines from a googled site to be sure I had all the correct syntax but this is not working out.

I want make an entry in either B5 or J5 and run two different codes.

If I put each code on a separate button, both work just fine.

thanks,
Howard

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("B5,J5")) Is Nothing Then

If Target.Count 1 Then Exit Sub

If Target = Range("B5") Then
Scan_In_Check
End If

If Target = Range("J5") Then
Scan_Out_Check
End If

End If
End Sub

Hi Howard,

Am Thu, 29 May 2014 23:15:26 -0700 (PDT) schrieb L. Howard:

Then if I change J5 it does its work and sends B5's called code into a continual loop for about 20 reps then errors out and the workbook freezes up.

try:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("B5,J5")) Is Nothing Then

If Target.Count 1 Then Exit Sub

Application.EnableEvents = False
On Error GoTo EventsOn

If Target = Range("B5") Then
Scan_In_Check
End If

If Target = Range("J5") Then
Scan_Out_Check
End If

End If

EventsOn:
Application.EnableEvents = True
End Sub

If this does not solve the problem please post "Scan_In_Check" and
"Scan_Out_Check"


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

If this does not solve the problem please post "Scan_In_Check" and

"Scan_Out_Check"


Regards

Claus B.

--

Hi Claus,

That did solve the problem. I did try an error capture routine from that same site I mentioned, but must have misapplied it.
This works just fine and I thank you.

Howard

Another way...

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B5,J5")) Is Nothing Then Exit Sub
If Target.Count = 1 Then
Application.EnableEvents = False
On Error Resume Next
Select Case Target.Address
Case "$B$5": Call Scan_In_Check
Case "$J$5": Call Scan_Out_Check
End Select
Application.EnableEvents = True
End If
End Sub

Sub Scan_In_Check()
MsgBox "B5 changed!"
End Sub

Sub Scan_Out_Check()
MsgBox "J5 changed!"
End Sub

...depending, of course, on what those called procedures do.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Note that the "If Target.Count..." line really isn't needed since the
select case criteria are single cells...

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B5,J5")) Is Nothing Then Exit Sub
Application.EnableEvents = False
On Error Resume Next
Select Case Target.Address
Case "$B$5": Scan_In_Check
Case "$J$5": Scan_Out_Check
End Select
Application.EnableEvents = True
End Sub

...and so will not match if a range of cells are changed.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

On Friday, May 30, 2014 9:21:04 AM UTC-7, GS wrote:
Another way...



Option Explicit



Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("B5,J5")) Is Nothing Then Exit Sub

If Target.Count = 1 Then

Application.EnableEvents = False

On Error Resume Next

Select Case Target.Address

Case "$B$5": Call Scan_In_Check

Case "$J$5": Call Scan_Out_Check

End Select

Application.EnableEvents = True

End If

End Sub



Sub Scan_In_Check()

MsgBox "B5 changed!"

End Sub



Sub Scan_Out_Check()

MsgBox "J5 changed!"

End Sub



..depending, of course, on what those called procedures do.



--

Garry


Hi Garry,

That works well and also did away with a pesky little item that lingered with Claus' solution.

The Scan In macro takes the input from B5 and searches a list on another sheet and returns data to an "In" list.

The Scan Out takes the input from J5 and looks at the "In" list and removes elements from it to an "Out" list.

The pesky problem was if the search string was the same in B5 & J5, then when either B5 or J5 produced the change_event both called macros would run.

I cannot reproduce that scenario with your solution in the many test I given it.

So, my next step is to time stamp the identical info returned to the "In" & "Out" lists but do it on sheets named by Month of the year. So sheet named May (for the next day or so) then to sheet June after that.

I'm sensing some difficulties here as I seldom work with dates other than a time stamp. I believe there are some 'Month' function/s that I will need to look into to determine what sheet to go to.

I suppose to test for it going to July or August is to use Now() + 30 & 60 in some capacity. Need to jump in and see whats what.

Thanks for the code.

Howard

So, my next step is to time stamp the identical info returned to the
"In" & "Out" lists but do it on sheets named by Month of the year.
So sheet named May (for the next day or so) then to sheet June after
that.

I'm sensing some difficulties here as I seldom work with dates other
than a time stamp. I believe there are some 'Month' function/s that
I will need to look into to determine what sheet to go to.

I suppose to test for it going to July or August is to use Now() + 30
& 60 in some capacity. Need to jump in and see whats what.

I'm up for the easiest solution here...

Set wksTarget = Sheets(Format(Date(),"mmm")) 'Jan,Feb...
OR
Set wksTarget = Sheets(Format(Date(),"mmmm")) 'full name

...depending on what you prefer.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

I'm up for the easiest solution here...



Set wksTarget = Sheets(Format(Date(),"mmm")) 'Jan,Feb...

OR

Set wksTarget = Sheets(Format(Date(),"mmmm")) 'full name



..depending on what you prefer.



So if the sheet name is Feb 14 this would work with the space after "mmm "?

Set wksTarget = Sheets(Format(Date, "mmm " & 14)) 'Feb 14

or

Set wksTarget = Sheets(Format(Date, "mmm" & " " & 14)) 'Feb 14

Howard

No! Do it this way...

Set wksTarget = Sheets(Format(Date, "mmm yy")) 'Jan,Feb...

OR

Set wksTarget = Sheets(Format(Date, "mmmm yy")) 'full name

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Just to add, all the sheets are named like Feb 14, where before the 14th of Feb the data would go on sheet Jan 14, the 14th and after on sheet Feb 14.

Bit strange to me but...

I may be able to change the sheet names to something similar but not a real date.

Howard

Almost there but now I can't figure out the last pesky problem/s.

https://www.dropbox.com/s/knm4fawt21...p%20Box.xl sm

I include a link as I doubt if I can fully explain the problem without it.

With the workbook as sent, on sheet "Jun 14" is an example of what is happening well and what is not happening well.

There are two rows that have correctly posted data with the date and time for scan in and for scan out. The next two where only are Z23 and Z24 posted, and that is where the problem in the code is.

If working correctly, on sheet UI scan in Z23 and then Z24. Columns B, C & D will display the data for Z23 and Z24. And the data will also be copied to sheet Jun 14 along with a date and time scanned in. This works.

Now scan Z23 and Z24 out in J5, Z23 and Z24 and the data on sheet UI is moved to columns I, J & K. And on sheet Jun 14 the date and time of the scan out is displayed. This works also.

Now with B5 if you scan Z23 and Z24 in again the data from I, J and K should go back to B, C and D. And the data should also display below the existing data on sheet Jun 14 along with the date and time Z23 and Z24 were re scanned in. Now it only brings the Z23 and Z24 with no data or scan in time stamp. Can't understand why this is not repeatable if it can do it one correctly but only once.

I think I also have the wrong code to place the time stamps. The time stamps need come from code that offset from the Z number and not from an ".xl(up)(2)" because there will certainly be fully occupied rows below an entry that needs a scan out date and time.

Another problem looming is if an employee scans in on one month and then scans out the next month... I have no clue how to get the scan out time stamp back to the previous month. That seems formidable to me.

Thanks for any advice or help you can offer.

Howard

It's almost as if you need a 'ScanIn2' and 'ScanOut2' to handle
rescans!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

On Sunday, June 1, 2014 10:53:13 PM UTC-7, GS wrote:
It's almost as if you need a 'ScanIn2' and 'ScanOut2' to handle

rescans!



--

Garry


Okay, that never occurred to me, I'll give it some thought. Something to take some of the load off the existing code.

Thanks, I will ponder that.

Howard