【VBA】ハイパーリンクを作成するフォーム

フォーム

Private Sub CommandButton1_Click()
    Call エクセル
End Sub

Private Sub CommandButton2_Click()
    Call ファイル
End Sub

Private Sub CommandButton3_Click()
    Call その他
End Sub

Private Sub TextBox1_Change()
     Path = TextBox1.Text
End Sub

Private Sub TextBox2_Change()
     File = TextBox2.Text
End Sub

Private Sub TextBox3_Change()
    Sheet = TextBox3.Text
End Sub

Private Sub TextBox4_Change()
    Cell = TextBox4.Text
End Sub

Private Sub TextBox5_Change()
    Title = TextBox5.Text
End Sub

Private Sub UserForm_Initialize()
    Cell = "A1"
    Sheet = "Sheet1"
End Sub

ThisWorkbook

Private Sub Workbook_Open()
    Dim objCB As CommandBar
    Dim objCBCtrl As CommandBarControl

    Set objCB = Application.CommandBars("Worksheet Menu Bar")

    On Error Resume Next
    objCB.Controls("ハイパーリンク").Delete
    On Error GoTo 0

    Set objCBCtrl = objCB.Controls.Add(Type:=msoControlButton)
    
    With objCBCtrl
        .Caption = "ハイパーリンク"
        .FaceId = 277
        .OnAction = "ユーザーフォーム"
    End With
End Sub

標準モジュール

Public Path As String
Public File As String
Public Sheet As String
Public Cell As String
Public Title As String

Sub ユーザーフォーム()
    UserForm1.Show vbModeless
End Sub

Sub エクセル()
    ActiveCell.FormulaR1C1 = "=HYPERLINK(""[" + Path + "\" + File + "]" + Sheet + "!" + Cell + """, """ + Title + """)"
End Sub

Sub ファイル()
    ActiveCell.FormulaR1C1 = "=HYPERLINK(""" + Path + "\" + File + """, """ + Title + """)"
End Sub

Sub その他()
    ActiveCell.FormulaR1C1 = "=HYPERLINK(""" + Path + """, """ + Title + """)"
End Sub