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

[sootsie74]

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?

[#2]

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?

---

[#3]

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.

[#4]

@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

[#5]

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 

[#6]

@sootsie74 I had the same issue, but on Manage protection>Options, if you allow to "Insert Row" responses are appearing correctly. Is the only way i have found to work this around while protecting the rest of the sheet from other users not editing specific cells. I hope this works for you.

Best