MAC VBA Workarounds – Listindex

Listindex does not work on Mac VBA in Excel. It’s a shame because it can be a very powerful function when dealing with Comboboxes in Userforms for instance. If you want to know the order in the list that a chosen value is held so you can populate other parts of the form, or lookup corresponding cell values to the value chosen in a list we have to use a workaround which isn’t entirely a bad thing, it just takes a little more logic to get it to work.

If for instance you had a list of fruit in a Combobox taken from a list on a worksheet and the stock level in the cell to the right of the fruits name:

Apples 10
Bananas 7
Pears 6

Your combobox1 contains Apples,Bananas,Pears to choose from. The usual way to identify which numbered item in the list you have chosen would be to use:

Frt = combobox1.listindex

So if you chose Apples, your listindex value would be 0 – (listindex always starts at 0) and if you chose Bananas your listindex would be 1. You would then use a command to identify the stock level:

Msgbox worksheets(“Fruitlist”).Range(“B” & listindex + 1)

The reason we include + 1 is due to the listindex first value always being 0. However on a Mac this will trip and won’t work. We have to workaround by actually going and looking for the value in combobox1 in the list that the combobox1 list is derived from, in this case the 3 fruits.

Postn = worksheetfunction.match(combobox1,worksheets("Fruits").Range("A:A"),0)

This will give you the value that listindex + 1 would have given you. At this point you can then use:

Msgbox worksheets("Fruit").Range("B" & Postn)

Whilst the Mac may prove different to Windows VBA in many instances, there is usually a workaround that follows a logical path and you will be able to succeed in your project on a Mac. The best part is that if designed on Windows to work on a Mac, it will work on both in most instances. Feel free to contact us to help with your Excel project. Our Excel consultations start from as little as 1 hour.

Contact us for some advice and guidance on how your Excel development could be created and start helping your business straight away. Contact Us


Leave a Reply

Your email address will not be published. Required fields are marked *