Excelのハイパーリンクにマクロを割り当てる方法

質問

ハイパーリンクの挿入で設定した、ハイパーリンクをクリックしてコードを実行する方法が分かりません。1つのシートで複数のハイパーリンクに、異なるコードを設定するにはどのようにすればよいのですか。ご回答宜しくお願い致します。

回答No.3

cj_mover · ベストアンサー

＃１-２、ｃｊです。＃２お礼欄を読みました。

> シート上に設定した文字列を誤って変更してしまわないように注意しようと思います。
ハイパーリンクの表示文字列　≒　セルの値
が、仮に変更されてしまったとしても
　　Case "Hoge"
　　　　Call Hoge
の "Hoge" など、指定した文字列とマッチしなければ、
期待するマクロが動作しない、という結果なので、比較的軽い問題で済みますが、
偶然、他のマクロを実行させるキーワードにマッチしてしまうと、
全く期待しないマクロが実行されてしまい、こちらの場合は重篤なトラブルになりかねません。

場合によっては、Excel一般機能の、データの入力規則、等が防衛策に役立つかも知れません。
例えば、文字列値でハイパーリンクを設定した後に、
データの入力規則を新たに設定し、数値（最小値～最大値）を指定しておく、
（勝手に文字列値を設定出来なくなる）とか。
（データの入力規則を設定したセルをコピーし、他のセルの書式のみ貼り付けることで
手数を減らして設定できます）
これに因んだ情報として、以下。

ハイパーリンクの表示文字列　はあくまでも文字列ですから、
仮に、数値を設定したセルの値を拾ってマッチングするには
　　Select Case Target.Range.Value
と書き換えてやれば可能です。
次のサンプル7では、
セル値が、
　　数値　　123
　　数字文字列値　　"123"
　　プレフィクス付の数字文字列　　"'123"
３つの場合にマッチします。
' ' 〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
　　Select Case Target.Range.Value
　　Case "Hoge"
　　　　Call Hoge
　　Case "Piyo"
　　　　Call Piyo
　　Case "Fuga"
　　　　Call Fuga
　　Case "123"
　　　　Call Hogera
　　End Select
　　Application.EnableEvents = False
　　Application.Goto
　　Application.EnableEvents = True
End Sub
' ' 〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓
または
　　Select Case Target.TextToDisplay
と書き換えると
　　数字文字列値　　"123"
　　プレフィクス付の数字文字列　　"'123"
にだけマッチして、数値には反応しません。
' ' 〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
　　Select Case Target.TextToDisplay
　　Case "Hoge"
　　　　Call Hoge
　　Case "Piyo"
　　　　Call Piyo
　　Case "Fuga"
　　　　Call Fuga
　　Case "123"
　　　　Call Hogera
　　End Select
　　Application.EnableEvents = False
　　Application.Goto
　　Application.EnableEvents = True
End Sub
' ' 〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓
正確には、セルの値に数値を設定すると、
ハイパーリンクの表示文字列の実態は空の文字列になる、ということです。

こういった特性を利用した設計を考えるなどすれば、
何かの役に立つかも知れませんね。

お礼

有難うございます。
数値、数字文字列値、プレフィクス付の数字文字に反応せせるようにしておけば、ハイパーリンクしようとする文字全体をコードに入力する必要はないのですね。
その方法を使い、作ってみようと思います。
ご親切に回答頂きありがとうございます。

回答No.2

cj_mover

＃１、ｃｊです。＃１補足欄へのレスです。

> もう1点分からないことは、そのハイパーリンクは、Case "C3"とあるように、セルに設定するものなのですか。
> 例えば、ハイパーリンクを設定した後、並び替えをしてセル番地が異なることとなった場合は、Case "C3"のC3部分のの変更が必要なのですか。
＃１で示した例では仰る通りです。

ハイパーリンクと実行したいマクロとを、セルの移動とは無縁に
常に関連付けを維持したいということでしたらば、
ハイパーリンクの表示文字列で関連付けするのが比較的簡単です。

例えば、
予め、ハイパーリンクの表示文字列を実行したいマクロ名にしておけば、
以下のように書くことが出来て、
セル番地変更の影響を受けないようになります。

' ' 〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓
Private Sub Worksheet__FollowHyperlink(ByVal Target As Hyperlink)
　　Select Case Target.Name
　　Case "Hoge"
　　　　Call Hoge
　　Case "Piyo"
　　　　Call Piyo
　　Case "Fuga"
　　　　Call Fuga
　　End Select
End Sub
' ' 〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓
もう少しシンプルに書くと
' ' 〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
　　Select Case Target.Name
　　Case "Hoge", "Piyo", "Fuga"
　　　　Application.Run (Target.Name)
　　End Select
End Sub
' ' 〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓
これでも同じ結果になります。

ただ、ハイパーリンクのジャンプ先をどうするか、という問題は残ります。
（どこに飛んで行くか解らなくなるので）
ひとつの方法としてハイパーリンクの参照先はすべて、
当該シートのA1セル等に統一しておいて、
ジャンプした後に、元のハイパーリンクが設定されたセルに戻してやるような
使い方もあります。
その場合は、
　　Application.EnableEvents = False
　　Application.Goto
　　Application.EnableEvents = True
この３行を
End Sub
行の直前に挿入してください。

また、
ハイパーリンクの表示文字列　と、実行するマクロ　との
関連付けさえ書いておけばよいので、
　　"Alpha" と表示された ハイパーリンクがクリックされたら、
　　　　マクロ： Hoge を
　　"Bravo" と表示された ハイパーリンクがクリックされたら、
　　　　マクロ： Piyo を
　　"Charlie" と表示された ハイパーリンクがクリックされたら、
　　　　マクロ： Fuga を
という対応関係を明確にしておけば、
自在に応用できることと思います。
' ' 〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓
Private Sub Worksheet__FollowHyperlink(ByVal Target As Hyperlink)
　　Select Case Target.Name
　　Case "Alpha"
　　　　Call Hoge
　　Case "Bravo"
　　　　Call Piyo
　　Case "Charlie"
　　　　Call Fuga
　　End Select
End Sub
' ' 〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓

ハイパーリンクの表示文字列　について
気を付けて欲しいのは、
シート上で容易に変更可能なことや、
　　Case "Alpha"
　　Case "alpha"
　　Case "Ａｌｐｈａ"
のように大文字小文字、全角半角、を区別する、ということと、
数字のみで指定してしまうと、数字文字列なのか数値なのかの区別が複雑になってしまうことです。

紹介した、ハイパーリンクの表示文字列　の例の他に、
ハイパーリンクの参照先アドレスで振り分けることなど、
ここには書きませんが、他にも方法はあります。

念の為、
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
から
End Sub
までで一連のイベントプロシージャですが、
ひとつのシートモジュールに同じ目的同じ名前のプロシージャを重複して設定することはできません。

ハイパーリンク自体は簡単に編集出来てしまうものですから、
セル番地に限らず、ＶＢＡが前提にしている条件が崩される場面は、
容易に想像できます。
なので、他者による利用を想定する場合は、シートの保護なども検討に入れるか、
ハイパーリンク以外のオベジェクト（ボタンコントロールや各種シェープなど）などと
うまく使い分け出来ると好いと思います。

補足欄へのレスは以上です。

お礼

よく分かりました。
実際に考えているのは、並び替えを頻繁に行い、コードを設定する文字列の長さも違います。
Case "Alpha"
　　　　Call Hoge
のように、例えばその文字列をクリックするとマクロ名"a1"、"a2"、"a3"を実行するというように、マクロの名前とシート上の文字列をを別々に設定する方法があるのですね。

　Application.EnableEvents = False
　　Application.Goto
　　Application.EnableEvents = True
も試してみでうまく出来ました。

シート上に設定した文字列を誤って変更してしまわないように注意しようと思います。

マクロ名を記号や、数値を使うことで、今後の整理がしやすいように思います。
この方法を元にして、実際に作ってみようと思います。
丁寧なご回答ありがとうございます。

回答No.1

cj_mover

シートモジュールで_FollowHyperlink イベントを使います。

シートモジュールに以下を貼り付けます。
' ' 〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
　　Select Case Target.Range.Address(0, 0)
　　Case "C3"
　　　　Call Hoge
　　Case "D4"
　　　　Call Piyo
　　Case "E5"
　　　　Call fuga
　　End Select
End Sub

' ' 〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓〓

以上の例では、
　C3 の ハイパーリンクがクリックされたら、マクロ： Hoge を
　D4 の ハイパーリンクがクリックされたら、マクロ： Piyo を
　E5 の ハイパーリンクがクリックされたら、マクロ： fuga を
実行します。

パターンとして
　　Case "C3"
　　　　Call Hoge
の２行で一組、
　"C3"　　…　　セル範囲
　Hoge　　…　　マクロ名
の部分を、そちらでの必要に合わせて。編集します。
この２行一組のパターンを適宜増やすことで、
> 1つのシートで複数のハイパーリンクに、異なるコードを設定する
ことが可能です。

なお、呼び出すマクロは標準モジュールに登録してあるものとします。
無論、
　　　　Call Hoge
のようにマクロを呼び出す代わりに、直接、コードを記入しても構いません。

ハイパーリンクの登録は
[このドキュメント内]
[セル参照を入力してください]と書かれたボックスに
ハイパーリンクを設定するセルのアドレス（C3 など）を指定して
[ＯＫ]
自セルにジャンプするように設定し、
見かけ上は何もしないように設定しておくのがベターです。

補足

ご回答ありがとうございます。
複数のハイパーリンクに異なるコードを割り当てることが出来ました。
FollowHyperlink イベントで1つのシートの複数のハイパーリンクをすべて設定すればよいのですね。
もう1点分からないことは、そのハイパーリンクは、Case "C3"とあるように、セルに設定するものなのですか。
例えば、ハイパーリンクを設定した後、並び替えをしてセル番地が異なることとなった場合は、Case "C3"のC3部分のの変更が必要なのですか。