vba VBA获取单击超链接的单元格的值

VBA getting the value of the cell from which a hyper link was clicked

提问by DROP TABLE users

I am trying to send the user to a certian sheet in the workbook based on the value of the hyperlink they click on. "1.01" Is example text in one of the cells that are hyperlinks. I have the event getting triggered when they click with this code, but every time the BRis 0.

我正在尝试根据用户单击的超链接的值将用户发送到工作簿中的某个特定工作表。“1.01”是作为超链接的单元格之一中的示例文本。当他们使用此代码单击时，我会触发事件，但每次BR都是 0。

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    Dim BR As Integer
    BR = ActiveCell.Value
    Select Case BR
    Case Is < 2  'case always gets run because BR is always 0
        Worksheets("Appts").Select
    Case 2 To 3
        Worksheets("Next").Select
    Case 3 To 4
        Worksheets("Calls").Select
    End Select
End Sub

I think my problem is where I am getting the ActiveCell.Valuebut I haven't been able to figure it out. Any help is appreciated.

我认为我的问题是我在哪里得到，ActiveCell.Value但我一直无法弄清楚。任何帮助表示赞赏。

Thanks,

谢谢，

回答by Siddharth Rout

Is this what you are trying?

这是你正在尝试的吗？

Activecellwill always give you the value of the cell that is active. If you want the value of the cell which has the hyperlink then use this.

Activecell将始终为您提供活动单元格的值。如果您想要具有超链接的单元格的值，请使用它。

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    Dim BR As Integer

    BR = Target.Range.Value

    Select Case BR
        Case Is < 2: Worksheets("Appts").Select
        Case 2 To 3: Worksheets("Next").Select
        Case 3 To 4: Worksheets("Calls").Select
    End Select
End Sub

EDIT

编辑

Since there is lot of comment around using the correct data type. Here is an added info. From your select case it is evident that you are only concerned with values from 1 to 4. In such a case declaring your variable as Integer is just fine. If you are not sure of the user input then introduce a small error handling because then declaring the variable as Integer or Long or Single wouldn't help. For example

因为有很多关于使用正确数据类型的评论。这是一个补充信息。从您的选择情况来看，很明显您只关心 1 到 4 之间的值。在这种情况下，将您的变量声明为 Integer 就好了。如果您不确定用户输入，则引入一个小错误处理，因为然后将变量声明为 Integer 或 Long 或 Single 将无济于事。例如

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    Dim BR As Integer

    On Error GoTo Whoa

    BR = Target.Range.Value

    Select Case BR
        Case Is < 2: Worksheets("Appts").Select
        Case 2 To 3: Worksheets("Next").Select
        Case 3 To 4: Worksheets("Calls").Select
    End Select

    Exit Sub
Whoa:
    MsgBox "The value of the cell which has the hyperlink doesn't fall in the range 1 to 4"
End Sub

SNAPSHOT

快照

回答by Jeremy Thompson

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    Dim BR As Integer
    BR = ActiveCell.Formula
    'or
    BR = ActiveCell.Value2
    Select Case BR
    Case Is < 2  'case always gets run because BR is always 0
        Worksheets("Appts").Select
    Case 2 To 3
        Worksheets("Next").Select
    Case 3 To 4
        Worksheets("Calls").Select
    End Select
End Sub

回答by Jon Crowell

There are two things you need to fix.

您需要解决两件事。

1. Get the value from the hyperlink

1.从超链接中获取值

In order to get the value from activecell.value, your hyperlinks need to be linking to themselves. (Right-click a hyperlink and select edit hyperlink, select Place in This Document, and type the cell's address in the cell reference.)

为了从 中获取值activecell.value，您的超链接需要链接到它们自己。（右键单击超链接并选择edit hyperlink、选择Place in This Document并在单元格引用中键入单元格的地址。）

A better option is to do what Siddharth suggests.

更好的选择是按照 Siddharth 的建议去做。

2. Make sure you select the sheet you want based on the value of the hyperlink

2.确保根据超链接的值选择所需的工作表

You also need to fix your case statement. If BR is an integer, any value between 3 and 3.5 will evaluate to 3 and select the "Next" sheet instead of the "Calls" sheet.

你还需要修正你的case语句。如果 BR 是整数，则 3 到 3.5 之间的任何值都将计算为 3 并选择“下一个”表而不是“调用”表。

回答by mkingston

The value of your cell is probably stored as text rather than a numeric value. Select the cell, press ctrl+1 then change the type to general, or number. Press OK, then push F2 to change the value in the cell, followed by Enter. The data type is now numeric. Run your code again.

单元格的值可能存储为文本而不是数字值。选择单元格，按 ctrl+1，然后将类型更改为常规或数字。按 OK，然后按 F2 更改单元格中的值，然后按 Enter。数据类型现在是数字。再次运行您的代码。

Alternatively, instead of this:

或者，而不是这样：

BR = ActiveCell.Value

Use this:

用这个：

BR = CDbl(ActiveCell.Value)