All geek questions in one place

Allow insertion on a worksheet without overwriting locked cells

I have a protected worksheet that users would like to copy and paste. I can not control the book from which they copy.

A protected worksheet has several lines available for data entry, and other lines that are locked and grayed out to the user. Users would like to be able to insert the top of the entire sheet from another random workbook and have all the cells available for data entry filled out, while the locked cells are not broken. In the current state, the user receives an error message when trying to insert, because he cannot insert locked cells.

Example :
Worksheet 1:

Act1 100 100 100
Act2 100 100 100
Act3 100 100 100

Worksheet 2: (Second line locked)

Act1 300 300 300
Act2 200 200 200
Act3 100 100 100

After copying / pasting the worksheet 2 should look like this:

Act1 100 100 100
Act2 200 200 200
Act3 100 100 100

The values ​​from sheet 1 are populated, and the locked rows are not changed.

  • I was thinking that you have a hook where on the insert the locked cells are unlocked, so the insert can happen, and then return to the original values ​​and lock.
  • Is there a way that I can scroll through cells in the clipboard and only paste cells where the target is not locked?
  • , , , .
  • , , , .
+3
4

:

  • .

:

  • Excel.
  • unprotecting temp
  • , .
  • ( )
  • .

Jan Karel Catch Paste . , , .

ThisWorkbook

Private mdNextTimeCatchPaste As Double

Private Sub Workbook_Activate()
    REM Add Paste event handler
    CatchPaste
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    REM Restore Paste event handler
    StopCatchPaste
    mdNextTimeCatchPaste = Now
    Application.OnTime mdNextTimeCatchPaste, "'" & ThisWorkbook.Name & "'!UnProtectPasteToSheet"
End Sub


Private Sub Workbook_Deactivate()
    REM Restore Paste event handler
    StopCatchPaste
    On Error Resume Next
    REM Cancel scheduled macroREM s,
    REM because we might be closing the file
    Application.OnTime mdNextTimeCatchPaste, "'" & ThisWorkbook.Name & "'!UnProtectPasteToSheet", , False
End Sub

Private Sub Workbook_Open()
    REM Add Paste event handler
    CatchPaste
End Sub


REM Add Paste event handler
Public Sub CatchPaste()
REM these are the ways you can Paste in to Excel
REM refer to http://www.jkp-ads.com/articles/catchpaste.asp for more details
Application.OnKey "^v", "UnProtectPasteToSheet"
Application.OnKey "^{Insert}", "UnProtectPasteToSheet"
Application.OnKey "+{Insert}", "UnProtectPasteToSheet"
Application.OnKey "~", "UnProtectPasteToSheet"
Application.OnKey "{Enter}", "UnProtectPasteToSheet"
End Sub
REM restore all default events
Public Sub StopCatchPaste()
Application.OnKey "^v", ""
Application.OnKey "^{Insert}", ""
Application.OnKey "+{Insert}", ""
Application.OnKey "~", ""
Application.OnKey "{Enter}", ""
End Sub

REM Here we will check the sheet is protected, if it is then paste to a temp sheet,
REM unprotect main sheet, paste the values, and restore locked cells
Private Sub UnProtectPasteToSheet()
On Error GoTo ErrHandler
Dim bProtected As Boolean, oSheet As Worksheet, oTempSheet As Worksheet, sPasteLocation As String
Dim oCell As Range, oCollAddress As New Collection, oCollValue As New Collection, iCount As Integer

REM check protection status
If Not ThisWorkbook.ActiveSheet.ProtectContents Then
    Selection.PasteSpecial Paste:=xlAll
Else
    bProtected = True
    Set oSheet = ThisWorkbook.ActiveSheet
    REM save paste location
    sPasteLocation = Selection.Address
    REM unprotecting clears Clipboard in Excel!! strange but true..
    REM So paste it to a new sheet before unprotecting
    Set oTempSheet = ThisWorkbook.Worksheets.Add
    REM oSheet.Visible = xlSheetVeryHidden
    oTempSheet.Paste
    REM unprotect the sheet
    oSheet.Unprotect

    REM make a note of all locked cells
    For Each oCell In oSheet.UsedRange
        If oCell.Locked Then
            oCollAddress.Add oCell.Address
            oCollValue.Add oCell.Value
        End If
    Next

    REM paste
    oTempSheet.UsedRange.Copy
    oSheet.Activate
    oSheet.Range(sPasteLocation).Select
    REM you need to paste only values since pasting format will lock all those cells
    REM since in Excel default status is "Locked"
    Selection.PasteSpecial xlValues

    REM remove temp sheet
    Application.DisplayAlerts = False
    oTempSheet.Delete
    Application.DisplayAlerts = True

    REM restore locked cells
    For iCount = 1 To oCollAddress.Count
        Range(oCollAddress.Item(iCount)) = oCollValue.Item(iCount)
    Next
    REM restore protection
    oSheet.Protect

End If
Exit Sub

ErrHandler:
    Debug.Print Err.Description
    If bProtected Then
        ThisWorkbook.ActiveSheet.Protect
    End If
End Sub

. REM ', Stackoverflow . , .

+2

, , , . (), ( , ).

, .

0

, Paste . Office-2007 , - , Office-2007 , wnd .

Excel ( , - ), , - .

Private Sub Worksheet_Change(ByVal Target As Range)
Dim oCell As Range
For Each oCell In Target
    If oCell.Locked = True Then
        'disable events to prevent recursive function call
       Application.EnableEvents = False
       'undo the paste
       Application.Undo
       'enable events
       Application.EnableEvents = True
       Exit For
    End If
Next
End Sub

: , Excel . , , , "Locked", !! , , - , "".

, , . , , , . , . , , .

Private Sub Worksheet_Change(ByVal Target As Range)
Dim oCell As Range, oCollAddress As New Collection, oCollValue As New Collection, iCount As Integer
'get all pasted content in to a collection
For Each oCell In Target
    oCollAddress.Add oCell.Address
    oCollValue.Add oCell.Value
Next

'undo the changes done, and re-paste it for unlocked cells
'disable events to prevent infinite calls
Application.EnableEvents = False
Application.Undo
For iCount = 1 To oCollAddress.Count
    If Range(oCollAddress.Item(iCount)).Locked = False Then
        Range(oCollAddress.Item(iCount)) = oCollValue.Item(iCount)
    End If
Next
Application.EnableEvents = True
End Sub

5/27/2010: , () Excel. , .

0

, , Paste

, Excel "On-Paste" ( ), 2003 . "" 2003 ( , Sheet_Activate()):

Sub SetPasteTrap(Mode As Boolean)
' TRUE sets the trap, FALSE releases trap
    If Mode Then
        Application.CommandBars("Edit").Controls("Paste").OnAction = "TrappedPaste"
        Application.CommandBars("Edit").Controls("Paste Special...").OnAction = "TrappedPaste"
        Application.CommandBars("Cell").Controls("Paste").OnAction = "TrappedPaste"
        Application.CommandBars("Cell").Controls("Paste Special...").OnAction = "TrappedPaste"
        Application.OnKey "^v", "TrappedPaste"
    Else
        Application.CommandBars("Edit").Controls("Paste").OnAction = ""
        Application.CommandBars("Edit").Controls("Paste Special...").OnAction = ""
        Application.CommandBars("Cell").Controls("Paste").OnAction = ""
        Application.CommandBars("Cell").Controls("Paste Special...").OnAction = ""
        Application.OnKey "^v"
    End If
End Sub

, Ctrl-V - . OnAction ,

Sub TrappedPaste()
    If ActiveSheet.ProtectContents Then
        ' as long as sheet is protected, we don't paste at all
        MsgBox "Sheet is protected, all Paste/PasteSpecial functions are disabled." & vbCrLf & _
               "At your own risk you may unprotect the sheet." & vbCrLf & vbCrLf & _
               "When unprotected, you can copy/paste from other text, WORD, HTML or EXCEL files." & vbCrLf & _
               "All Paste operations will implicitly be executed as PasteSpecial/Values", _
               vbOKOnly, "Paste"
        Exit Sub
    End If

    ' silently do a PasteSpecial/Values
    On Error GoTo TryExcel
    ' try to paste text
    ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:=False
    Exit Sub
TryExcel:
    On Error GoTo DoesntWork
    Selection.PasteSpecial xlPasteValues
    Exit Sub
DoesntWork:
    MsgBox "Sorry - wrong format for pasting", vbExclamation + vbOKOnly, "PasteSpecial ..."
End Sub

, , , (excel, text, html ..)

​​ TrappedPaste() ,

1) / ( )

2)

3) ,

4) the target cell fulfills the condition of the absence of blocking, verification or similar

5) re-protect the target sheet

6) blank hidden sheet / range

Please note that with this design, the user will not be able to use the UNDO function!

Hope this helps - Good Luck MikeD

0
source

Source: https://habr.com/ru/post/1747051/

More articles:


All Articles