Excel Reiterating Code

D

diablo

Is there a way to keep Excel from reiterating your code over and over? I
know I can change it under options, but when I send the spreadsheet to
someone else they may not have the same options set as I do. Can this be
accomplished in a VB code?

Thanks,
Brian

--
 
C

Chip Pearson

Do you mean "Iteration" setting on the Calculation tab of the Options
dialog? If so, use

Application.Iteration = False


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)
 
D

diablo

Chip,

That is correct, I was referring to the calculation tab settings. How ever I
thought that this would fix my problem but it doesn't. The problem is when
clearing any cell value other than the A column with a back space or delete
key I get the "hour glass" showing until I hit the esc key.
Then I get the option to debug, end, continue or help. When I go to the
debug mode my code is cycling over and over. If I use the "clear contents"
selection when right clicking with the mouse it never happens.

Below is the code that I'm using.

-----------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo errHandler
If Target.Count = 1 Then
If Target.Column = 1 Then
Application.EnableEvents = False
If Target.Value <> "" Then
Target.Offset(0, 6).Value = Format(Date)
Target.Offset(0, 5).Value = Format(Time(), "hh:mm")
If Target.Offset(0, 5).Value >= TimeValue("07:00:00") _
And Target.Offset(0, 5).Value < TimeValue("15:00:00") _
Then Target.Offset(0, 7) = "1st Shift"
If Target.Offset(0, 5).Value >= TimeValue("15:00:00") _
And Target.Offset(0, 5).Value < TimeValue("23:00:00") _
Then Target.Offset(0, 7) = "2nd Shift"
If Target.Offset(0, 5).Value >= TimeValue("23:00:00") _
Or Target.Offset(0, 5).Value < TimeValue("07:00:00") _
Then Target.Offset(0, 7) = "3rd Shift"
End If
End If
End If
If Target.Value = "" Then
Target.Offset(0, 5).ClearContents
Target.Offset(0, 6).ClearContents
Target.Offset(0, 7).ClearContents
End If
errHandler:
Application.EnableEvents = True
End Sub
-----------------------------------------------------

Any help is greatly appreciated.

Regards,
Brian

--

Chip Pearson said:
Do you mean "Iteration" setting on the Calculation tab of the Options
dialog? If so, use

Application.Iteration = False


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)
Click to expand...
 
D

Dave Peterson

Maybe indenting your code differently would make the solution more evident:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo errHandler
If Target.Count = 1 Then
If Target.Column = 1 Then
Application.EnableEvents = False
If Target.Value <> "" Then
Target.Offset(0, 6).Value = Format(Date)
Target.Offset(0, 5).Value = Format(Time(), "hh:mm")

If Target.Offset(0, 5).Value >= TimeValue("07:00:00") _
And Target.Offset(0, 5).Value < TimeValue("15:00:00") _
Then Target.Offset(0, 7) = "1st Shift"

If Target.Offset(0, 5).Value >= TimeValue("15:00:00") _
And Target.Offset(0, 5).Value < TimeValue("23:00:00") _
Then Target.Offset(0, 7) = "2nd Shift"

If Target.Offset(0, 5).Value >= TimeValue("23:00:00") _
Or Target.Offset(0, 5).Value < TimeValue("07:00:00") _
Then Target.Offset(0, 7) = "3rd Shift"

End If
End If
End If
If Target.Value = "" Then
Target.Offset(0, 5).ClearContents
Target.Offset(0, 6).ClearContents
Target.Offset(0, 7).ClearContents
End If
errHandler:
Application.EnableEvents = True
End Sub


You're only disabling events when you change a single cell in column A.

I'm guessing that you want something like:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo errHandler
If Target.Count = 1 Then
If Target.Column = 1 Then
Application.EnableEvents = False
If Target.Value <> "" Then
Target.Offset(0, 6).Value = Format(Date)
Target.Offset(0, 5).Value = Format(Time(), "hh:mm")

If Target.Offset(0, 5).Value >= TimeValue("07:00:00") _
And Target.Offset(0, 5).Value < TimeValue("15:00:00") _
Then Target.Offset(0, 7) = "1st Shift"

If Target.Offset(0, 5).Value >= TimeValue("15:00:00") _
And Target.Offset(0, 5).Value < TimeValue("23:00:00") _
Then Target.Offset(0, 7) = "2nd Shift"

If Target.Offset(0, 5).Value >= TimeValue("23:00:00") _
Or Target.Offset(0, 5).Value < TimeValue("07:00:00") _
Then Target.Offset(0, 7) = "3rd Shift"

Else
Target.Offset(0, 5).ClearContents
Target.Offset(0, 6).ClearContents
Target.Offset(0, 7).ClearContents
End If
End If
End If
errHandler:
Application.EnableEvents = True
End Sub



Chip,

That is correct, I was referring to the calculation tab settings. How ever I
thought that this would fix my problem but it doesn't. The problem is when
clearing any cell value other than the A column with a back space or delete
key I get the "hour glass" showing until I hit the esc key.
Then I get the option to debug, end, continue or help. When I go to the
debug mode my code is cycling over and over. If I use the "clear contents"
selection when right clicking with the mouse it never happens.

Below is the code that I'm using.

-----------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo errHandler
If Target.Count = 1 Then
If Target.Column = 1 Then
Application.EnableEvents = False
If Target.Value <> "" Then
Target.Offset(0, 6).Value = Format(Date)
Target.Offset(0, 5).Value = Format(Time(), "hh:mm")
If Target.Offset(0, 5).Value >= TimeValue("07:00:00") _
And Target.Offset(0, 5).Value < TimeValue("15:00:00") _
Then Target.Offset(0, 7) = "1st Shift"
If Target.Offset(0, 5).Value >= TimeValue("15:00:00") _
And Target.Offset(0, 5).Value < TimeValue("23:00:00") _
Then Target.Offset(0, 7) = "2nd Shift"
If Target.Offset(0, 5).Value >= TimeValue("23:00:00") _
Or Target.Offset(0, 5).Value < TimeValue("07:00:00") _
Then Target.Offset(0, 7) = "3rd Shift"
End If
End If
End If
If Target.Value = "" Then
Target.Offset(0, 5).ClearContents
Target.Offset(0, 6).ClearContents
Target.Offset(0, 7).ClearContents
End If
errHandler:
Application.EnableEvents = True
End Sub
-----------------------------------------------------

Any help is greatly appreciated.

Regards,
Brian

--
Click to expand...
 
D

diablo

Dave,

That is what was wrong, apparently I was creating a never ending cycle due
to the way I was trying to "clear the contents" out side the nested IF / END
IF statements.

Thank you for looking at this and clearing it up for me.

Brian

--
 
Top