VLookup fromula referencing a listbox and executed by a command button

A worksheet formula has no way to refer to a value selected in a listbox used on a userform. (If the listbox were in a worksheet you could use the LinkedCell attribute, but for some strange reason this attribute is not available in a listbox used on a userform.) I can think of two ways to do this.

1. Crude but effective: Write a Change handler for the listbox so that every time a value is selected, that value is written to a cell someplace. This code goes into the userform module:

Please Login or Register  to view this content.

and change ListBox1 to the name of your list box, and also for the sheet and cell. Then your formula will look like

=VLOOKUP(Sheet1!A1,A1:B10,2,FALSE)

2. Impress your friends: Write a UDF to return the value. Create a new Module, and insert code like this:

Please Login or Register  to view this content.

Substitute your actual user form name and listbox name for MyUserForm.MyListBox.

Now your worksheet formula will look something like this:

=VLOOKUP(ListBoxValue,A1:B10,2,FALSE)