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?
 
ShumsDistinguished Expert - 2017Commented:
Hi Paul,

Please check attached...
vipaman_postcode.xlsm
0
 
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
 
ShumsDistinguished Expert - 2017Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
vipamanAuthor Commented:
when you say G3, I am assuming you mean G23? What do you mean by normal dropdown?
0
 
ShumsDistinguished Expert - 2017Commented:
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
 
ShumsDistinguished Expert - 2017Commented:
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
 
ShumsDistinguished Expert - 2017Commented:
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
 
ShumsDistinguished Expert - 2017Commented:
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
 
ShumsDistinguished Expert - 2017Commented:
You're Welcome Paul! Glad I was able to help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.