Hello
I have a file with 33 sheets and a VB code (below) in "This Workbook". The
problem is that I don't want the first and last sheets obey this code. How
can I fix this problem?
This is code:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Not Application.Intersect(Target, Range("A7:A68")) Is Nothing Then
If IsNumeric(Target.Value) Then
Application.EnableEvents = False
Target = Left(Format(Target.Value, "0000"), 2) & ":" & _
Right(Format(Target.Value, "0000"), 2)
Application.EnableEvents = True
End If
End If
If Not Application.Intersect(Target, Range("G7:G64")) Is Nothing Then
If IsNumeric(Target.Value) Then
Application.EnableEvents = False
Target = Left(Format(Target.Value, "0000"), 0) & "P." & _
Right(Format(Target.Value, "0000"), 2)
Application.EnableEvents = True
End If
End If
End Sub

Thanks in advance.

You could use the name of the sheets:

if lcase(sh.name) = lcase("firstsheet") _
or lcase(sh.name) = lcase("lastsheet") then
exit sub
end if

Or you could use the .index property

if sh.index = 1 _
or sh.index = sh.parent.sheets.count then
exit sub
end if

This adds some basic checks:

Option Explicit
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If Sh.Index = 1 _
Or Sh.Index = Sh.Parent.Sheets.Count Then
Exit Sub
End If

If Target.Cells.Count 1 Then
Exit Sub
End If

If Not Application.Intersect(Target, Sh.Range("A7:A68")) Is Nothing Then
If IsNumeric(Target.Value) Then
Application.EnableEvents = False
Target = Left(Format(Target.Value, "0000"), 2) & ":" & _
Right(Format(Target.Value, "0000"), 2)
Application.EnableEvents = True
End If
Else
If Not Application.Intersect(Target, Sh.Range("G7:G64")) Is Nothing Then
If IsNumeric(Target.Value) Then
Application.EnableEvents = False
Target = Left(Format(Target.Value, "0000"), 0) & "P." & _
Right(Format(Target.Value, "0000"), 2)
Application.EnableEvents = True
End If
End If
End If
End Sub


MAX wrote:

Hello
I have a file with 33 sheets and a VB code (below) in "This Workbook". The
problem is that I don't want the first and last sheets obey this code. How
can I fix this problem?
This is code:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Not Application.Intersect(Target, Range("A7:A68")) Is Nothing Then
If IsNumeric(Target.Value) Then
Application.EnableEvents = False
Target = Left(Format(Target.Value, "0000"), 2) & ":" & _
Right(Format(Target.Value, "0000"), 2)
Application.EnableEvents = True
End If
End If
If Not Application.Intersect(Target, Range("G7:G64")) Is Nothing Then
If IsNumeric(Target.Value) Then
Application.EnableEvents = False
Target = Left(Format(Target.Value, "0000"), 0) & "P." & _
Right(Format(Target.Value, "0000"), 2)
Application.EnableEvents = True
End If
End If
End Sub

Thanks in advance.

--

Dave Peterson