Show first item in dropdown list instead of blank with VBA

**thecdnmole** · 12-12-2018, 01:51 PM

Hi, I have a list which contains formulas which I think is causing the problems. I tried to make a dynamic list and a table as explained on the Contextures website but either the list will not grow when a word is added (because of the formula?), or it is as long as the list of formulas, which is then showing the blanks and you have to scroll up to find the words in the list. I have also tried this from Extendoffice, but it does not work either. Attached is a simple example. The list is in column A, made into a table and contains formula. The dropdown list is in cell C1 which shows the whole list and not just 1. I also added a new formula in A12 which didn't expand the table either. Can someone help? Thanks!

Please Login or Register  to view this content.

**Logit** · 12-12-2018, 02:09 PM

.
https://excelchamps.com/blog/dynamic-drop-down-list/

**thecdnmole** · 12-12-2018, 02:15 PM

Thanks for the reply Logit, but the link is more or less the same was what I tried from the Conextures website which didn't work because of the formulas in the list, but I only want the list to show the results of the formula.

**thecdnmole** · 12-12-2018, 03:16 PM

Ok, I just tried the sub from Extendoffice on my example workbook I uploaded and it worked! But, if I move the list to another worksheet it does not work. Can someone help with that? I have all my lists on a seperate hidden worksheet. Thanks!

**thecdnmole** · 12-12-2018, 03:34 PM

I figured it out! If the list isn't on the same worksheet, then in this line, Target.Cells(1) = Range(Mid(xFormula, 1)).Cells(1).Value, you need to add the sheet name, as in Target.Cells(1) = Sheet1.Range(Mid(xFormula, 1)).Cells(1).Value.

**Logit** · 12-12-2018, 03:44 PM

.
Gald you have it solved !

Show first item in dropdown list instead of blank with VBA

LinkBack

Thread Tools

Rate This Thread

Display

Show first item in dropdown list instead of blank with VBA

Re: Show first item in dropdown list instead of blank with VBA

Re: Show first item in dropdown list instead of blank with VBA

Re: Show first item in dropdown list instead of blank with VBA

Re: Show first item in dropdown list instead of blank with VBA

Re: Show first item in dropdown list instead of blank with VBA

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Resetting all dropdown menus to first item in list

Remove items from dropdown list when item value = 0

Combobox Dropdown List only shows first Item

use dropdown and show another item automatically

if a = name from dropdown then e=list item and g=list item

Any way to get the index of a selected item from a dropdown list?

[SOLVED] How can I set up to chose more than 1 item from a dropdown list (.

Bookmarks

Bookmarks

Posting Permissions