Forum Discussion
Locking Tabs on a shared spreadsheet
Hi, can someone help, I share sheets with around 200 people to add their comments on, the most frustrating thing is when people think they are going to their tab and keep adding blank sheets - is there a way of stopping people from adding sheets but still being able to edit the tabs. Any help would be great.
To protect workbook structure, you can try to enable protect structure:
- Open your Excel file.
- Go to the Review tab.
- Click Protect Workbook > Protect Structure.
- Set a password (optional but recommended).
This will prevent users from adding, deleting, renaming, hiding, or moving sheets. Still allow them to edit the content of existing sheets.
-----------------------------------------------------------
If this helped, please mark it as the answer!
6 Replies
- OlufemiOBrass Contributor
Protect the Workbook Structure
This is the key feature that stops users from adding, deleting, or renaming sheets.
How to enable it:
- Go to the Review tab
- Click Protect Workbook
- Check Structure
- (Optional) Add a password
- Click OK
✅ This locks the workbook layout while still allowing users to edit cells in existing sheets.
Create a “Start Here” Navigation Sheet
To reduce confusion and guide users to their assigned tabs, I created a front-facing sheet with clear instructions and clickable links.
Example layout:
excel
🟢 Welcome to the Collaboration Sheet Please do not add new sheets.
Your tab is already set up.
Click your name below to jump to your sheet:
=HYPERLINK("#'Amos'!A1", "Amos")
=HYPERLINK("#'Wale'!A1", "Wale")
=HYPERLINK("#'Henry'!A1", "Henry")
=HYPERLINK("#'Jack'!A1", "Jack")
Need help?
Contact the sheet owner.Create Individual Sheets for Each User
- Each sheet is named after the user and includes a simple layout for comments or input. For example:
- Header: Comments for [Name]
- Table or section for input
- Optional formatting or instructions
Why This Works
- No VBA or macros required
- Fully functional in Excel 365 Desktop
- Prevents sheet creation while allowing collaboration
- Easy to maintain and scale for large teams
- KC14Copper Contributor
works a treat thank you.
- OlufemiOBrass Contributor
Preventing Users from Adding Sheets in a Shared Excel Workbook (No VBA Required)
Hi [Name], I’ve faced this exact challenge—sharing a workbook with hundreds of users and needing to prevent them from adding new sheets while still allowing them to edit their assigned tabs.
Here’s a practical solution I implemented using Excel 365 Desktop only, with no VBA, macros, or online tools:
Step 1: Protect the Workbook StructureThis is the key feature that stops users from adding, deleting, or renaming sheets.
How to enable it:
- Go to the Review tab
- Click Protect Workbook
- Check Structure
- (Optional) Add a password
- Click OK
✅ This locks the workbook layout while still allowing users to edit cells in existing sheets.
Step 2: Create a “Start Here” Navigation Sheet
To reduce confusion and guide users to their assigned tabs, I created a front-facing sheet with clear instructions and clickable links.
🟢 Welcome to the Collaboration Sheet
Please do not add new sheets. Your tab is already set up.
Click your name below to jump to your sheet:
- =HYPERLINK("#'Amos'!A1", "Amos")
- =HYPERLINK("#'Wale'!A1", "Wale")
- =HYPERLINK("#'Henry'!A1", "Henry")
- =HYPERLINK("#'Jack'!A1", "Jack")Need help? Contact the sheet owner.
Step 3: Create Individual Sheets for Each User
Each sheet is named after the user and includes a simple layout for comments or input. For example:
- Header: Comments for [Name]
- Table or section for input
- Optional formatting or instructions
Why This Works
- No VBA or macros required
- Fully functional in Excel 365 Desktop
- Prevents sheet creation while allowing collaboration
- Easy to maintain and scale for large teams
- JKPieterseSilver Contributor
Sounds like this is a navigation issue. Add a Table Of Contents worksheet (make it the first one in the workbook). Then at the top of every sheet add a link back to that ToC worksheet.
To prevent adding worksheets, you can consider protecting the workbook (Review tab).
- PankajBadoniIron Contributor
To protect workbook structure, you can try to enable protect structure:
- Open your Excel file.
- Go to the Review tab.
- Click Protect Workbook > Protect Structure.
- Set a password (optional but recommended).
This will prevent users from adding, deleting, renaming, hiding, or moving sheets. Still allow them to edit the content of existing sheets.
-----------------------------------------------------------
If this helped, please mark it as the answer!
- KC14Copper Contributor
works a treat thank you.