Drop down list not showing and also no data.

In the attached worksheet 'postcode', it contains 3500 postcodes. In the 'coversheet' there is a cell G23. I have added some code to a Drop down list courtesy of Extendoffice. I have defined the postcodes as a range 'Postcode'. I think I have defined them correctly in the code, but not 100%. When I open the workbook, cell G23 does not display the arrow denoting a drop down? If I go into design mode it appears. Also none of the postcodes display. What I am trying to achieve is to stop the user scrolling through 3500 records, as they type hopefully in lower or uppercase the drop down will autocomplete. Regards Paul
postcode.xlsm
vipamanAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ShumsExcel & VBA ExpertCommented:
Hi Paul,

Please check attached...
vipaman_postcode.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
vipamanAuthor Commented:
Thanks Shums. what did I do wrong? I have just looked at the code and I am struggling to see the issue. I need to back draft into my master spreadsheet.
0
ShumsExcel & VBA ExpertCommented:
Hi Paul,

You have code for Worksheet Selection Change, you need to make changes to G23 so the code will work, I just added normal dropdown to G23, which forces VBA to execute.
0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

vipamanAuthor Commented:
when you say G3, I am assuming you mean G23? What do you mean by normal dropdown?
0
ShumsExcel & VBA ExpertCommented:
Oops, sorry, yes I did realized later and changed G3 to G23.

Normal Dropdown from Data Validation, I have also created a new NamedRange called "PostCodeList", which I am referring in Data Validation Dropdown.
0
vipamanAuthor Commented:
Sorry struggling with this one a little. I have added a list via data validation and set to =postcode. My dropdown is now populated, but crashes because I haven't named the dropdown as TempCombo. I have gone into developer tab and try to set properties but no joy. I have noticed too as I type, it doesn't autofill, but I am hoping this is because of the TempCombo issue
0
ShumsExcel & VBA ExpertCommented:
You don't need to do any changes in VBA code,

Create a New Named Range called "PostCodeList" with below formula:
=postcode!$A$2:INDEX(postcode!$A:$A,MATCH(99^99,postcode!$B:$B,1))

Open in new window

Where Col B has all numeric values, if not change the Col Alphabet.
Then select G23 through arrow keys, Navigate to Data/Data Validation/Data Validation, Select List in Allow, Source =PostCodeList, press OK
Now select any postcode through drop down, your ComboBox Code will execute.
Hope this helps.
0
vipamanAuthor Commented:
Whatever I am doing, I am not inserting the same dropdown box as you. The properties are different.
0
ShumsExcel & VBA ExpertCommented:
Paul,

Newly created dropdown will not be visible, its just forcing your VBA code to execute.
0
vipamanAuthor Commented:
Argh right. Now for the daft question - where are you defining =postcode!$A$2:INDEX(postcode!$A:$A,MATCH(99^99,postcode!$B:$B,1))
0
ShumsExcel & VBA ExpertCommented:
Creating new Named Range:
  1. Navigate to Formulas/Name Manager
  2. Click New
  3. Name: PostCodeList
  4. Refers To: =postcode!$A$2:INDEX(postcode!$A:$A,MATCH(99^99,postcode!$B:$B,1))
  5. Click OK
0
vipamanAuthor Commented:
After a stupid issue - managed to paste in a apostrophe on pasting and I didn't notice, everything appears to be set-up correctly. However when I run the sheet (I have saved and restarted excel) the VB script fails on Me.TempCombo.DropDown.
Now I named TempCombo via the name box (top left). Was this incorrect?
0
vipamanAuthor Commented:
Thank you. Excellent support.
0
ShumsExcel & VBA ExpertCommented:
You're Welcome Paul! Glad I was able to help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.