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

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" <(E-Mail Removed)> wrote in message
news:6C4966D5-A497-418B-85A4-(E-Mail Removed)...
> 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

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" <(E-Mail Removed)> wrote in message
> news:6C4966D5-A497-418B-85A4-(E-Mail Removed)...
> > 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
>
>
>

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(xlConstants).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

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" <(E-Mail Removed)> wrote in message
news:44E3915A-E786-4582-B72F-(E-Mail Removed)...
>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(xlConstants).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

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" <(E-Mail Removed)> wrote in message
> news:44E3915A-E786-4582-B72F-(E-Mail Removed)...
> >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(xlConstants).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
>
>
>