vba 我可以只对特定列使用 worksheet_change 吗？

Can I use worksheet_change for a specific column only?

提问by Peter Khayundi

Here is what I have. I am using a drop down list of 3 values in order to hide columns. Each value has specific columns that are unique to it and when a value is selected, I need the other columns that are not associated with it to be hidden.

这是我所拥有的。我正在使用 3 个值的下拉列表来隐藏列。Each value has specific columns that are unique to it and when a value is selected, I need the other columns that are not associated with it to be hidden.

I have used the following code:

我使用了以下代码：

Private Sub Worksheet_Change(ByVal Target As Range)
    Select Case Target.Value
    Case "Marine"
        Columns("T:X").EntireColumn.Hidden = True
        Columns("Z").EntireColumn.Hidden = True

    Case "Inland"
        Columns("S").EntireColumn.Hidden = True
        Columns("U").EntireColumn.Hidden = True

    Case Else
        Columns("T:X").EntireColumn.Hidden = False
        Columns("Z").EntireColumn.Hidden = False
        Columns("S").EntireColumn.Hidden = False
        Columns("U").EntireColumn.Hidden = False
    End Select
 End Sub

This works when I select the values from the drop down but as soon as I click on another cell in the worksheet then the hidden columns reappear. I want to be able to select a value in the drop down and for the cells to remain hidden until I select another value in the drop down. Can anyone help me with this? I have tried to use WorkSheet_SelectionChangebut this doesn't work.

当我从下拉列表中选择值时，这会起作用，但是一旦我单击工作表中的另一个单元格，隐藏的列就会重新出现。我希望能够在下拉列表中选择一个值，并使单元格保持隐藏状态，直到我在下拉列表中选择另一个值。谁能帮我这个？我试过使用，WorkSheet_SelectionChange但这不起作用。

采纳答案by Patrick Honorez

Add this (you'll need to adjust it) at the beginning of your code, to check the range that was clicked and eventually abort the sub when user clicks outside your special range.

在代码的开头添加这个（你需要调整它），以检查点击的范围并最终在用户点击你的特殊范围之外时中止子。

Dim isect As Range
Set isect = Intersect(Target, Me.Range("$a:$a"))
If isect Is Nothing Then Exit Sub

You could also check the address:

您还可以查看地址：

If Target.Range Like "$X$*" Then...

---

---

Update:
On the other hand, if the columns must be shown/hidden depending on where you click in column A, for example, then I would rather use the SelectionChange event. Here is a sample:

更新：
另一方面，如果必须根据您在 A 列中单击的位置来显示/隐藏列，那么我宁愿使用 SelectionChange 事件。这是一个示例：

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        Dim isect As Range
        Set isect = Intersect(Target, Me.Range("$a:$a"))
        If Not isect Is Nothing Then
            select case Target.Value
                .....
            end select
        End If
    End Sub

回答by Aditya Deo

Wrap the code with Target.Address.

用 Target.Address 包装代码。

For example, the Drop down is in the Cell "B2" then, the code would be as follows:

例如，下拉在单元格“B2”中，代码​​如下：

If Target.Address(True, True) = "$B" Then
    Select Case Target.Value
    Case "Marine"
        Columns("T:X").EntireColumn.Hidden = True
        Columns("Z").EntireColumn.Hidden = True

    Case "Inland"
        Columns("S").EntireColumn.Hidden = True
        Columns("U").EntireColumn.Hidden = True

    Case Else
        Columns("T:X").EntireColumn.Hidden = False
        Columns("Z").EntireColumn.Hidden = False
        Columns("S").EntireColumn.Hidden = False
        Columns("U").EntireColumn.Hidden = False
    End Select
End If

Additionally, guessing the purpose of your code, I have tweaked it further. The simplified version would look like:

此外，猜测你的代码的目的，我进一步调整了它。简化版本如下所示：

If Target.Address(True, True) = "$B" Then
    Select Case Target.Value
    Case "Marine"
        Columns("S:Z").EntireColumn.Hidden = False
        Columns("T:X").EntireColumn.Hidden = True
        Columns("Z").EntireColumn.Hidden = True
    Case "Inland"
        Columns("S:Z").EntireColumn.Hidden = False
        Columns("S").EntireColumn.Hidden = True
        Columns("U").EntireColumn.Hidden = True

    Case Else
        Columns("S:Z").EntireColumn.Hidden = False
    End Select
End If

回答by MeenakshiSundharam

Check out this code.

看看这个代码。

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Column

Case 2

    If Target.Value = "Marine" Then

        Columns("S").EntireColumn.Hidden = False
        Columns("U").EntireColumn.Hidden = False            
        Columns("T:X").EntireColumn.Hidden = True
        Columns("Z").EntireColumn.Hidden = True

    ElseIf Target.Value = "Inland" Then

        Columns("S").EntireColumn.Hidden = True
        Columns("U").EntireColumn.Hidden = True
        Columns("T:X").EntireColumn.Hidden = False
        Columns("Z").EntireColumn.Hidden = False
    Else

        Columns("T:X").EntireColumn.Hidden = False
        Columns("Z").EntireColumn.Hidden = False
        Columns("S").EntireColumn.Hidden = False
        Columns("U").EntireColumn.Hidden = False

    End If

End Select

End Sub

Now in line 4 of the code where the case is selected, give the column number of the column number in which you have the drop-downs or validations enabled in the sheet, in the given case it's 2, which represents column 'B', and Boom!

现在在选择案例的代码的第 4 行中，给出在工作表中启用下拉或验证的列号的列号，在给定的情况下它是 2，它代表列“B”，和繁荣！

I have made a little correction based on my past experiences in your code. Assuming that for the Marine entries have to be made in column 'S' and column 'U' and the Island entries are to be made in columns 'T:X' and 'Z'.

根据我过去在您的代码中的经验，我做了一些更正。假设必须在“S”列和“U”列中输入海洋条目，并在“T:X”和“Z”列中输入岛屿条目。

In your original code, if you have selected "Marine" first in a particular line item, and later corrected it to "Island", then you'll only end up having all the required columns hidden, but you would have wanted the Island columns un-hidden (to probably enter data in those fields) which I have corrected now.

在您的原始代码中，如果您首先在特定行项目中选择了“海洋”，然后将其更正为“岛”，那么您最终只会隐藏所有必需的列，但您会想要岛列未隐藏（可能在这些字段中输入数据），我现在已更正。

回答by Jay

I think the problem is your Case Elsestatement. The worksheet change event will be triggered when you go to other cells, and because the value is neither "Marine" nor "Inland," this Elsestatement gets executed and all the columns are set to Hidden = False.

我认为问题在于你的Case Else陈述。当您转到其他单元格时将触发工作表更改事件，并且由于该值既不是“Marine”也不是“Inland”，因此Else执行此语句并将所有列设置为Hidden = False。

Since you have 3 options in the drop-down, you only need to make the third Casestatement explicit instead of a catch-all.

由于下拉列表中有 3 个选项，因此您只需要Case明确第三条语句，而不是全部。