user5538704 user5538704 - 1 month ago 12
Vb.net Question

Protecting cells in excel using vb.net

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!!

Answer

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.