Macro not allowing inserting cells/colums/rows
 

Claudio,
Add a standard module and class module to the WB, then add the code below.
Adjust the menu IDs for the actions you wish to monitor/control.
Obviously macro and events must both be .Enabled for this to work.

< ThisWork module
Private Sub Workbook_Open()
Set gclsCbarEvents = New CCbarEvents
End Sub
</ ThisWork module

< Standard module
Public gclsCbarEvents As CCbarEvents
</ Standard module

< Class module
Private WithEvents mRowDelButton As CommandBarButton
Private WithEvents mCellDelButton As CommandBarButton

Private Sub Class_Initialize()

Set mRowDelButton = Application.CommandBars.FindControl(, 293)
Set mCellDelButton = Application.CommandBars.FindControl(, 478)

End Sub

Private Sub mCellDelButton_Click(ByVal Ctrl As Office.CommandBarButton,
CancelDefault As Boolean)

If TypeName(Selection) = "Range" Then
If Selection.Address = Selection.EntireRow.Address Then
MsgBox Selection.Rows.Count & " Row" & _
IIf(Selection.Rows.Count = 1, "", "s") & " deleted."
End If
End If

End Sub

Private Sub mRowDelButton_Click(ByVal Ctrl As Office.CommandBarButton,
CancelDefault As Boolean)

MsgBox Selection.Rows.Count & " Row" & _
IIf(Selection.Rows.Count = 1, "", "s") & " deleted."

End Sub
</ Class module

NickHK

"claudio" wrote in message
...
Hi all,

I need a macro that brings up a message if the user inserts a
cell/column/row into the sheet (some users crack the password of the
protection, and I want them to be reminded not to change the sheet).
I have an ugly solution working with values in a column, added up and
checked, but I'm sure there is a direct solution in VBA.

I would appreciate help.
Thanx
Claudio

Macro not allowing inserting cells/colums/rows
 

Yes, did you try the code, changing the ID to that of the required insert
controls and setting CancelDefault =True.

NickHK

"claudio" wrote in message
...
Thank you for the effort! However, as the titel says, the user must not
add
cells/columns/rows! Buttons are therefore not an option.

I will use the "ugly" solution where the value of a cell changes if the
user
adds cells/colums/rows, and the macro checks this value after every
worksheet.change.

"NickHK" wrote:

Claudio,
Add a standard module and class module to the WB, then add the code
below.
Adjust the menu IDs for the actions you wish to monitor/control.
Obviously macro and events must both be .Enabled for this to work.

< ThisWork module
Private Sub Workbook_Open()
Set gclsCbarEvents = New CCbarEvents
End Sub
</ ThisWork module

< Standard module
Public gclsCbarEvents As CCbarEvents
</ Standard module

< Class module
Private WithEvents mRowDelButton As CommandBarButton
Private WithEvents mCellDelButton As CommandBarButton

Private Sub Class_Initialize()

Set mRowDelButton = Application.CommandBars.FindControl(, 293)
Set mCellDelButton = Application.CommandBars.FindControl(, 478)

End Sub

Private Sub mCellDelButton_Click(ByVal Ctrl As Office.CommandBarButton,
CancelDefault As Boolean)

If TypeName(Selection) = "Range" Then
If Selection.Address = Selection.EntireRow.Address Then
MsgBox Selection.Rows.Count & " Row" & _
IIf(Selection.Rows.Count = 1, "", "s") & " deleted."
End If
End If

End Sub

Private Sub mRowDelButton_Click(ByVal Ctrl As Office.CommandBarButton,
CancelDefault As Boolean)

MsgBox Selection.Rows.Count & " Row" & _
IIf(Selection.Rows.Count = 1, "", "s") & " deleted."

End Sub
</ Class module

NickHK

"claudio" wrote in message
...
Hi all,

I need a macro that brings up a message if the user inserts a
cell/column/row into the sheet (some users crack the password of the
protection, and I want them to be reminded not to change the sheet).
I have an ugly solution working with values in a column, added up and
checked, but I'm sure there is a direct solution in VBA.

I would appreciate help.
Thanx
Claudio