Code to Protect Sheet Allowing Format Rows & Edit Objects

When my form protects my spreadsheet after hitting the command button, it doesn't keep my protection settings. I need to allow users to format rows and edit objects. I searched online came up with adding to the last line below, but it isn't working. Please help!



Private Sub UpdateRatesCommandButton_Click()
Sheets("TA Form").Unprotect Password:="XXXX"
Worksheets("TA Form").Range("AV267").Value = AutomobileTextBox.Text
Worksheets("TA Form").Range("AV268").Value = AircraftTextBox.Text
Worksheets("TA Form").Range("AV269").Value = MovesTextBox.Text
Worksheets("TA Form").Range("AV270").Value = AllOthersTextBox.Text
Unload VehicleRatesUserForm
Sheets("TA Form").Protect Password:="XXXX", AllowEditObjects:=True, FormattingRows:=True
End Sub

^^ Hi, unfortunately I haven't got an answer for you, I have a similar problem!

I have a sheet that I am trying to protect parts of but allow a completely user editable range. When it runs, however, it locks the whole sheet.
I have seen people using ".Protect AllowEditObjects" but for some reason my Excel doesn't recognise this as a valid command - do I not have a library turned on somewhere?

Any / All help appreciated.
Writing in Excel 2007 but have to save in compatibility format for my users.

Please Login or Register  to view this content.

usually you cannot format cells in a protect sheet

I answered my own question here. My code wasn't allowing for the selection of unlocked cells, so even though they were unlocked Excel didn't know it.

I inserted an extra line (below) and it seems to have helped.

Please Login or Register  to view this content.

hopefully this may be of use to someone else?