Force user to add copy of specific sheet instead of adding "New Sheet"

[NMeeker]

I currently have a database set up with one sheet named "Master" the Master sheet has all of my ranges, labels, headers, and format set how I would like all sheets within the workbook to be set.

I would like to make this workbook "foolproof" seeing as how some of the people who may be using it in the future might not have a clue how to work a computer well... let alone excel.

I have a user form set up with some easy settings to allow someone to input data onto worksheets, and now I would like to "Lock" the worksheets so the information on them may be edited, but not the settings, row headers and ranges I have set up currently.

If a blank worksheet is added to the workbook without MY settings, it will cause my form to not work correctly in many places. Currently in order to add a new sheet to the workbook, it MUST be a copy of the "Master" Sheet.
Does anyone have any ideas how to force all new sheets to be a copy of the "Master" sheet?

 

[Yard]

Maybe something along these lines in the ThisWorkbook module?

Private Sub Workbook_NewSheet(ByVal Sh As Object)

Sheets("Master").Copy After:=Sheets(Me.Sheets.Count)
Sheets(Me.Sheets.Count).Name = "NewSheet" & Format(Now(), "hhmmss")

Application.DisplayAlerts = False
Sh.Delete
Application.DisplayAlerts = True

End Sub

 

[Von Pookie]

If you protect the *workbook* you can stop new sheets from being added. Maybe add a button to run a macro to add a new sheet based off of your Master?

 

[NMeeker]

HAHAHA in a pinch that worked perfect Yard! FANTASTIC Thank you both very much. I thought about protecting it, but it didn't have the affect i was looking for exactly. Thank you both so much.

 

[NMeeker]

Maybe something along these lines in the ThisWorkbook module?

Private Sub Workbook_NewSheet(ByVal Sh As Object)

Sheets("Master").Copy After:=Sheets(Me.Sheets.Count)
Sheets(Me.Sheets.Count).Name = "NewSheet" & Format(Now(), "hhmmss")

Application.DisplayAlerts = False
Sh.Delete
Application.DisplayAlerts = True

End Sub

Acutally Yard, that worked great, but is there any way to do it if the "Master" sheet was hiden? So that the new sheet would not be hidden?

 

[NMeeker]

Never mind I got it. Thanks a lot though... I hid the "Master" sheet, then I used:

Private Sub Workbook_NewSheet(ByVal Sh As Object)


Sheets("Master").Copy After:=Sheets(Me.Sheets.Count)

Sheets(Me.Sheets.Count).Visible = True

Sheets(Me.Sheets.Count).Name = "New Rope" & Format(Now(), "hhmmss")
Application.DisplayAlerts = False
Sh.Delete
Application.DisplayAlerts = True

End Sub

In the ThisWorkbook Module...

Thanks again

 

[Yard]

Good work.

BTW, the Format(Now(), "hhmmss") line was just to provide a neater & (probably) unique new sheet name. You could always add some InputBox code to get the user to add the sheet's name etc...