Excelにメアドや別シートへのセル指定リンクを相対パスで設定VBA/マクロ便利Tips

業務効率化に役立つVBA/マクロのさまざまなTipsをコード例を交えて紹介していきます。今回は、HyperlinksのEmailSubjectプロパティを使い、リンクにメールアドレスを設定しメールの件名を指定する方法と、リンクで任意のシートにジャンプする方法について。

» 2014年07月07日 18時00分 公開
[薬師寺国安PROJECT KySS]
「VBA/マクロ便利Tips」のインデックス

連載目次

※本Tipsの環境:Windows 8.1 Enterprize(64ビット)+Excel 2013


 今回も、Tips「Excelのセルにハイパーリンクを設定し、一括削除する」に引き続きハイパーリンク関連のTipsを紹介する。今回はメールアドレスの管理と、任意のセルにジャンプさせるTipsだ。

ハイパーリンクにメールアドレスを設定してメールの件名を指定

 まずは、ハイパーリンクにメールアドレスを設定してメールの件名を指定するTipsを紹介しよう。

 図1では、Tips「Excelのセルにハイパーリンクを設定し、一括削除する」で紹介した、Webサイトを管理するのと同じようなUIと処理を用いている。

図1 「得意先名」「メールアドレス」「OK」ボタンが表示されたシートと「得意先メールアドレス」と表示された「得意先メールアドレス」シートが用意されている

 「得意先名」「メールアドレス」を入力するセルを用意し、「角丸四角形」を配置して「OK」ボタンとしている。

 ハイパーリンクにメールアドレスを設定する書式は下記の通りだ。Tips「Excelのセルにハイパーリンクを設定し、一括削除する」の「HyperlinksオブジェクトのAddメソッドの書式」と同じ書式だが、Addressに指定する内容が異なるので、再掲載しておく。

HyperlinksオブジェクトのAddメソッドの書式

Hyperlinks.Add Anchor:={Anchor},Address:={Address},TextToDisplay:={TextToDisplay}


 {Anchor}にはハイパーリンクのアンカーを指定するが、今回はセル(Rangeオブジェクト)やShapeオブジェクトを指定することになる。必須項目だ。{Address}にはメールアドレスの先頭に「mailto:」を付けて指定する。これも必須項目だ。

 {TextToDisplay}には、ハイパーリンクで表示されるテキストを指定する。これは、省略可能だ。

 今回の場合は、登録したメールアドレスをクリックするとメーラーが起動し、自動的にメールの「件名」を表示するようにしている。メールに「件名」を表示する書式は下記だ。

HyperlinksオブジェクトのEmailSubjectプロパティの書式

Hyperlinks.EmailSubject


 これら2つの書式を活用するのが、下記リスト1のコードだ。

Option Explicit
Sub 得意先メールアドレス()
  If Range("C2").Value = "" Or Range("C3").Value = "" Then
    MsgBox "「得意先名」と「メールアドレス]を入力してください。"
    Exit Sub
  Else
    Dim lastRow As Long
    With Worksheets("得意先メールアドレス")
      lastRow = .Cells(Rows.Count, 1).End(xlUp).Row + 1
      .Cells(lastRow, 1).Hyperlinks.Add Anchor:=.Cells(lastRow, 1), _
                                    Address:="mailto:" & Range("C3").Value, _
                                    TextToDisplay:=Range("C2").Value
      .Cells(lastRow, 1).Hyperlinks(1).EmailSubject = "ご依頼の件について"
    End With
  End If
  Worksheets("得意先メールアドレス").Select
End Sub
リスト1 複数の「コメント」内を検索するコード

 Tips「Excelのセルにハイパーリンクを設定し、一括削除する」とほとんど同じなので、異なる部分のみ解説する。

 {Anchor}には「C3」セルに入力された値の先頭に「mailto:」を連結して指定する。{TextToDisplay}には「C2」のセルに入力された値を指定している。

 VBE(Visual Basic Editor)を起動してModule1内にリスト1のコードを記述し、このマクロを「OK」ボタンに関連付ける。

 これで、「得意先名」「メールアドレス」を入力して「OK」ボタンをクリックすると、「得意先メールアドレス」シートに、「得意先名」に「メールアドレス」が関連付けされて順次登録されていく。

 「OK」ボタンをクリックすると、図2のように表示される

図2 得意先名」をクリックすると「ご依頼の件について」という「件名」が付いてメーラーが起動した

 さらに、登録されたメールアドレスをクリックすると、サブジェクトに「ご依頼の件について」という「件名」が付いてメーラーが起動する。

ハイパーリンクで任意のシートにジャンプ

 次は、「ハイパーリンクで任意のセルにジャンプするTipsを紹介しよう。

 「シート表示」というシートを作成し、図3のように「シート名表示」ボタンを配置しておく。

図3 「シート名表示」ボタンと「シート名」が追加表示されるセルが用意されている

 このボタンをクリックすると、「1月売上」から「12月売上」の項目名に、「1月売上」から「12月売上」までのシートの「A1」のセルにハイパーリンクが張られて表示される。このリンクをクリックすると、各指定したシートにジャンプする。月別売り上げ管理などに利用すると便利なTipsだと思う。

 書式は下記のようになる。

HyperlinksオブジェクトのAddメソッドの書式(引数にSubAddress付き)

Hyperlinks.Add Anchor:={Anchor},Address:={Address},SubAddress:={SubAddress},TextToDisplay:={TextToDisplay}


 {Anchor}にはハイパーリンクのアンカーを指定する。セル(Rangeオブジェクト)やShapeオブジェクトを指定する。必須項目だ。{Address}には今回は何も指定しない。何も指定しないからといって省略することはできない。必須項目だからだ。

 {SubAddress}にはハイパーリンクのサブアドレスを指定する。これは、省略可能だ。TextToDisplayには、ハイパーリンクで表示されるテキストを指定する。これも、省略可能だ。

 この書式を活用し、図3の「シート名表示」ボタンをクリックし、表示される「シート名」をクリックして任意のシートのA1のセルにジャンプするコードはリスト2になる。

Sub 任意のシートにジャンプ()
  Dim lastRow As Long
  Dim i As Integer
  For i = 1 To 12
    With ActiveSheet
      lastRow = .Cells(Rows.Count, 4).End(xlUp).Row + 1
      .Cells(lastRow, 4).Hyperlinks.Add Anchor:=.Cells(lastRow, 4), _
                                    Address:="", _
                                    SubAddress:=i & "月売上!A1", _
                                    TextToDisplay:=i & "月売上"
    End With
  Next
End Sub
リスト2 シート名の一覧を表示して、任意のシートにジャンプするコード

 まずは、Long型の変数lastRowとInteger型変数iを宣言する。

 「1月売上」から「12月売上」のシート名を表示させるために、変数iを12回繰り返す。繰り返し処理の間で下記の処理を行う。

 記事「Excelの基本オブジェクト「セル」の参照、移動、コピー、選択、挿入、削除」でも解説した、終端セルを参照するEndプロパティを使用して、アクティブシートの4列目(D列)の最終行から上方向の終端セル(「D列」の最終行)を選択し、データが入っている場合は、次の行にデータが追加されるようにする。

 Hyperlinks.Addメソッドを使って、「D列」のセルに順次データが追加されるよう、{Anchor}に「.Cells(lastRow,4)」と指定する。こうすることで、変数lastRow行の4列目のセル(D列)から順次データが追加されていく。

 {Address}には何も指定しないでおく。{SubAddress}には、12回繰り返す変数iの値に「月売上!A1」という文字列を指定して、表示された「サイト名」がクリックされたとき、任意のサイト名の「A1」のセルにジャンプするように指定する。例えば「2月売上!A1」は「2月売上」シートの「A1」にジャンプする意味を表す。

 {TextToDisplay}には、12回繰り返す変数iの値に「月売上」という文字列を連結して指定する。

 これで、「シート名表示」ボタンがクリックされると、アクティブセルのD列に「1月売上」から「12月売上」の文字列にジャンプ先のハイパーリンクが設定されて表示される。

 このマクロを「シート名表示」ボタンに関連付け、実行すると図4のように表示される。

図4 表示されたシート名をクリックして、任意のシートのA1セルにジャンプした

まとめ

 今回は、お得意先のメールアドレスをハイパーリンクで管理するTipsを最初に紹介した。このように、ちょっと工夫するだけで、いちいちExcelのメニューから、ハイパーリンクを設定してメールの設定を行うより、数倍手間が省けるのではないだろうか。

 また後半では、シート名を一気に表示させて、任意のシートにジャンプするTipsを紹介した。わずか、これだけのマクロを記述するだけで、日常の作業の手間は大いに省けるのではないだろうか。

 ぜひ、今回のTipsも各自の職場でアレンジして使ってもらえるとうれしい。

著者プロフィール

薬師寺 国安(やくしじ くにやす) / 薬師寺国安事務所

薬師寺国安事務所代表。Visual Basicプログラミングと、マイクロソフト系の技術をテーマとした、書籍や記事の執筆を行う。

1950年生まれ。事務系のサラリーマンだった40歳から趣味でプログラミングを始め、1996年より独学でActiveXに取り組む。

1997年に薬師寺聖とコラボレーション・ユニット「PROJECT KySS」を結成。

2003年よりフリーになり、PROJECT KySSの活動に本格的に参加。.NETやRIAに関する書籍や記事を多数執筆する傍ら、受託案件のプログラミングも手掛ける。

Windows Phoneアプリ開発を経て、現在はWindowsストアアプリを多数公開中。

Microsoft MVP for Development Platforms - Client App Dev(Oct 2003-Sep 2012)。

Microsoft MVP for Development Platforms - Windows Phone Development(Oct 2012-Sep 2013)。

Microsoft MVP for Development Platforms - Client Development(Oct 2013-Sep 2014)。


Copyright © ITmedia, Inc. All Rights Reserved.

RSSについて

アイティメディアIDについて

メールマガジン登録

@ITのメールマガジンは、 もちろん、すべて無料です。ぜひメールマガジンをご購読ください。