How Can I tap into Excel's "Insert Hyperlink" Dialog box

[itechxxiv]

.:The Scenario:.
I manage all the documents Team Leads create, modify, and distribute. I use an Excel spreadsheet to track & report various
attributes i.e. Doc Started, If it is under review, and so. There about 50 documents to track. The Document data is cultivated from
our online Document Management System.

.:The Rational:.
At some point in the process I manually create a Hyperlink in my spreadsheet for each entry to easily jump to the Doc location
within the online Doc Mgnt system. I am transitioning the WrkSht to be more robust and integrate some automation.


.:The Current State:
I have created a drop down list that allows me to select the
appropriate item based on the Doc status. Then I use Excel?s
?Insert Hyperlink? to apply the URL to ?LINK? item in the list.

Fig.1


[Items in the List: Link, No Link, No External Doc, NA]

.:The Objective:.
Is to have the ?Insert Hyperlink? Dialog box automatically pop-up when the user selects
?LINK? in the drop down list.

Fig.2


The drop-down list reside in column R, Cell Range(R9:R65) How can I use VB
to create this feature. Or perhaps bypass this approach and create a Form
that pops-up that the user can paste in the URL.

Thank you for your time and effort.

I?m rather new to VB please try to give me as much detail as you can or in addition,
- Links to How-To?s
- Other Tutorials

itechxxiv

[austenr]

Check out this link

http://support.microsoft.com/?kbid=247507

[Bob Phillips]

Is this what you mean

[vba]
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "R9:R65"
Dim myLink

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
If Target.Value = "Link" Then
myLink = Application.Dialogs(xlDialogInsertHyperlink).Show
End If
End If

ws_exit:
Application.EnableEvents = True
End Sub
[/vba]

This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.

[Ken Puls]

FYI, Cross posted at JMT

[itechxxiv]

Sorry for the delay. Get? in back from the holidays is killing me at work.
First off sorry about the miscommunication regarding the cross linking.
kpul brought this to my attention http://www.puremis.net/excel/cgi-bin...m=1135876262/0
and I take it very seriously. Going forward, I will be sure to not do that again.

Ok, let me copy the code and play around with it and respond as soon as I can.
This project is due on 1/13/06. So, bear with me while I work more or less for
the next 48 hours.