List box's macro: Adding index formula to Linked Cell

[DavidRoger]

Hi all,

I have created a list box (xactive control) with macro shown below:

Private Sub Workbook_Open()

With Sheet1.ListBox1
   .AddItem "AAA"
   .AddItem "BBB"
   .AddItem "CCC"
   .LinkedCell = "$A$5"
   
End With

End Sub

To complete the list box, when click "AAA" shows 1, click "BBB" shows 2 and click "CCC" shows 3.

I have searched for two days online that the solution is to add index formula to Linked Cell. However I can not use the index formula since my List Fill Range is in macro.

Any help would be appreciated.