I have a user form with a list box and a command button. Am I able to program a VLOOKUP formula within the command button so that it looks up the selected value in the list box and returns the new value into the active cell on the worksheet?
I have a user form with a list box and a command button. Am I able to program a VLOOKUP formula within the command button so that it looks up the selected value in the list box and returns the new value into the active cell on the worksheet?
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:
and change ListBox1 to the name of your list box, and also for the sheet and cell. Then your formula will look likePlease Login or Register to view this content.
=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:
Substitute your actual user form name and listbox name for MyUserForm.MyListBox.Please Login or Register to view this content.
Now your worksheet formula will look something like this:
=VLOOKUP(ListBoxValue,A1:B10,2,FALSE)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks