CODEWITHSUNDEEP
vbaexcel
thebiglebowski11
thebiglebowski11

Reputation: 1461

excel vba - only enable cell edits through code

I have an excel tool that gathers information from a user based on their login information. The information is stored in cells on one of the worksheets. If I wanted to lock these cells so they can't be updated manually, how can I go about doing that? If someone else logs in, obviously these cells would change. I hope to do it through VBA.

I only want to lock 6 cells... everything else should be editable...

Thanks

Upvotes: 0

Views: 3495

Answers (2)

Mr. Mascaro
Mr. Mascaro

Reputation: 2733

If these cells are the only ones on the sheet you want to protect, then just change the cell properties of the remaining cells by changing Locked property to false and leave the cells in question as Locked then protect the sheet using UserInterfaceOnly set to true (but realize that this doesn't work for all possible macro changes, so I usually avoid it.)

There are other methods that could work, but I think this is the best solution for you. If not, please add a comment to let me know.

Upvotes: 0

Drew Gaynor
Drew Gaynor

Reputation: 8472

You can protect the worksheet with a password like this:

Private Sub Workbook_Open()
    Sheets("sheetName").Protect Password:="YourPassword", UserInterfaceOnly:=True
End Sub

That will prevent users from manually making changes to the worksheet without entering the password. Your VBA code will still be able to make changes because you've set UserInterfaceOnly to True.

Note that users could easily view this password by navigating to the code through the Visual Basic editor. You can password protect the code as well, though: just right-click on the module, click on VBAProject Properties and go to the Protection tab.

See this page for more information: Excel VBA: Macro Code To Run Macros On Protected Worksheets & Sheets.

Upvotes: 4

Related Questions