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

Craig,

Modified to work for rows and columns on all sheeets

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim Msg As String
Msg = "Deleting Rows/Columns Not Permitted"
If Target.Address = Target.EntireRow.Address Or _
Target.Address = Target.EntireColumn.Address Then
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

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

Mike,

Excellent, works perfectly, many thanks, but how do I gain access to delete
/ add...its probably blindingly obvious, sorry if daft question

Craig

"Mike H" wrote:

Craig,

Modified to work for rows and columns on all sheeets

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim Msg As String
Msg = "Deleting Rows/Columns Not Permitted"
If Target.Address = Target.EntireRow.Address Or _
Target.Address = Target.EntireColumn.Address Then
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

Craig,

Glad i could help, see my other post for the password bit
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Craig" wrote:

Mike,

Excellent, works perfectly, many thanks, but how do I gain access to delete
/ add...its probably blindingly obvious, sorry if daft question

Craig

"Mike H" wrote:

Craig,

Modified to work for rows and columns on all sheeets

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim Msg As String
Msg = "Deleting Rows/Columns Not Permitted"
If Target.Address = Target.EntireRow.Address Or _
Target.Address = Target.EntireColumn.Address Then
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

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

Craig,

Using the principle above, is there a code to stop anyone deleting the
workbook when its in its folder?

If you mean can you stop someone browsing to the folder containing the
workbook and deleting it then I think this is a Windows issue and not an
Excel one and I don't know how to do that. Maybe you could create a backup
every time you or a user saves. There's a piece of code by Don Guillett at
the link below which you could put in the 'Before save' event to auto create
a backup to another directory.

http://www.mvps.org/dmcritchie/excel/backup.htm

and also stop them making a copy when they
have opened in read only mode - this is frequent despite my rantings and
ravings

No

as all the sheets designed contain vital customer data,
can i prevent accidental deletion?

If you search these forums or Google for that you'll find lots of
suggestions. However, IMHO none will provide the necessary level of
protection for 'Vital Customer data' Excel protection is simply not robust
enough for that. It seems to me essential that you introduce automatic backup
noted in the link above.

HTH


--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Craig" wrote:

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

Mike,

Yet aqain, very useful info, you have certainly made my sheets better to
protect and increased my poor knowledge of vba .....again Thank You.

Craig

"Mike H" wrote:

Craig,

Using the principle above, is there a code to stop anyone deleting the
workbook when its in its folder?

If you mean can you stop someone browsing to the folder containing the
workbook and deleting it then I think this is a Windows issue and not an
Excel one and I don't know how to do that. Maybe you could create a backup
every time you or a user saves. There's a piece of code by Don Guillett at
the link below which you could put in the 'Before save' event to auto create
a backup to another directory.

http://www.mvps.org/dmcritchie/excel/backup.htm

and also stop them making a copy when they
have opened in read only mode - this is frequent despite my rantings and
ravings

No

as all the sheets designed contain vital customer data,
can i prevent accidental deletion?

If you search these forums or Google for that you'll find lots of
suggestions. However, IMHO none will provide the necessary level of
protection for 'Vital Customer data' Excel protection is simply not robust
enough for that. It seems to me essential that you introduce automatic backup
noted in the link above.

HTH


--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Craig" wrote:

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