Nyx Assasin Nyx Assasin - 1 year ago 184
Vb.net Question

How to unlock columns in Excel using VB.Net?

Good Morning

I have a program in VB.Net that exports a file from Datagridview into Excel file
and it looks like this.

enter image description here

My goal here is how can I lock some columns? based on the Image above? Lock all columns except the column that has a color yellow? I mean all the columns except the yellow are uneditable.

Here is my code in exporting excel

If DataGridView1.Rows.Count = 0 Then
MsgBox("Nothing to Export")
Dim ExcelApp As Object, ExcelBook As Object
Dim ExcelSheet As Object
Dim i As Integer
Dim J As Integer
Dim rowIndex As Integer = 1
Dim total As Double = 0
Dim indexTotal As Integer

ExcelApp = CreateObject("Excel.Application")
ExcelBook = ExcelApp.WorkBooks.Add
ExcelSheet = ExcelBook.WorkSheets(1)
With ExcelSheet
rowIndex += 2
For Each column As DataGridViewColumn In DataGridView1.Columns
.cells(rowIndex, column.Index + 1) = column.HeaderText
.Range(.Cells(rowIndex, 1), .Cells(rowIndex, DataGridView1.Columns.Count)).Font.Bold = True
rowIndex += 1
For i = 0 To Me.DataGridView1.RowCount - 1
.cells(rowIndex, 1) = Me.DataGridView1.Rows(i).Cells("ItemCode").Value
For J = 1 To DataGridView1.Columns.Count - 1
If IsNumeric(DataGridView1.Rows(i).Cells(J).Value) Then
.cells(rowIndex, J + 1).NumberFormat = "#,##0.00"
.cells(rowIndex, J + 1) = DataGridView1.Rows(i).Cells(J).Value
.cells(rowIndex, J + 1) = DataGridView1.Rows(i).Cells(J).Value
End If
'You can test also by index for example : if J = indexofTotalColumn then
If DataGridView1.Columns(J).Name = "Total" Then
total += DataGridView1.Rows(i).Cells(J).Value
indexTotal = J
End If
rowIndex += 1
.Columns("L").ColumnWidth = 0
.cells(5).Locked = False

End With

ExcelApp.Visible = True
ExcelSheet = Nothing
ExcelBook = Nothing
ExcelApp = Nothing
End If
End Try

TYSM for help

Answer Source

Set the Locked property of the cell to false, where J+1 is the desired column number.

For example to unlock column 5 :

For J = 1 To DataGridView1.Columns.Count - 1
If J=5 then
 .cells(rowIndex, J + 1).Locked=False
End if
 If IsNumeric(DataGridView1.Rows(i).Cells(J).Value) Then

In the code, once you are done populating data in sheet, protect the sheet

.Protect ("fakepwd")
End With
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download