If the workbook is protected (tools|Protection|protect workbook with the
Structure checked), then the user can't move, copy, insert, delete, rename
worksheets. (See note.)

And if your workbook always has worksheets named 1, 2, 3, ... and nothing more,
you can do this:

Option Explicit
Sub testme()
Dim myPWD As String
myPWD = "hi"
With ThisWorkbook
.Unprotect Password:=myPWD
.Worksheets.Add.Name = .Worksheets.Count + 1
.Protect Password:=myPWD
End With
End Sub

Note: Workbook protection is easily broken. This ain't fullproof.

Second, you'll want to protect the code, too--so that your password is hidden.

Inside the VBE:
Tools|VBAProject Properties|Protection tab.

This password is easily bypassed, too.


Sinner wrote:

I want to have a button on an excel sheet. This button can be on the
sheet or could be on a floating bar, most preferably a floating bar to
reduce workbook size. Name of first worksheet is let's say '1'. On
press it should generate another sheet with name '2' having same
button on sheet '2' as well.

The workbook should also be able to logically detect the last or
greatest sheet number value in the workbook so that the name of the
added sheet is n+1. If sheets are 62,63,64,65,66,67,68 & the last
sheet is 68, then the next sheet should be 69 even if I press the
button on let's say sheet 65.

This workbook may also have other sheets with alphanumeric names like
List8, Bookings etc.
The process should not alter with these sheets.

Finally, to have a sequential sheet names, protection to edit sheet
name is also required form any accidental change of sheet name, like
rename sheet to abc1, abc22, etc.

--

Dave Peterson