I have been scratching around in vain to write a code that will limit
the number of Worksheets in a Workbook to fall within a certain range.
For example, prevent the user from inserting new sheets if a maximum of
10 sheets is reached, and contrariwise, to prevent deletions if number
of sheets is 3.

I have a sneaking feeling that this task will need a Workbook event but
the following code failed me. In spite of the code, insertions/deletions
are carried out after the Msgbox displays. TIA

Private Sub Workbook_NewSheet(ByVal Sh As Object)
If Sheets.Count 5 Then
MsgBox "You cannot have MORE THAN 5 sheets"
Exit Sub
End If
If Sheets.Count < 3 Then
MsgBox "You cannot have LESS THAN 3 sheets"
Exit Sub
End If
End Sub


--
davidm
------------------------------------------------------------------------
davidm's Profile: http://www.excelforum.com/member.php...o&userid=20645
View this thread: http://www.excelforum.com/showthread...hreadid=473686