hyperlink dialog vba

Question

Can I invoke the Hyperlink Dialog from Excel VBA ?

I'd like to use it in much the same way as GetSaveFilename.

Answer 1

GetSaveAsFileName returns the filename specified by the user but it doesn't actually save - you as programmer have to do that.

You can use

Application.Dialogs(xlDialogInsertHyperlink).Show

to display the Insert Hyperlink dialog, but when the user clicks OK, the hyperlink is created.

Answer 2

Use code like this to add a hyperlink to a shape:

    Worksheets(cSheet).Hyperlinks.Add Anchor:=shp, Address:="", SubAddress:=cLink

Answer 3

There is no built-in dialog constant for displaying the Macros dialog (it makes little sense to display this dialog while a macro is running), but you could try

CommandBars.FindControl(ID:=186).Execute

Answer 4

Hans

Works very well, thanks.

Next question - when updating a hyperlink programmatically, the link refuses to give me the correct answer when trying to link to a cell in the same file, even when I take the property from a working link and simply place it back into the same link.

' cLink is taken from the address property of the original shape

' cSheet is the activesheet (but in my case won't always be)

'cObject is the name of the object against which the link is being updated

Set shp = Sheets(cSheet).Shapes(cObject)

With shp

       .Hyperlinks.Item(1).Name = cLink

       .Hyperlinks.Item(1).Address = cLink

End With

What I get is a link based on an 'Existing File or Web Page'

What I want is a link to a 'Place in This Document'

Answer 5

Is there a way to get the link returned ?

Also, is there a similar dialog option for selecting a macro ?