User Access in excel sheets with macro's

[saravanan24]

Hi,

I am seeking a way to prevent writing on a sheet at the user level without using a protect password method, since we use macro's to transfer data from one sheet to another even its protected.

When attempting to transfer data from one sheet to another after protecting the sheet, an error 400 is generated.

Could you please provide us the way to solve this.

Thanks

[#2]

@saravanan24 

Hello! You've posted your question in the Tech Community Discussion space, which is intended for discussion around the Tech Community website itself, not product questions. I'm moving your question to the Excel space - please post Excel questions here in the future. 

[#3]

@saravanan24 

One option is to protect the sheet, and then unprotect it at the beginning of a macro, and reprotect it at the end.

Another option is to protect the sheet in the Workbook_Open event procedure in the ThisWorkbook module with the UserInterfaceOnly argument set to True:

Private Sub Workbook_Open()
    Worksheets("Sheet1").Protect Password:="Secret", UserInterfaceOnly:=True
End Sub

VBA code should then be able to manipulate the protected sheet without causing an error.