我只能将worksheet_change用于特定的列吗? [英] Can I use worksheet_change for a specific column only?

问题描述

这就是我所拥有的.我正在使用3个值的下拉列表以隐藏列.每个值具有所特有的，并当选择一个值，我需要未关联与它被隐藏在其他列中特定列.

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.

我使用了以下代码:

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

当我从下拉列表中选择值时，此方法有效，但是当我单击工作表中的另一个单元格时，隐藏的列就会重新出现.我希望能够在下拉列表中选择一个值，并使单元格保持隐藏状态，直到我在下拉列表中选择另一个值为止.谁能帮我这个?我尝试使用 WorkSheet_SelectionChange ，但这不起作用.

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_SelectionChange but this doesn't work.

推荐答案

在代码的开头添加此代码(您需要对其进行调整)，以检查单击的范围，并在用户单击时最终中止子项超出您的特殊范围.

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$8:$a$48"))
If isect Is Nothing Then Exit Sub

您还可以检查地址:

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

---

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

---

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:

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

这篇关于我只能将worksheet_change用于特定的列吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！