How to make changes in Excel sheets only with Visual Basic .NET (Only Programing)

Question

hi I want to have all kinds of changes in the sheets by programming. But the user cannot edit the cells in Excel itself. All edits should be programming only.

Answer 1

Hi,@Mansour_Dalir. Welcome to Microsoft Q&A.

To make changes to Excel sheets programmatically using Visual Basic .NET and prevent users from editing cells directly in Excel, you can use the Microsoft Excel Interop library.

First, make sure you have added a reference to the Microsoft.Office.Interop.Excel assembly. You can do this by right-clicking on your project, selecting "Add" > "Manage NuGet Packages...", and then searching for and adding the Microsoft.Office.Interop.Excel assembly.

Imports Microsoft.Office.Interop.Excel
Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1
    Dim xlApp As Excel.Application
    Dim xlWorkBook As Excel.Workbook
    Dim xlWorkSheet As Excel.Worksheet

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        ' Create a new Excel application
        xlApp = New Excel.Application()

        ' Add a new workbook
        xlWorkBook = xlApp.Workbooks.Add()

        ' Add a new worksheet
        xlWorkSheet = CType(xlWorkBook.Worksheets(1), Excel.Worksheet)

        ' Make changes to the worksheet programmatically
        xlWorkSheet.Cells(1, 1).Value = "Hello, Excel!"
        xlWorkSheet.Cells(2, 1).Value = "This is a programmatic change."

        ' Protect the worksheet to prevent user edits
        xlWorkSheet.Protect(Password:="123", DrawingObjects:=True, Contents:=True, Scenarios:=True)
        xlWorkBook.SaveAs("C:\File.xlsx")

    End Sub

    ' Clean up resources when the form is closed
    Private Sub Form1_FormClosed(sender As Object, e As FormClosedEventArgs) Handles MyBase.FormClosed
        ' Release Excel objects
        ReleaseObject(xlWorkSheet)
        ReleaseObject(xlWorkBook)
        ReleaseObject(xlApp)
    End Sub

    ' Helper method to release Excel objects
    Private Sub ReleaseObject(obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub
End Class

This code creates a new Excel workbook, adds a worksheet, makes changes to the cells, and then protects the worksheet with a password. Users won't be able to edit cells directly in Excel due to the protection, but your program can still make changes programmatically.

The result:

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.