防止用户根据该行中的单元格的内容删除某些行 [英] Prevent user from deleting certain rows based on contents of a cell in that row

问题描述

我有一个我想要保护的模板文件，以便用户不能修改公式。当工作表被保护时，我已经写了一个宏来允许用户插入行。我还希望一个宏允许用户删除行，但我想阻止用户删除某些关键行（例如，检查总计和标题等）。

为此，我在模板中使用了列L来标识不能删除的行。对于这些行，我在列L的行中有单词keep。我在下面写了一个基本的删除宏，但是我需要修改它以查看列L的所选范围 rRange 和退出Sub 如果单词保持在那里

*请注意， rRange 可能包含多个相邻的行，因此宏将需要退出，如果其中任何一个行无法测试。

  Sub DeteteRows（）
 
 Dim rRange As Range 
 On错误恢复Next 
 Application.DisplayAlerts = False 
设置rRange = Application.InputBox（提示：= _ 
请使用鼠标选择要删除的行。，_ 
标题：=SPECIFY ROW TO DELETE，类型：= 8）
错误GoTo 0 
 Application.DisplayAlerts = True 
如果rRange不是，然后
 
退出子
 
 Else 
 
 rRange.EntireRow.Delete 
范围（a1）。选择
 
 MsgBox（Row（s）Deteted ）
 End If 
 
 End Sub 
  

解决方案

这可能不是最好的方法，但它在下面。我没有添加删除部分在最后，如果否则，因为我想你可以处理这个

  Sub DeteteRows（）
 Dim rRange As Range 
 Dim bKeepFound As Boolean 
 bKeepFound = False 
 On Error Resume Next 
 Application.DisplayAlerts = False 
设置rRange = Application.InputBox（提示符：= _ 
请使用鼠标选择要删除的行。，_ 
标题：=SPECIFY ROW TO DELETE，类型：= 8）
错误GoTo 0 
 Application.DisplayAlerts = True 
如果rRange不是，然后
退出Sub 
'不需要else语句，因为如果失败，则退出sub 
 End If 
 
对于每一行在rRange.Rows 
 Dim s'变量来保存数组
s = Split（Row.Address，：）'分割列和行
'删除$并转换为一个数字，然后检查单元格值
如果rRange.Cells（CInt（替换（s（0），$，）），12）.Value =保持然后
 bKeepFound = True 
结束如果
下一行
'检查以查看是否找到一行保留
如果bKeepFound然后
退出Sub'行被发现，因此退出sub 
 Else 
'删除范围内的行
 End If 
 
 End Sub 
  

I have a template file that I want to protect so that users cannot modify formulas. As the sheet is protected, I have written a macro to allow the user to insert rows. I also want a macro to allow the user to delete rows, but I want to prevent the user from deleting certain critical rows (e.g. check totals and headings, etc.).

To this end I have used column L in my template to identify rows that cannot be deleted. For these rows I have the word "keep" in that row of column L. I have written a basic delete macro below but I need to modify it to look in column L of the selected range rRange and Exit Sub if the word "keep" is there.

*Note that rRange could contain a number of adjacent rows so the macro would need to exit if any of those rows fail the test.

Sub DeteteRows()

Dim rRange As Range
On Error Resume Next
    Application.DisplayAlerts = False
     Set rRange = Application.InputBox(Prompt:= _
            "Please use mouse to select a row to Delete.", _
                Title:="SPECIFY ROW TO DELETE", Type:=8)
On Error GoTo 0
    Application.DisplayAlerts = True
    If rRange Is Nothing Then

    Exit Sub

    Else

rRange.EntireRow.Delete
Range("a1").Select

MsgBox ("Row(s) Deteted")
    End If

End Sub

解决方案

This may not be the best way but it is below. I did not add the delete portion in the last if then else as I figured you can handle that

Sub DeteteRows()
Dim rRange As Range
Dim bKeepFound As Boolean
bKeepFound = False
On Error Resume Next
Application.DisplayAlerts = False
Set rRange = Application.InputBox(Prompt:= _
"Please use mouse to select a row to Delete.", _
Title:="SPECIFY ROW TO DELETE", Type:=8)
On Error GoTo 0
    Application.DisplayAlerts = True
    If rRange Is Nothing Then
        Exit Sub
        'dont need the else statement cause you exit the sub if it fails
    End If

    For Each Row In rRange.Rows
    Dim s 'variable to hold the array
    s = Split(Row.Address, ":") 'split out the column and row
        'remove the $ and convert to a number then check the cell value
        If rRange.Cells(CInt(Replace(s(0), "$", "")), 12).Value = "keep" Then
            bKeepFound = True
        End If
    Next Row
    'check to see if a row was found to keep
    If bKeepFound Then
        Exit Sub 'row was found so exit sub
    Else
        'delete the rows in the range
    End If

End Sub

这篇关于防止用户根据该行中的单元格的内容删除某些行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！