PDA

View Full Version : Solved: I Need to Restrict The number of worksheets in Excel



ads_3131
10-07-2011, 01:25 AM
Hello all

I have a button that when clicked copys a Template sheet and places it with a assigned name to the end.... I want to limit the number of worksheets to 100....

otherwise it keeps going untill excel grinds to a halt..

So basically im asking for help to limit the number of worksheets allowed to be created in a workbook.... / Not allow any more worksheets to be created past 100 from the click of the button....

Any help would be great!!! :D

regards

-------------------------------------------------------------------------
code i have is below
--------------------------------------------------------------------------
sub buttn_CreateSheet()

Dim Sh As Worksheet, TemplateSh As Worksheet
Dim ShNum As Integer, HighestNum As Integer
Dim SheetCoreName As String
Dim i As Long

' INDICATE THE CORE SHEET NAME
SheetCoreName = "TQ"

' INDICATE THE SOURCE SHEET
Set TemplateSh = Sheets("Template")

' DETERMINE NEXT NUMBER FOR SHEET
For Each Sh In Worksheets
If InStr(1, Sh.Name, SheetCoreName) = 1 Then
ShNum = Val(Right(Sh.Name, Len(Sh.Name) - Len(SheetCoreName)))
If ShNum > HighestNum Then HighestNum = ShNum
End If
Next Sh

' COPY TEMPLATE
TemplateSh.Copy after:=Sheets(Sheets.Count)

' MAKE VISIBLE
ActiveSheet.Visible = xlSheetVisible

' RENAME
ActiveSheet.Name = SheetCoreName & HighestNum + 1

End Sub

ads_3131
10-07-2011, 01:54 AM
Did it myself :D

Using if sheetcount = 100 then

*code*

end if