VBA How to "Lock" Excel Sheet [closed]

Closed. This question does not meet Stack Overflow guidelines. It is not currently accepting answers.

---

This question does not appear to be about a specific programming problem, a software algorithm, or software tools primarily used by programmers. If you believe the question would be on-topic on another Stack Exchange site, you can leave a comment to explain where the question may be able to be answered.

Closed 8 years ago.

Improve this question 开发者_如何转开发

Is there a way to lock the excel sheet from being altered by the user while my vba code runs and modifies the excel sheet?

---

If your code is active then I'm not sure why the user has the ability to modify data at the same time - the macro hourglass should be displayed rendering interaction impossible

Can you pls post your code?

Options you may consider include

1. adding a progress bar to let users know something is happening (ie don't play while code is running), and how long there is to go This example from Walkenbach is useful http://spreadsheetpage.com/index.php/tip/displaying_a_progress_indicator/

2. making the Excel Application invisible while the work is occurring

   Application.Visible = False
   'code
   Application.Visible = True
   

---

Coming from Excel 2003 ... protecting a sheet without additional actions will prevent user entry and VBA manipulations. In order to prevent user interaction with locked cells but still allow VBA you may use the UserInterfaceOnly property. This can only be done via VBA, there is no way to "manually" protect a sheet in this way. If you manually unprotect a sheet this property is removed. Therefore it's a good idea to set this property on workbook/sheet open/activate events, e.g.

Private Sub Workbook_Open()
Dim SH As Worksheet
    ' allow some non-destructive activities
    For Each SH In ActiveWorkbook.Worksheets
        SH.Protect DrawingObjects:=False, Contents:=True, UserInterfaceOnly:=True, _
                        AllowSorting:=True, AllowFiltering:=True, _
                        AllowUsingPivotTables:=True
    Next SH
End Sub