Disable right click menu
 

Hi,
I'm using XL2007. I have a protected spreadsheet with the area for data
entry being in the unlocked state. However, I have one column in that area
that is locked (it contains an auto sum) and I need the ability to delete a
row. I have a Workbook_SheetBeforeRightClick event that will delete the row,
but the menu keeps popping up. Can I disable the menu or is there an easier
way to delete a row? I'm already using the Workbook_SheetBeforeDoubleClick to
insert a row.
Thanks.
Mark

Disable right click menu
 

You can use the same event to do more than one task by using If statements:

Example only:

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, _
ByVal Target As Range, Cancel As Boolean)
If Condition1 = True Then 'or False
'Do Something - Insert a row?
End If
If Condition1 = True Then 'or False
'Do Something else - Delete a row?
End If
End Sub

"Mark" wrote in message
...
Hi,
I'm using XL2007. I have a protected spreadsheet with the area for data
entry being in the unlocked state. However, I have one column in that area
that is locked (it contains an auto sum) and I need the ability to delete
a
row. I have a Workbook_SheetBeforeRightClick event that will delete the
row,
but the menu keeps popping up. Can I disable the menu or is there an
easier
way to delete a row? I'm already using the Workbook_SheetBeforeDoubleClick
to
insert a row.
Thanks.
Mark

Disable right click menu
 

Thanks, JLGWhiz, I never thought about that .............. but how would I
define the conditions? I don't understand how to code the change event to
differentiate between adding or deleting a row. What I have is a student
score sheet and the teachers need to be able enter new students that enroll,
or existing students leave, during the semester. Can you please give me a
little more insight?
Thanks.
Mark

"JLGWhiz" wrote:

You can use the same event to do more than one task by using If statements:

Example only:

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, _
ByVal Target As Range, Cancel As Boolean)
If Condition1 = True Then 'or False
'Do Something - Insert a row?
End If
If Condition1 = True Then 'or False
'Do Something else - Delete a row?
End If
End Sub

"Mark" wrote in message
...
Hi,
I'm using XL2007. I have a protected spreadsheet with the area for data
entry being in the unlocked state. However, I have one column in that area
that is locked (it contains an auto sum) and I need the ability to delete
a
row. I have a Workbook_SheetBeforeRightClick event that will delete the
row,
but the menu keeps popping up. Can I disable the menu or is there an
easier
way to delete a row? I'm already using the Workbook_SheetBeforeDoubleClick
to
insert a row.
Thanks.
Mark

Disable right click menu
 

I put a little thought into this problem and I think I came up with a
workable solution. What I ended up doing was restricting the delete row
routine to double clicking on column "A", however I'm getting an error (424 -
Object required) on the delete. Can anyone help straighten me out on this one?
Thanks.
Mark

Sub XtraRow(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
If TypeName(Sh) = "Worksheet" Then
With Sh
.Unprotect
Cancel = True 'Eliminate Edit status due to doubleclick
If Target.Column < 1 Then
Target.Offset(1).EntireRow.Insert
Target.EntireRow.Copy Target.Offset(1).EntireRow
On Error Resume Next
Target.Offset(1).EntireRow.SpecialCells(xlConstant s).ClearContents
Target.Offset(1, 0).Select
If Target.Row = 13 Then
With Selection.Font
.Name = "Times New Roman"
.Size = 12
.Bold = True
End With
End If
On Error GoTo 0
Else
Target.EntireRow.Delete <= Error 424 occurs here
On Error Resume Next
Target.Offset(1, 0).Select
End If
.Protect
End With
End If
End Sub

"Mark" wrote:

Hi,
I'm using XL2007. I have a protected spreadsheet with the area for data
entry being in the unlocked state. However, I have one column in that area
that is locked (it contains an auto sum) and I need the ability to delete a
row. I have a Workbook_SheetBeforeRightClick event that will delete the row,
but the menu keeps popping up. Can I disable the menu or is there an easier
way to delete a row? I'm already using the Workbook_SheetBeforeDoubleClick to
insert a row.
Thanks.
Mark

Disable right click menu
 

I used the BeforeDoubleClick event and it worked OK. Not sure what you are
doing with the title line you are using in your postl.


"Mark" wrote in message
...
I put a little thought into this problem and I think I came up with a
workable solution. What I ended up doing was restricting the delete row
routine to double clicking on column "A", however I'm getting an error
(424 -
Object required) on the delete. Can anyone help straighten me out on this
one?
Thanks.
Mark

Sub XtraRow(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
If TypeName(Sh) = "Worksheet" Then
With Sh
.Unprotect
Cancel = True 'Eliminate Edit status due to doubleclick
If Target.Column < 1 Then
Target.Offset(1).EntireRow.Insert
Target.EntireRow.Copy Target.Offset(1).EntireRow
On Error Resume Next
Target.Offset(1).EntireRow.SpecialCells(xlConstant s).ClearContents
Target.Offset(1, 0).Select
If Target.Row = 13 Then
With Selection.Font
.Name = "Times New Roman"
.Size = 12
.Bold = True
End With
End If
On Error GoTo 0
Else
Target.EntireRow.Delete <= Error 424 occurs here
On Error Resume Next
Target.Offset(1, 0).Select
End If
.Protect
End With
End If
End Sub

"Mark" wrote:

Hi,
I'm using XL2007. I have a protected spreadsheet with the area for data
entry being in the unlocked state. However, I have one column in that
area
that is locked (it contains an auto sum) and I need the ability to delete
a
row. I have a Workbook_SheetBeforeRightClick event that will delete the
row,
but the menu keeps popping up. Can I disable the menu or is there an
easier
way to delete a row? I'm already using the
Workbook_SheetBeforeDoubleClick to
insert a row.
Thanks.
Mark

Disable right click menu
 

Thanks, JLGWhiz, but I was simply being stupid - I was deleting the target,
which was generating the error, so I simply selected the active cell which
took care of the problem. It's amazing what a pot of coffee will do for me,
first thing in the morning.
Mark

"JLGWhiz" wrote:

I used the BeforeDoubleClick event and it worked OK. Not sure what you are
doing with the title line you are using in your postl.


"Mark" wrote in message
...
I put a little thought into this problem and I think I came up with a
workable solution. What I ended up doing was restricting the delete row
routine to double clicking on column "A", however I'm getting an error
(424 -
Object required) on the delete. Can anyone help straighten me out on this
one?
Thanks.
Mark

Sub XtraRow(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
If TypeName(Sh) = "Worksheet" Then
With Sh
.Unprotect
Cancel = True 'Eliminate Edit status due to doubleclick
If Target.Column < 1 Then
Target.Offset(1).EntireRow.Insert
Target.EntireRow.Copy Target.Offset(1).EntireRow
On Error Resume Next
Target.Offset(1).EntireRow.SpecialCells(xlConstant s).ClearContents
Target.Offset(1, 0).Select
If Target.Row = 13 Then
With Selection.Font
.Name = "Times New Roman"
.Size = 12
.Bold = True
End With
End If
On Error GoTo 0
Else
Target.EntireRow.Delete <= Error 424 occurs here
On Error Resume Next
Target.Offset(1, 0).Select
End If
.Protect
End With
End If
End Sub

"Mark" wrote:

Hi,
I'm using XL2007. I have a protected spreadsheet with the area for data
entry being in the unlocked state. However, I have one column in that
area
that is locked (it contains an auto sum) and I need the ability to delete
a
row. I have a Workbook_SheetBeforeRightClick event that will delete the
row,
but the menu keeps popping up. Can I disable the menu or is there an
easier
way to delete a row? I'm already using the
Workbook_SheetBeforeDoubleClick to
insert a row.
Thanks.
Mark