Excel row locking from .Net

Question

I am generating an Excel spreadsheet from code. I am trying to customize the cells using .net code, and I would like to lock the header row so that it won't allow text to be entered into those cells.

I tries following 2 ways with no success

worksheet.Range("A1", "A1").EntireRow.Locked = True
worksheet.Unprotect()
worksheet.Range("A1","A100").Locked = True
worksheet.Protect()

Is that anything I am missing here?

---CODE---

Public Sub CreatNewExcelWithAppliedRules(ByVal noOfWSheets As List(Of String))

    Dim misValue As Object = System.Reflection.Missing.Value
    Dim App As New Application
    Dim workbook As Workbook = App.Workbooks.Add()
    Dim worksheet As Worksheet = workbook.Worksheets(1)
    Dim sFile As String = "sample-excel"
    
 
    ---READ HEADERS FROM XML
    Dim xmlFile = "E:\ExcelPOC\ExcelValidation\App_Data\Headers.xml"
    Dim fsReadXml As New System.IO.FileStream(xmlFile, System.IO.FileMode.Open)
    dsHeaders.ReadXml(fsReadXml)
    dtHeader = dsHeaders.Tables("Column")

    ---ADD HEADERS LIST TO EXCEL
    FillColumnHeader(worksheet, dtHeader, culture)

    worksheet.Unprotect()
    ---ADD VALIDATION RULES
    For Each worksheet In workbook.Worksheets
        ListValidExcelRule(worksheet)
        DateValidExcelRule(worksheet)
        TextLengthValidExcelRule(worksheet)
        DecimalValidExcelRule(worksheet)
    Next
    worksheet.Range("1:1").Locked = True
    worksheet.Protect()

    ---SAVE THE EXCEL
     sFile = App.GetSaveAsFilename(InitialFilename:=sFile, FileFilter:="xls Files  (*.xls), *.xls")

    If sFile <> "False" Then
        workbook.SaveAs(Filename:=sFile, _
                              FileFormat:=XlFileFormat.xlWorkbookNormal, _
                              Password:="", _
                              WriteResPassword:="", _
                              ReadOnlyRecommended:=False, _
                              CreateBackup:=False)
    Else
        App.DisplayAlerts = False
    End If

    workbook.Close(True, misValue, misValue)
    App.Quit()
    releaseObject(worksheet)
    releaseObject(workbook)
    releaseObject(App)

End Sub

Did you receive any errors, or did it simply not prevent edits to the row? — Patrick
– Patrick, Commented Aug 18, 2011 at 5:38
I didnot receive any errors. it is not applying the locking and allowing to enter text — ramesh
– ramesh, Commented Aug 18, 2011 at 5:56

Hand-E-Food · Accepted Answer · 2011-08-18 07:11:25Z

2

A1:A100 is the left-hand column. A1:IV1 is the top row. Both of your attempts are close.

Try:

worksheet.Cells.Locked = False
worksheet.Range("1:1").Locked = True
worksheet.Protect

In a new Excel worksheet, all cells are locked by default. Unless you're unlocking the entire worksheet first, you're better off selecting the cells to unlock rather than the cells to lock.

edited Aug 18, 2011 at 7:11

answered Aug 18, 2011 at 5:44

Hand-E-Food

12.9k11 gold badges48 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

ramesh Over a year ago

I tried the above , but still it is allowing to enter the text in the header

Patrick Over a year ago

This works for me when I replace worksheet with worksheets(1). You may need to change the index depending on how you generate your Excel file.

Chris McGrath Over a year ago

whorksheets are generally stored as a collection of worksheets and you need to tell it which work sheet you wish to lock

ramesh Over a year ago

The above code is locking the "Sheet3" that too complete sheet. I want to lock only top row header

Hand-E-Food Over a year ago

I think the problem is with the loop For Each worksheet In workbook.Worksheets. The loop changes the value of worksheet. The .Locked = True line is outside this loop when worksheet has been changed by the loop. Move the locking code inside the loop, or write worksheet = workbook.Worksheets(1) again.

|

Collectives™ on Stack Overflow

Excel row locking from .Net

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related