asked on 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
postcode.xlsm
Microsoft OfficeVBAMicrosoft ExcelMicrosoft ApplicationsProgramming
Last Comment
Shums Faruk
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
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.
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.
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.
ASKER
when you say G3, I am assuming you mean G23? What do you mean by normal dropdown?
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.
Normal Dropdown from Data Validation, I have also created a new NamedRange called "PostCodeList", which I am referring in Data Validation Dropdown.
ASKER
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
You don't need to do any changes in VBA code,
Create a New Named Range called "PostCodeList" with below formula:
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.
Create a New Named Range called "PostCodeList" with below formula:
=postcode!$A$2:INDEX(postcode!$A:$A,MATCH(99^99,postcode!$B:$B,1))
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.
ASKER
Whatever I am doing, I am not inserting the same dropdown box as you. The properties are different.
Paul,
Newly created dropdown will not be visible, its just forcing your VBA code to execute.
Newly created dropdown will not be visible, its just forcing your VBA code to execute.
ASKER
Argh right. Now for the daft question - where are you defining =postcode!$A$2:INDEX(postc
Creating new Named Range:
- Navigate to Formulas/Name Manager
- Click New
- Name: PostCodeList
- Refers To: =postcode!$A$2:INDEX(postc
ode!$A:$A, MATCH(99^9 9,postcode !$B:$B,1)) - Click OK
ASKER
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?
Now I named TempCombo via the name box (top left). Was this incorrect?
ASKER
Thank you. Excellent support.
You're Welcome Paul! Glad I was able to help.