VBA Excel仅在按键Ctrl + C上复制可见的单元格 [英] vba excel copy only visible cells on key press ctrl+c

问题描述

我有一个具有75列和数千行数据的Excel.在75列中，我将5列用于我的vba编码目的.这5列保留标志( 0 或 1 )，基于这些标志我将相应行中的单元格锁定(标记来自数据库).由于用户不希望使用这些标志列，因此我只是隐藏了这些列，但是无论何时用户尝试将数据从我的工作簿复制到另一个工作簿，用户都可以复制客户端不需要的隐藏列.

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 0 or 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.

因此，是否存在限制它们不要通过 VBA 或任何设置复制隐藏列的限制?实际上，对于这个问题，我想在按 Ctrl + C 时会感觉像是，我试图将 Selection.Copy 更改为 Selection.Range.SpecialCells(xlCellTypeVisible).但是我遇到了一些错误，例如错误的参数数量或无效的属性分配.

So is there anyway to restrict them not to copy the hidden columns through VBA or with any setting? Actually for this issue what I thought is like on key press of Ctrl + C, I tried to change the Selection.Copy as Selection.Range.SpecialCells(xlCellTypeVisible). But I am getting some error like wrong number of arguments or invalid property assignment.

代码行是

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.

推荐答案

尝试一下

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

这篇关于VBA Excel仅在按键Ctrl + C上复制可见的单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！