This may not be as efficient and as elegant as possible, but it works.
*~*~*~*~*~*~*~*~*~
Global nSheets As Integer
'Run one time to initialize the original count of sheets.
Sub InitializeCount()
Dim sh As Object, shCount As Integer
shCount = 0
For Each sh In ActiveWorkbook.Sheets
shCount = shCount + 1
Next sh
ActiveWorkbook.Names.Add Name:="myCount", RefersToR1C1:=shCount
End Sub
Sub Auto_Open()
Dim myNum, myValue As Integer
myNum = ActiveWorkbook.Names("myCount").Value
myValue = Right(myNum, 1)
CountSheets
If nSheets > myValue Then
newSheetMessage
ActiveWorkbook.Names.Add Name:="myCount",
RefersToR1C1:=nSheets
End If
End Sub
Sub CountSheets()
Dim sh As Object
nSheets = 0
For Each sh In ActiveWorkbook.Sheets
nSheets = nSheets + 1
Next sh
End Sub
Sub newSheetMessage()
Msg = "A new sheet has been added."
Style = vbOKOnly + vbExclamation
Title = "Important Information"
Response = MsgBox(Msg, Style, Title)
End Sub