是否可以创建一个宏，该宏将创建一个超链接，该宏将运行一个宏 [英] Is it possible to create a macro, that will create a hyperlink, that will run a macro

问题描述

我只是想知道是否可以创建一个宏，该宏将为单元格提供超链接并为其分配单独的宏?

I'm just wondering if you can create a Macro, that will hyperlink a cell and assign a separate macro to it?

我知道您可以使用超链接通过以下命令运行宏

I know you can use a hyperlink to run a macro using the following

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
       Call Macro Name
End Sub

我还知道您可以使用宏使用以下内容创建超链接

I also know you can use a macro to create a Hyperlink using the following

With Worksheets(1) 
 .Hyperlinks.Add Anchor:=.Range("a5"), _ 
 Address:="", _ 
 ScreenTip:="", _ 
 TextToDisplay:= ""
End With 

有没有把它们放在一起?还是有一种方法可以使用第二个代码来分配宏?而不是地址?

Is there anyway to put these together? Or is there a way to use the second code to assign a macro? instead of an address?

因此，如果我创建了一个宏，可以从原始数据标签中获取数据，并将其放入这样的表中

So if I made a macro that takes data from a raw data tab, and puts it into a table like this

然后，宏需要为值(4,2,5,2,5)分配一个超链接，该值将在单击时运行一个单独的宏.我希望它看起来像这样

The macro then needs to assign a hyperlink to the values (4,2,5,2,5) that will run a separate macro when clicked. I'm hoping it would look something like this

With Worksheets(1) 
 .Hyperlinks.Add Anchor:=.Range("B2"), _ 
 Address:=Macro Name, _ 
 ScreenTip:="", _ 
 TextToDisplay:= ""
End With 

由于表可能具有更多的行，因此还有更多的内容，因此超链接必须具有可变的范围等，但是我只是想知道是否有可能，因为我不能像上面那样工作

There is much more to it as the table may have more rows so the hyperlinks will have to have a variable range etc, but i'd just to know if it's possible because I can't get it to work as above

推荐答案

可以，但默认情况下不可以.您将执行某种解决方法:

It is possible, but not by default. You will to perform some kind of workaround:

在例如单元格B2上创建链接到B2本身的超链接.

Create a hyperlink on eg cell B2 linking to B2 itself.

.Hyperlinks.Add Anchor:=Range("B2"), Address:="", SubAddress:=Range("B2").Address

然后使用Target决定应调用哪个宏，因此可以使用地址Target.Parent.Address或单元格值Target.Parent.Value来区分宏.

And then use the Target to decide which macro should be called, so you can use the address Target.Parent.Address or the cell value Target.Parent.Value to distinguish between the macros.

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    'decide by address
    Select Case Target.Parent.Address
        Case "$B$2"
            CallMacro1
        Case "$B$3"
            CallMacro1
    End Select

    'or by intersection a range
    Select Case True
        Case Not Intersect(Target.Parent, Range("B4:B8")) Is Nothing
            CallMacro1
    End Select


    'or by value
    Select Case Target.Parent.Value
        Case 2
            CallMacro1
        Case 4
            CallMacro1
    End Select
End Sub

请注意，不建议使用Call语句，并且不应再使用它.只需使用宏名称本身来调用宏:代替Call MacroName而是使用MacroName.

Note that the Call statement is deprecated and should not be used anymore. Just use the macro name itself to call a macro: Instead of Call MacroName just use MacroName.

或者使用ScreenTip作为解决方法来设置宏名称

Alternatively use the ScreenTip as workaround to set the macro name

.Hyperlinks.Add Anchor:=Range("B4"), Address:="", SubAddress:= _
    Range("B4").Address, ScreenTip:="MyMacro"

，然后使用ScreenTip运行该宏

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    Application.Run Target.ScreenTip
End Sub

请注意，MyMacro必须在模块中公开，否则您需要使用例如. Sheet1.MyMacro告诉宏实际在哪个范围内.否则，将引发错误，指出宏名称不存在.

Note that the MyMacro has to be public in a Module otherwise you need to use eg. Sheet1.MyMacro to tell in which scope the macro actually is. Otherwise it will throw an error that the macro name does not exist.

还请注意，这是一种变通方法，会误用ScreenTip.

Note also that this is a kind of workaround, that miss-uses the ScreenTip.

这篇关于是否可以创建一个宏，该宏将创建一个超链接，该宏将运行一个宏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！