Using a ListBox with custom range

Question

I am trying to solve the following:

Create a ListBox with a range of 100 values for example in this order:

...0.1 0.2 1 1.3 1.6 1.9 3 10.3 30 ...
When any value is selected in the ListBox A1 is set to that value.
A2 is set to whatever value is before.

For example from the series above:

1.6 is selected in Listbox then A1==1.6 and A2 == 1.3

What I'm having problems with is how to define a custom range of number which I can use from many places in VBA, for example to populate the ListBox with and to use arithmetic on. My first thought is to use a Dictionary where the keys are 1..100 and the values are my range. However my limited experience with VBA in Excel makes me unsure how to even begin using such a data structure.

My question is: What is the best way to solve the following problem using VBA in Excel?

You can create a Named Range. First create the list you want on a different sheet and then see the 'Define a Name for a Cell or Cell Range on a Worksheet" section here: support.office.com/en-gb/article/… You can then add your names range to the RowSource property of the list box — Calico
– Calico, Commented Oct 29, 2017 at 11:55

ashleedawg · Accepted Answer · 2017-10-29 12:29:17Z

1

There's no need to over-complicate by using VBA for such a simple task.

On the other hand, it sure sounds like homework! If you "have to" simply record a macro doing the same steps, then edit the macro and you will see the VBA code. Kind of cheating, but hey...

Collectives™ on Stack Overflow

Using a ListBox with custom range

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related