Prevent Coloumn & Row Deletion / Insertion
Mike,
Brilliant thanks again...must admit was reading and re-reading original
post, could I trouble you on one last question please?
Using the principle above, is there a code to stop anyone deleting the
workbook when its in its folder? and also stop them making a copy when they
have opened in read only mode - this is frequent despite my rantings and
ravings
We have approx 60 users inputting data, 5 have editing rights and two with
full permissions - as all the sheets designed contain vital customer data,
can i prevent accidental deletion? - some users do not read before acting!!!
Craig
"Mike H" wrote:
Forgot the password bit, now includes override pasword set to a case
sensitive MyPass
Change to suit
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim Msg As String
Dim pWord As String
pWord = "MyPass"
Msg = "Deleting Rows/Columns Not Permitted"
If Target.Address = Target.EntireRow.Address Or _
Target.Address = Target.EntireColumn.Address Then
response = InputBox("Enter password")
If response = pWord Then Exit Sub
With Application
.EnableEvents = False
.Undo
Msg = MsgBox(Msg, 16, "WARNING")
.EnableEvents = True
End With
Else
Exit Sub
End If
End Sub
--
Mike
When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.
"Craig" wrote:
Hi,
Is there a code to prevent users from deleting rows or columns in all the
sheets within a workbook, but allowing an authorised user (me), to input a
password to then carryout deletion / insertion.
I have a number of users who have access to the password to unprotect the
sheet for editing....the vba password is known only to two of us and i want
to stop my colleagues from 'accidentally' changing things without my being
asked.
I have used a variation of this so far (thanks to John for the post) but
need to expand as stated above:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Sh.Name = "Sheet2" Then
If Target.Address = Target.EntireRow.Address Then
With Application
.EnableEvents = False
.Undo
msg = MsgBox("Deleting Rows Not Permitted", 16, "WARNING")
.EnableEvents = True
End With
Else
Exit Sub
End If
End If
End Sub
|