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