vba excel仅复制按键ctrl + c上的可见单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15784284/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
vba excel copy only visible cells on key press ctrl+c
提问by SaiKiran Mandhala
i have an excel with 75 columns and some thousands of rows of data. Out of 75 columns I am using 5 columns for my vba coding purpose. These 5 columns hold flags (either 0or 1) based on which I am locking the cells in the corresponding row (Flags are coming from Database). As user doesn't want these flag columns I just hid those columns but when ever user tries to copy data from my workbook to another workbook user is able to copy the hidden columns which client doesn't want.
我有一个包含 75 列和数千行数据的 excel。在 75 列中，我将 5 列用于我的 vba 编码目的。这 5 列包含标志（0或1），我根据这些标志锁定相应行中的单元格（标志来自 Database）。由于用户不想要这些标志列，我只是隐藏了这些列，但是当用户尝试将数据从我的工作簿复制到另一个工作簿时，用户可以复制客户端不想要的隐藏列。
So is there anyway to restrict them not to copy the hidden columns through VBAor with any setting? Actually for this issue what I thought is like on key press of Ctrl + C, I tried to change the Selection.Copyas Selection.Range.SpecialCells(xlCellTypeVisible). But I am getting some error like wrong number of arguments or invalid property assignment.
那么无论如何限制他们不通过VBA或任何设置复制隐藏列？实际上，对于这个问题，我认为在按键时的样子Ctrl + C，我尝试将Selection.Copyas更改为Selection.Range.SpecialCells(xlCellTypeVisible). 但是我收到了一些错误，例如wrong number of arguments or invalid property assignment.
The lines of code is
代码行是
Private Sub Workbook_Open()
Application.OnKey "^c", "Copy"
End Sub
Sub Copy()
If Selection Is Nothing Then
Else
Selection.Copy = Selection.Range.SpecialCells(xlCellTypeVisible)
End If
End Sub
Any ideas to restrict users not to copy the hidden columns. Any help would be appreciated greatly.
任何限制用户不复制隐藏列的想法。任何帮助将不胜感激。
回答by Siddharth Rout
Try this
尝试这个
Sub Copy()
Dim rng As Range
On Error GoTo Whoa
If Not Selection Is Nothing Then
Set rng = Selection.Cells.SpecialCells(xlCellTypeVisible)
rng.Copy
End If
LetsContinue:
Exit Sub
Whoa:
MsgBox Err.Description, vbCritical, "Error Number : " & Err.Number
Resume LetsContinue
End Sub
Note: I have used Error Handling which is a must because the user might select non contiguous ranges and the code will break if the error handling is not done :) See Screenshot below
注意：我使用了错误处理，这是必须的，因为用户可能会选择不连续的范围，如果错误处理没有完成，代码将中断:) 请参阅下面的屏幕截图
