ＶＢＡについて

質問

ＥｘｃｅｌのＶＢＡで、ブック内のシートに飛ぶハイパーリンクを設定するマクロを組みたいんですが、シート名に変数を使うと、変数として認識されません。ためしにシート名を指定してみるとうまくいくんですが、どうしても変数を使いたいのです。いい方法ありますか？

回答No.2

papayuka · ベストアンサー

#1です。
引数名と変数の指定がおかしいのです。

SubAdress → SubAddress 、
"シート番号!A1" → シート番号 & "!A1"

つまり　SubAddress:= シート番号 & "!A1"　にすると動くと思います。

ちなみに以下はサンプルです。
一番左のシートの A4 以降に、2枚目以降のシート名を入れてリンクします。
シートが追加されても、シート名が変更されても、再実行すれば直します。
試すならテスト環境のブックで。

Sub Test1()
Dim i As Integer, r As Range
　With Worksheets(1)
　　.Hyperlinks.Delete
　　.Range("A4:A65536").ClearContents
　　For i = 2 To Worksheets.Count
　　　Set r = .Cells(i + 2, 1)
　　　r.Value = Worksheets(i).Name
　　　.Hyperlinks.Add Anchor:=r, Address:="", _
　　　　　SubAddress:=Worksheets(i).Name & "!A1"
　　Next i
　End With
End Sub

お礼

ありがとうございます、早速職場に行って試して見ます。

回答No.1

papayuka

どのように書いていて、どう言う結果を期待しているのかと、具体的なコードを書いた方がレスしやすいです。

Sub Test()
Dim sName As String, r As Range
　Set r = ActiveCell
　sName = "Sheet1" & "!A1"
　ActiveSheet.Hyperlinks.Add r, "", sName
End Sub

補足

Sub ハイパーリンク()
Dim 行番号 As Integer
Dim シート総数 As Integer
Dim シート番号 As Integer
シート総数 = Worksheets.Count
For 行番号 = 4 To シート総数 + 2
シート番号 = 行番号 - 2
Cells(行番号,1).Select
ActiveSheet.Hyperlinks.AddAnchor:=Selection, _
Address:="", SubAdress:= "シート番号!A1"
next 行番号
End Sub

　このように記述してます。ブックの構成は１シート目に索引があってＡ列に一連番号、Ｂ列に件名が入ってます。２シート目以降は同じ定型で内容が異なるものです。ブックによってシートの総数は変わりますが、索引+定型とゆう形は変わりません。
　１シート目の索引の一連番号をクリックすれば、それに対応するシートに飛ぶようにしたいのです。あと、マクロを実行してもフォント変わってしまわないようにしたいです。
　作業の関係で、シートの順番が入れ替わったり、間にシートを挿入したりするので、シート名を一連番号にするマクロと、並び替えたシート順で索引簿を作るマクロは完成してます。
　長く見にくい説明で申し訳ありませんがよろしくお願いします。