Protecting cells in excel using vb.net

Question

I have an excel sheet and a windows form in visual studio 2013. The form allows users to enter data in excel sheet. It will be shared by multiple users. What I want to do is that certain cells in excel (eg: column B to G) should be locked but data entry in these cells should be allowed through the form. Once a user opens excel, they can edit other columns but not these columns. I locked the cells and protected the worksheet in excel. Then, in my vb code I added these:

 Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
              xlWorkBook =   xlApp.Workbooks.Open("C:\Users\test.xlsx")
          'xlApp.Visible = True
           xlWorkSheet = xlWorkBook.Sheets("Sheet1")
    xlWorkBook.Unprotect()

   'entering data in excel

    xlWorkBook.Save()
    xlWorkBook.Protect()
End Sub

I get the error saying that 'the cells you are trying to enter data is protected.' something like this. What should I do? Please help!!

Let's continue here. No, I mean, if there is no password to "protect" the cells, then any user can still unprotect it, right? And lastly, the workbook is really the one protected? or the worksheet? — Aethan
ya @CrushSundae but they'll have to open excel, click on review tab and then unprotect it. Anyway, I'll add password afterwards. My main concern right now is how to allow data entry in the locked cells through the form. I protected only sheet1 — user5538704
No, you're on the right direction. You cannot enter records to the cell because it is protected. So check whether the protection is on the Workbook or Worksheet level. — Aethan
In my testing (not thru code), I tried protecting Excel by worksheet and by workbook. Removed the protection by workbook and still cannot edit unless I also remove the protection from worksheet. — Aethan
It is on worksheet level I think. Because only Sheet1 is protected in excel — user5538704

Aethan Aethan · Accepted Answer · 2016-10-13T06:49:07

So as per our discussion:

The problem with your code is you are unprotecting the Workbook but the ones that are really protected is your Worksheets.

Also, in addition to disable the confirmation alerts you need to use:

xlApp.DisplayAlerts = False

You also need to set the protection first before saving.

PS. I summarized our discussion as an answer because it solved the question and can be helpful to future users.

Protecting cells in excel using vb.net

1 Answers