Disable Editing a Sheet without Locking it?

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:

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
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Watch MrExcel Video

Forum statistics

Threads
1,122,672
Messages
5,597,488
Members
414,146
Latest member
marginmakerb

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top