How to add a popup to add a hyperlink to a cell in Excel?

Question

I am new to Excel. I have a data sheet, and a lot of users have to enter their data in the sheet. I have a column with the header - 'linkToData'. Whenever a user clicks on a cell, there has to be a popup, which asks for the 'link text', and 'URL', so the user can enter it easily, than using 'Insert --> Link --> Insert Link'. How do I do this customization? Can you please help?

Thanks.

Answer 1

If the existing right-click menu item "Link" is not sufficient, then you can try intercepting the SelectionChange() event for the sheet.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim nCol As Integer
    nCol = Target.Column
    
    If Target.Row > 1 And Cells(1, nCol) = "linkToData" Then
        If Target.Hyperlinks.Count = 0 Then
            Application.Dialogs(xlDialogInsertHyperlink).Show
        End If
    End If
End Sub

Put this code in the VBA module for your sheet (eg "Sheet1(Sheet1)").

If the top cell in the column is "linkToData", and there isn't already a Hyperlink in the cell, then show the Insert Link dialog. You could write you own input form, but this dialog does what you need.