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

**DavidRoger** · 02-28-2013, 04:11 AM

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.

**Sniper** · 02-28-2013, 04:46 AM

Hi

Maybe this will help

**DavidRoger** · 02-28-2013, 05:44 AM

Thanks Sniper.

Do you think you can make A8 to show 1 instead of AAA?

**Sniper** · 02-28-2013, 06:36 AM

You mean like this?

**DavidRoger** · 02-28-2013, 08:27 AM

This is wonderful. If you don't mind, one more request. Can the data be inserted into the macro instead having it on the Excel?

**Sniper** · 02-28-2013, 08:38 AM

It can yes, does this mean that the data in the listbox never changes?

**DavidRoger** · 02-28-2013, 08:52 AM

Currently that is the full list. The reason for this is to prevent accidental change unless you really want to change it.

**Sniper** · 02-28-2013, 08:59 AM

In that case your original code is 100%

With Sheet1.ListBox1
.AddItem "AAA"
.AddItem "BBB"
.AddItem "CCC"

End With

**DavidRoger** · 02-28-2013, 09:35 AM

Is it possible to transfer my data to your macro? I would like to keep it in one place.

**Sniper** · 02-28-2013, 10:02 AM

Yes you can just add it here

**arlu1201** · 02-28-2013, 09:57 AM

DavidRoger,

Welcome to the forum.

I have added code tags to your post. As per forum rule 3, you need to use them whenever you put any code in your post. Please add them in future. If you need more information on how to use them, check my signature below this post.

**DavidRoger** · 02-28-2013, 07:15 PM

Originally Posted by arlu1201

DavidRoger,

Welcome to the forum.

I have added code tags to your post. As per forum rule 3, you need to use them whenever you put any code in your post. Please add them in future. If you need more information on how to use them, check my signature below this post.

I was looking for how to add the solve. Thanks for your guide. Thanks.

**DavidRoger** · 02-28-2013, 07:33 PM

Good to know that you are helping really a lot. More than thanks. Thanks.

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

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

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

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

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

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

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

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

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

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

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

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

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

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

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

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions