Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I disable the right-click shortcut menu on charts | Excel Programming | |||
disable customize option when right click on menu bar | Excel Programming | |||
disable customize option when right click on menu bar | Excel Discussion (Misc queries) | |||
disable right click menu | Excel Programming | |||
disable right mouse click on "Worksheet Menu Bar" | Excel Programming |