Pass arguments within Excel

Question

How can I do the following ?

I have some two thousands rows in Excel in following format :

NameID

Dejan 8082

Bernard 8317

Will 8349

.

.

I would like to create some hyperlinks at all IDs so that once I click any of them, the ID and the corresponding Name will be passed as arguments to the following batch file.

echo

echo Dear %1,

echo Your ID which is %2 has been locked.

I can call a batch file through hyperlink but seems passing argument in it is invalid.

I suspect some Visual Basic coding is needed. Can anyone help ?

更新:

Sorry. The original table above depicts the format incorrectly. All the names should be in column "A" and IDs should be in column "B". Something like the following :

|___A___|___B__|

|_______|_______|

|_Name_|___ID__|

|__Joe__|__1567_|

|__Ada__|____17_|

|__Ben__|____32_|

Answer 1

最愛解答

　 A B C D E 1 NameID 　 　 　 　 2 Dejan 8082 　 　 　 Will 8349 3 Bernard 8317 　 　 　 　 4 Will 8349 　 　 　 Will 5 　 　 　 　 8349

在工作表加入以下VB code:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

If Intersect(Range("E2"), ActiveCell) Is Nothing Then

If Not ActiveCell = "" Then

Application.EnableEvents = False

Range("E2") = ActiveCell

Application.EnableEvents = True

Cancel = True

End If

End If

End Sub

如上表，double click A4會把value pass 去E2，然後再把E2分析去E4 & E5，跟住先用E4&E5去出你封信。

E4公式 =LEFT(E2,LEN(E2)-5)

E5公式 =RIGHT(E2,4)

看看這方法能否達到你的目的。

2013-07-19 13:59:18 補充：

下面的 VB code可成功pass E4, E5 去 batch file:

Sub test()

Shell ("c:\new.bat " & Range("E4").Value & " " & Range("E5").Value)

End Sub

2013-07-20 00:02:33 補充：

唔緊要，將錯就錯，反正double click 都係只 click 一個 cell，咁就做多一個 column 來combine Name & ID, 假設是放在C column 的話，咁double click C column 的 cell，一樣可以得相同結果。

C2: A2 & " " & B2

C3: A3 & " " & B3

......

連VBA code都唔使改。