ilarson007
New Member
- Joined
- Jan 23, 2014
- Messages
- 3
I have a sheet that I want to disable the user from editing in my workbook. I found some code that worked great by protecting the sheet and disallowing scroll and whatnot. However, by protecting the worksheet, the users on our network here could only open the sheet as read-only, and can't save it (they are importing/processing data with the sheet), and having it as a master file is useless if they can't save the data they are importing. Is there anyway to disable all editing (including cells, text boxes, command buttons, locations of pictures and buttons and text boxes, etc) without protecting the sheet?
The code I have now just disables scroll and cell selection:
The code I have now just disables scroll and cell selection:
Code:
Option Explicit
Public sName As String
Public sWS As Worksheet
Dim iArray As Variant
Private Sub SheetLock()
Set sWS = ThisWorkbook.ActiveSheet
sName = sWS.Name
' Kill Cell Selection on Active Sheet
With ThisWorkbook.ActiveSheet
' Disable scrolling by setting the scroll area to one cell
.ScrollArea = Range("A1").Address
' Disable cell selection
.EnableSelection = xlNoSelection
End With
MsgBox (sName & " Sheet successfully locked.")
End Sub
Private Sub SheetUnlock()
Set sWS = ThisWorkbook.ActiveSheet
sName = sWS.Name
' Reset the Active sheet
With ThisWorkbook.ActiveSheet
.Range("A1").Select
' Re-open the scroll area
.ScrollArea = Empty
' Allow for cell selection
.EnableSelection = xlNoRestrictions
End With
MsgBox (sName & " Sheet successfully unlocked.")
End Sub
'Other code