Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

Microsoft forms won't submit to a protected sheet in Excel with insert permissions

Copper Contributor

I have a form connected to an Excel file in my OneDrive. I locked the sheet to allow for inserts but not deletes. This resulted in no responses being written to my Excel file until I removed protection from the sheet. Any thoughts?

4 Replies

hey @sootsie74 

did you get a solution to this?

 

i have the same issue.


@sootsie74 wrote:

I have a form connected to an Excel file in my OneDrive. I locked the sheet to allow for inserts but not deletes. This resulted in no responses being written to my Excel file until I removed protection from the sheet. Any thoughts?


 

Does anyone have a solution for this? I would like to be able to have responses submitted with the form but make the excel sheet uneditable.

@Todd_Edwards if you just use the spreadsheet connected to the form then only those who are owners of the form can see & edit it. That seems the most sensible solution. Otherwise you can save the form responses via a flow in Power Automate to a different spreadsheet that only you can access.

 

Rob
Los Gallardos
Microsoft Power Automate Community Super User

Solution 1:   

Before you protect the sheet, right click on the cell you wish to be able to have someone submit a response in.  Select "Format Cells..." on the "Protection Tab" uncheck the "Locked" option.  do this again for any additional cells you wish to be editable.  Then add your protection back to the sheet and see if it works.  If not, try solution 2.

 

Solution 2:

Before you protect the sheet, go to the "Review" tab and select "Allow Users to Edit Ranges" under the "Changes" Section.  Select "New".  give the New range a Title.  and then in the "Refers to cells:" box, Hold Ctrl and click all cells you want to be editable before releasing Ctrl.  Leave the "Range Password:" box empty and select the "Permissions" box at the bottom of the window. Select "Add" and type "User" in the object names box. then again select "Add" and type "Administrator" in the object names box.  After all "Groups" or "User's" have been added to this list make sure you check mark the box below that says "Allow" next to "Edit range without a password"

***NOTE***: if you are part of a company with a company login you may be given several options to select after typing in "User" or "Administrator", you will have to "Add" them all to this list if you don't want to have to do this again in the future if someone finds they do not have access later on.

 

Hopes this helps!

@sootsie74