특정 셀을 잠그지만 필터링 및 정렬을 허용하는 방법

특정 셀을 잠그지만 필터링 및 정렬을 허용하는 방법

다음 코드를 사용하여 특정 셀의 내용을 잠그고 있습니다.

Sub LockCell(ws As Worksheet, strCellRng As String)
  With ws
   .Unprotect
   .Cells.Locked = False
   .Range(strCellRng).Locked = True
   .Protect Contents:=True, AllowFormattingCells:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True, AllowInsertingColumns:=True, AllowInsertingRows:=True, AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True, DrawingObjects:=True
  End With
End Sub

특정 열의 내용을 잠급니다.문제는 이러한 Excel 메뉴 항목이 비활성화되어 있기 때문에 사용자가 셀을 정렬하거나 필터링하거나 셀에 테두리를 적용할 수 없다는 것입니다.

나는 생각했습니다.AllowSorting:=True,AllowFiltering:=True그리고.DrawingObjects:=True그것은 같은 방식으로 허용할 것입니다.AllowFormattingColumns:=True그리고.AllowFormattingRows:=True허용된 크기 조정.

이런 어려움을 겪는 사람들이 많습니다.일반적인 대답은 제한 없이 정렬할 수 있는 동안 내용을 편집으로부터 보호할 수 없다는 것입니다.옵션은 다음과 같습니다.

편집 및 정렬 허용:(

보호를 적용하고 VBA를 사용하여 정렬할 코드가 있는 버튼을 만듭니다.이 방법을 설명하는 다른 게시물들이 있습니다.( (1) 시트해거가나코, 을적다한음, 를다시용시생, (2) 하두보있을 사용하여 두 합니다.UserInterfaceOnly:=True.

사용자가 셀을 선택할 수 없는 로리의 답변(https://stackoverflow.com/a/15390698/269953)

아직 논의되지 않은 한 가지 해결책은 VBA를 사용하여 기본적인 보호 기능을 제공하는 것입니다.를 들어, 예를들어, 다사여변내를 사용하여 .Worksheet_Change그러나 이상적인 해결책과는 거리가 멀습니다.

사용자가 데이터를 선택할 때 시트를 보호하고 헤더를 선택한 경우 시트를 보호하지 않은 상태로 유지할 수 있습니다.이는 사용자가 데이터를 엉망으로 만드는 동시에 사용성 문제를 야기할 수 있는 수많은 방법을 남겼지만, 적어도 성가신 동료들이 무분별하게 원치 않는 변경을 할 가능성을 줄여줍니다.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If (Target.row = HEADER_ROW) Then
        wsMainTable.Unprotect Password:=PROTECTION_PASSWORD
    Else
        wsMainTable.Protect Password:=PROTECTION_PASSWORD, UserInterfaceOnly:=True
    End If
End Sub

이것은 저에게 큰 문제였고 저는 비교적 간단한 답으로 다음 링크를 찾았습니다.보이저 감사합니다!!!

다른 사용자가 정렬할 수 있도록 범위 이름을 지정했습니다.

• 워크시트 보호 해제
• "보호"--- "사용자에게 범위 편집 허용"으로 이동합니다(Excel 2007인 경우, "검토" 탭).
• "새" 범위 추가
• 사용자가 정렬할 수 있도록 허용할 범위 선택
• "시트 보호"를 클릭
• 이번에는 *사용자가 "잠긴 셀"을 선택할 수 없습니다**
• 네 알겠습니다

http://answers.yahoo.com/question/index?qid=20090419000032AAs5VRR

거의 동일한 기능을 얻을 수 있는 까다로운 방법을 생각해 냈습니다.일반적인 방법으로 시트를 보호하는 대신 이벤트 처리기를 사용하여 사용자가 하려는 모든 작업을 취소합니다.

워크시트의 모듈에 다음을 추가합니다.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Locked = True Then
        Application.EnableEvents = False
        Application.Undo
        Application.EnableEvents = True
    End If
End Sub

사용자가 잠긴 셀을 변경하는 작업을 수행하면 작업이 즉시 취소됩니다.이벤트를 일시적으로 비활성화하는 것은 실행 취소 자체가 이 이벤트를 트리거하여 무한 루프를 발생시키지 않도록 하는 것입니다.

정렬 및 필터링은 변경 이벤트를 트리거하지 않으므로 이러한 기능은 활성화된 상태로 유지됩니다.

이 솔루션은 셀 내용을 변경하거나 지우는 것을 방지하지만 형식 변경은 방지하지 않습니다.결심이 확고한 사용자는 단순히 셀을 잠금 해제하도록 설정함으로써 이를 피할 수 있습니다.

다음은 문제와 솔루션에 대해 더 자세히 설명하는 기사입니다.

보호된 워크시트에서 잠긴 셀 정렬

이해해야 할 것은 셀을 잠그는 목적은 셀이 변경되는 것을 방지하는 것이고 정렬은 셀 값을 영구적으로 변경하는 것입니다.매크로를 작성할 수 있지만 "사용자에게 범위 편집 허용" 기능을 사용하는 것이 훨씬 더 좋습니다.이렇게 하면 셀을 편집할 수 있으므로 정렬할 수 있지만, 셀이 여전히 기술적으로 잠겨 있기 때문에 사용자가 셀을 선택할 수 없습니다.

이것이 아주 오래된 것이라는 것을 알지만, 이 문제를 검색할 때마다 나타납니다.위의 셀에 지정된 범위 보호를 해제한 다음 보호되지 않은 셀에 데이터 유효성 검사를 추가하여 "423fdgfdsg3254fer"와 같은 터무니없는 것을 참조할 수 있습니다. 그러면 사용자가 해당 셀을 편집하려고 하면 편집할 수 없지만 정렬하고 필터링할 수 있습니다.

Lorie의 답변은 좋지만 사용자가 잠금 및 잠금 해제된 셀이 포함된 범위를 선택하면 잠금/보호된 셀의 데이터를 삭제할 수 있습니다.

Isaac의 답변은 훌륭하지만 사용자가 잠긴 셀과 잠금 해제된 셀이 모두 있는 범위를 강조 표시하면 작동하지 않습니다.

목표 범위에 있는 셀 중 하나라도 잠겼을 경우 변경을 취소하기 위해 Isaac의 코드를 조금 수정했습니다.또한 작업이 실행 취소된 이유를 설명하는 메시지도 표시됩니다.Lorie의 답변과 결합하여, 저는 사용자가 보호되지 않은 셀을 변경할 수 있는 동시에 보호된 시트를 정렬/필터링할 수 있다는 원하는 결과를 얻을 수 있었습니다.

Lorie의 답변에 나와 있는 지침에 따라 워크시트 모듈에 다음 코드를 입력합니다.

Private Sub Worksheet_Change(ByVal Target As Range)
    For Each i In Target
       If i.Locked = True Then
            Application.EnableEvents = False
            Application.Undo
            Application.EnableEvents = True
            MsgBox "Your action was undone because it made changes to a locked cell.", , "Action Undone"
        Exit For
        End If
    Next i
End Sub

자동 필터링이 서브루틴 작업의 일부인 경우,

BioSum.Unprotect "letmein"

'<Your function here>

BioSum.Cells(1, 1).Activate
BioSum.Protect "letmein" 

일시적으로 시트 보호를 해제하고 셀을 필터링한 후 다시 보호합니다.

이것은 매우 오래되었지만 여전히 매우 유용한 실입니다.저는 최근에 같은 문제로 여기에 왔습니다.필터 행(예: 1열)을 선택하면 시트를 보호하고 보호를 해제하는 것이 좋습니다.내 솔루션은 암호 보호를 사용하지 않습니다. 암호 보호는 필요 없습니다(보안 기능이 아닌 보호 기능임).필터 버튼 선택을 인식하는 이벤트 핸들러를 찾을 수 없어서 사용자에게 먼저 필터 셀을 선택하고 필터 버튼을 클릭하라는 지침을 전달했습니다.제가 옹호하는 것은 다음과 같습니다. (보호를 변경해야 할 경우에만 보호를 변경하여 시간을 절약할 수도 있고 그렇지 않을 수도 있습니다. 잘 모르겠지만 "옳다고 생각"합니다.)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Const FilterRow = 1
  Dim c As Range
  Dim NotFilterRow As Boolean
  Dim oldstate As Boolean
  Dim ws As Worksheet
  Set ws = ActiveSheet
  oldstate = ws.ProtectContents
  NotFilterRow = False
  For Each c In Target.Cells
     NotFilterRow = c.Row <> FilterRow
     If NotFilterRow Then Exit For
  Next c
  If NotFilterRow <> oldstate Then
     If NotFilterRow Then
        ws.Protect
     Else
        ws.Unprotect
     End If
  End If
  Set ws = Nothing
End Sub

Excel 2007에서 데이터를 입력할 셀의 잠금을 해제합니다.검토로 이동

 > Protect Sheet
 > Select Locked Cells (already selected)
 > Select unlocked Cells (already selected)
 > (and either) select Sort (or) Auto Filter 

VB가 필요하지 않음

저도 비슷한 문제가 있었어요.저는 사용자가 보호된 워크시트에서 "표 3"을 필터링할 수 있기를 원했습니다.그러나 사용자는 테이블을 편집할 수 없습니다.나는 아래의 vba 코드를 사용하여 위의 작업을 완료했습니다.

Range("Table3").Select
Selection.Locked = True
Selection.FormulaHidden = False 
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
    , allowfiltering:=True

다음 코드에서 VBA를 사용하여 코드를 필터링했습니다.

Range("Table3[[#Headers],[Aantal4]]").Select
ActiveSheet.ListObjects("Table3").Range.AutoFilter Field:=8, Criteria1:= _
    Array("1", "12", "2", "24", "4", "6"), Operator:=xlFilterValues

언급URL : https://stackoverflow.com/questions/10197772/how-to-lock-specific-cells-but-allow-filtering-and-sorting