CODEWITHSUNDEEP
vbaexcel
Gajju
Gajju

Reputation: 443

Protect excel sheet with some exceptions

I have a excel sheet in which I am using macros, dropdowns and buttons. I have also got a lot of formulas in different cells like multiplication, vlookup etc. The buttons work in such a way that when I press it adds a empty row and adds some data to it. There are few cells in which the user is supposed to enter the data

Buttons, formulae and dropdowns change and add new data to different cells

Now I want to protect the sheet so that the user can't change the content by mistake or intentionally.

But when I protect the sheet its shows error on using the sheet because buttons, multiplication and vlookup tend to change the data in different cells all the time.

Is it possible to make the sheet read only for the user (except for some cells where is supposed to enter the data) and allow macros, formulae and dropdowns to change the cell content

Upvotes: 0

Views: 1922

Answers (1)

Anton
Anton

Reputation: 79

place a macro in the workbook_open() to unlock your sheet first then at the end of your other macros place the lock macro ex:

Private Sub Workbook_Open()
  On Error Resume Next
       Sheets("Sheet1").Unprotect Password:=""

  *****your codes here

       Sheets("Sheet1").Protect Password:=""
end sub

this will make sure your sheet is not locked when your codes run but will lock once your codes are done.

Upvotes: 1

Related Questions