この記事では、ハイパーリンクを一括で設定・解除する方法についてご説明します。
【動画】ハイパーリンクを一括で設定・解除する実際の動き
本題に入る前に、まずは次の動画をご覧ください。
「http」の文字列を特定し、特定出来たらHyperlinks.Addメソッドを使いハイパーリンクの設定を行っています。
また、ClearHyperlinksメソッドを使ってハイパーリンクの解除を行っています。
ただし、ClearHyperlinksメソッドを使うとハイパーリンクの解除だけしか行わなれず、フォントの書式はハイパーリンクが設定されていた時と変わりません。
なので、セルのアンダーラインの設定とフォントの色を元に戻しています。
今回ハイパーリンクを一括で解除するのにHyperlinks.deleteメソッドは使わずClearHyperlinksメソッドを使用
今回ハイパーリンクを一括で解除するのにHyperlinks.deleteメソッドは使わずClearHyperlinksメソッドを使用しています。
Hyperlinks.deleteメソッドでもハイパーリンクを解除することができますが、なぜHyperlinks.deleteメソッドを使う理由は、Hyperlinks.deleteメソッドだと下の画像の通りに罫線を消してしまうからです。
一方、ClearHyperlinksメソッドなら罫線を消してしまうことがないので、今回はClearHyperlinksメソッドを使うことにしました。
マクロ作成の流れ
①ハイパーリンクの一括設定
②ハイパーリンクの一括解除
Excelファイルの例
C列とG列に「http」の文字列が含まれるURLが入力されています。
このURLに対してハイパーリンクの一括設定・解除を行います。
コードの例
Excelのマクロのコード(例)
Option Explicit
Private Sub btn_hprLnkSetOnOff_Click()
Dim Rng As Range, rngWk As Range 'Rangeオブジェクト格納用変数
'セルの範囲を取得する
Set Rng = Worksheets("Top").Range("A1:H18").SpecialCells(xlCellTypeConstants, xlTextValues)
If btn_hprLnkSetOnOff.Caption = "ハイパーリンクの設定" Then
For Each rngWk In Rng
If InStr(rngWk.Text, "http") > 0 Then
'URL(httpの文字列)が含まれるセルの場合
'ハイパーリンクを設定する
Worksheets("Top").Hyperlinks.Add _
Anchor:=Cells(rngWk.Row, rngWk.Column), _
Address:=rngWk.Text, _
ScreenTip:=rngWk.Offset(0, -1).Text, _
TextToDisplay:=rngWk.Text
End If
Next rngWk
'ボタンのCaptionの文言を設定する(「ハイパーリンクの解除」)
btn_hprLnkSetOnOff.Caption = "ハイパーリンクの解除"
ElseIf btn_hprLnkSetOnOff.Caption = "ハイパーリンクの解除" Then
For Each rngWk In Rng
If InStr(rngWk.Text, "http") > 0 Then
With rngWk
'ハイパーリンクを解除する
.ClearHyperlinks
'セルのアンダーラインの設定を解除する
.Font.Underline = xlUnderlineStyleNone
'フォントの塗りつぶしの色を「色なし」に設定する
'(※フォントの塗りつぶしの色設定はお使いの環境にあわせてください)
.Font.ColorIndex = xlAutomatic
End With
End If
Next rngWk
'ボタンのCaptionの文言を設定する(「ハイパーリンクの設定」)
btn_hprLnkSetOnOff.Caption = "ハイパーリンクの設定"
End If
End Sub
コードの解説
コードの解説を、「ハイパーリンクの一括設定」と「ハイパーリンクの一括解除」の2つに分けて説明します。
①ハイパーリンクの一括設定
注目すべきコード①
最初に見て頂きたいのは14行目です。
If InStr(rngWk.Text, "http") > 0 Then
この14行目のIf文で、セルに入った文字列がURLなのかを判定します。
「http」の文字列が含まれるかどうかはInstr関数を使います。
注目すべきコード②
次に見て頂きたいのは19行目から23行目です。
'ハイパーリンクを設定する
Worksheets("Top").Hyperlinks.Add _
Anchor:=Cells(rngWk.Row, rngWk.Column), _
Address:=rngWk.Text, _
ScreenTip:=rngWk.Offset(0, -1).Text, _
TextToDisplay:=rngWk.Text
以上のHyperlinks.Addメソッドで指定している引数は次の通りです。
- Anchor：ハイパーリンクを設定するセルの位置
- Address：ハイパーリンクを設定するURL
- ScreenTip：マウスオーバーした時のポップアップの表示文字列
- TextToDisplay：セルの文字列
①Anchor
Anchorには、ハイパーリンクを設定するセルの位置を指定します。
②Address
Addressには、ハイパーリンクを設定するURLを指定します。
ハイパーリンクが設定された後にセルの文字列をクリックすると、Addressに指定したURLのサイトが表示されます。
③ScreenTip
ScreenTipには、マウスオーバーした時のポップアップの表示文字列を指定します。
④TextToDisplay
TextToDisplayには、セルの文字列を指定します。
①ハイパーリンクの一括設定
注目すべきコード①
最初に見て頂きたいのは36行目です。
If InStr(rngWk.Text, "http") > 0 Then
この36行目のIf文で、セルに入った文字列がURLなのかを判定します。
「http」の文字列が含まれるかどうかはInstr関数を使います。
注目すべきコード②
次に見て頂きたいのは38行目から50行目です。
With rngWk
'ハイパーリンクを解除する
.ClearHyperlinks
'セルのアンダーラインの設定を解除する
.Font.Underline = xlUnderlineStyleNone
'フォントの塗りつぶしの色を「色なし」に設定する
'(※フォントの塗りつぶしの色設定はお使いの環境にあわせてください)
.Font.ColorIndex = xlAutomatic
End With
41行目では、ClearHyperlinksメソッドを実行してハイパーリンクを解除します。
ClearHyperlinksメソッドを実行するとハイパーリンクを解除することができますが、ハイパーリンクを設定した時に設定されたセルのアンダーラインとフォントの色はそのままなので、44行目と48行目でアンダーラインの設定解除とフォントの色を元に戻します。
動作確認
ハイパーリンク一括設定前
ハイパーリンクを一括で設定する前の状態です。
ハイパーリンクを一括で設定する前
ハイパーリンク一括設定後
ハイパーリンクを一括で設定するマクロを実行した後は次の画面の通りに表示されます。
ハイパーリンクを一括で設定するマクロを実行した後
ハイパーリンクが設定されているURLをクリックすると、URLのサイトが開きます。
試しにハイパーリンクが設定された「google」のURLをクリックすると下の通りにgoogleのサイトが開きます。
ハイパーリンク一括設定解除後
ハイパーリンクを一括で設定解除するマクロを実行した後は次の画面の通りに表示されます。
ハイパーリンクを一括で設定解除するマクロを実行した後
ハイパーリンクが解除されたURLの表示が元に戻っています。
最後に
この記事では、ハイパーリンクを一括で設定・解除する方法についてご説明しました。
ハイパーリンクの設定はHyperlinks.Addメソッドを、ハイパーリンクの解除はClearHyperlinksを使います。
ただし、ClearHyperlinksメソッドを使うとハイパーリンクの解除だけしか行わなれず、フォントの書式はハイパーリンクが設定されていた時と変わりません。
なので、別でセルのアンダーラインの設定とフォントの色を元に戻しています。
ハイパーリンクを一括で設定・解除したい場合には是非参考にしてみてくださいね。
プログラミングのスキルを習得するなら
プログラミングのスキルを習得したい、今のスキルをもっと高めたい、そう考えているなら「プログラミングスクール」がおすすめです。
プログラミングのスキルの基礎を身につけるなら「TechAcademy」で1週間の無料体験があるので、これで「プログラミングの基礎」を学ぶのにおすすめですよ。
