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

NMeeker

New Member
Joined
Feb 10, 2009
Messages
31
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?
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Maybe something along these lines in the ThisWorkbook module?


Code:
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
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
Maybe something along these lines in the ThisWorkbook module?


Code:
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?
 
Upvote 0
Never mind I got it. Thanks a lot though... I hid the "Master" sheet, then I used:


Code:
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
 
Upvote 0
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...
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,044
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top