如何根据列表选择跳到特定的单元格

问题描述

我在R5单元格中创建了一个包含名称的下拉列表，我们将其命名为Name1 Name2 Name3.我想当用户选择一个特定的名称时，工作表将向下滚动到特定的行.例如，如果选择了名称1，我希望它转到第2行，如果选择了名称2，则选择10行，并且选择名称3的行18.该列表与我要滚动到的数据在同一工作表上.我可以使用一些代码来做到这一点吗?

I have created a drop-down list in cell R5 containing names, lets call them Name1 Name2 Name3. I'd like when the user selects a certain name the sheet will scroll down to a specific row. For instance, if Name 1 is selected I'd like it to go to row 2, if Name2 is selected row 10, and Name3 row 18. The list is on the same worksheet as the data I'm wanting to scroll to. Is there some code I can use to do this?

推荐答案

您将需要使用Sheet Events来处理此问题.像这样:

You would need to use Sheet Events to handle this. Something like this:

在具有您输入范围的工作表的工作表模块中，输入以下代码

In your Worksheet Module of the worksheet that has your input range, put this code

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim InputRange As Excel.Range
    Set InputRange = Me.Range("R5")

    '// Check if the change is happening in your dropdown cell
    If Not Intersect(Target, InputRange) Is Nothing Then
        Select Case InputRange.Value
            Case "Name1"
                Application.ActiveWindow.ScrollRow = 2
            Case "Name2"
                Application.ActiveWindow.ScrollRow = 10
            Case "Name3"
                Application.ActiveWindow.ScrollRow = 18
            Case Else
                '//...
        End Select
    End If
End Sub

如果您无法正常使用它.尝试通过单击代码左侧的区域来添加断点.当代码流到达该断点时，该断点将停止执行.这是弄清楚Excel是否甚至尝试运行此代码块的一种方法.

If you're having trouble getting this to work. Try adding a breakpoint by clicking in the area to the left of the code. A breakpoint will halt execution when the flow of code reaches that point. This is one way to figure out if Excel is even TRYING to run this block of code.

调试Excel代码

这篇关于如何根据列表选择跳到特定的单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！