Data Validation Combobox Help

**BennyL** · 03-12-2016, 11:31 AM

Hi All
I am rather new to Excel so excuse me if this is a dumb/old question.

I am trying to use the code developed by Contextures.com to link a Combobox to a Dropdown box.

I can get the Combobox to show the data from the table that I have created but as soon as I have copied and pasted the code into the VBA, I double click on a Dropdown box and it is blank??

The sheet with the Dropdown Lists & Combox is named "Test Results" and the sheet with the Table is called "Max Zs values".

I have posted the code and the file attachment below. Let me know if you require any more info.

I have tried looking at the example workbook on the Contextures website but I can't see where I am going wrong.

Can anyone help?

Thanks in advance.
Ben

Please Login or Register  to view this content.

**jaslake** · 03-12-2016, 12:38 PM

Hi BennyL

Welcome to the Forum!!!

Change your Data Validation Rule.

Data Validation.jpg

**tim201110** · 03-12-2016, 12:45 PM

could you upload an example file from the site?

**BennyL** · 03-12-2016, 01:22 PM

Thanks for your responses.

Jaslake, I have tried changing the source to "type" but it doesn't seem to help. Do I need to have any specific cells highlighted when I do this?

Tim, please see link to the example sheet from Contextures.com

Thanks for your help

**jaslake** · 03-12-2016, 01:40 PM

Hi Ben

Try the attached...

**BennyL** · 03-12-2016, 01:47 PM

Thanks Jaslake

Is that working your end?

On my computer the Combobox pops up to replace the Dropdown box as it should, however, it is blank

**jaslake** · 03-12-2016, 02:32 PM

Hi Ben

See attached...

Combo Pic.jpg

**BennyL** · 03-12-2016, 02:51 PM

Hi Jaslake

Yeah, that is what it is supposed to do but on mine it just comes up blank. I must have some setting wrong somewhere but I'm damned if I can find it.

Untitled.jpg

**jaslake** · 03-12-2016, 03:41 PM

Hi Ben

If you wish attach your File WITH THE CODE EMBEDDED and I'll look at it.

What version of Excel are you running?

**BennyL** · 03-12-2016, 03:53 PM

Thank you so much for your help Jaslake.
I have attached the workbook with the macros enabled. Is that what you mean?

**jaslake** · 03-12-2016, 04:32 PM

Hi Ben

You hadn't changed the Data Validation...

**BennyL** · 03-12-2016, 04:40 PM

Yeah sorry, I sent you the original which hadn't been changed.

I'm not joking but that workbook you sent back still throws up blank Comboboxes on my computer!

Do you think that it might be some setting that I've got turned on / off that shouldn't be??

**jaslake** · 03-12-2016, 04:44 PM

Hi Ben

No, I don't think it's a setting (assuming you've allowed Macros to run). What version of Excel are you running?
Send the File back...I'll see if I can trouble shoot it.

**BennyL** · 03-12-2016, 04:58 PM

I believe that Macros are allowed.

I have re-attached. Let me know if you require anything else.

Thank you

**jaslake** · 03-12-2016, 05:09 PM

Hi Ben

What do you mean when you say

I believe that Macros are allowed.

Have you enabled Macros?
Allow Macros.jpg

**jaslake** · 03-12-2016, 05:13 PM

Hi Ben

What Version of Excel are you running?

It may well be a setting!!!

**BennyL** · 03-12-2016, 05:21 PM

Hi Jaslake

Sorry forgot that bit! Its 2013.

When I go into Macro Security and open settings I clicked on "disable macros with notification".

**jaslake** · 03-12-2016, 05:26 PM

Originally Posted by BennyL

Hi Jaslake

Sorry forgot that bit! Its 2013.

When I go into Macro Security and open settings I clicked on "disable macros with notification".

Yes, that's what it should be...and what do you do here when you open the File?

Allow Macros.jpg

**BennyL** · 03-12-2016, 05:32 PM

The "Option" box does not appear when I open the workbook. Do you think that might be something to do with it?

**jaslake** · 03-12-2016, 05:45 PM

Hi Ben

I don't have Excel 2013. SHOW ME the equivalent of this...take a Screen Capture and attach it.

Trust Center Settings.jpg

**BennyL** · 03-12-2016, 05:49 PM

Hi Jaslake

Please see attached. It looks like it is the same as what you have there.

**jaslake** · 03-12-2016, 06:37 PM

Hi Ben

As I don't have Excel 2013 I've asked for help...http://www.excelforum.com/showthread...43#post4337343

Please follow this Thread as I will.

Gave the wrong link....http://www.excelforum.com/the-water-...ry-2016-a.html

**jaslake** · 03-12-2016, 06:42 PM

Hi Ben

I'd suggest this has everything to do with it...just don't know why...

The "Option" box does not appear when I open the workbook. Do you think that might be something to do with it?

**BennyL** · 03-13-2016, 06:00 AM

Thanks for your patience Jaslake

I will follow the other thread now

Kind Regards
Ben

**tony h** · 03-13-2016, 03:37 PM

Try this one:

**BennyL** · 03-13-2016, 06:10 PM

Hi Tony
I have tried the workbook you posted but I am still getting a blank Combobox appear. I am thinking that this must be a setting within my Excel as everyone else seems to be able to get this to work.....frustrating 😠

Any suggestions would be gladly received.

Thank you
Ben

**jaslake** · 03-13-2016, 06:40 PM

Hi Tony/Ben

This is Tony's File with Data Validation set to "Type" for all Cells in Rows 11 - 47 in Column K (which were not set before).

Will each of you please try this File in Excel 2013...I suspect our issue is with the Merged Cells (though it DOES work in Excel 2007 and 2010).
If we find that the issue is merged cells then we can perhaps resolve the issue...except...I don't have 2013 to test with so the two of you will need to do the testing...OK?

**BennyL** · 03-14-2016, 04:03 AM

Hi Jaslake

I have tried the "New Test" sheet on two different copies of Excel 2013 and the issue is still there.

I also managed to find a thread on the Contextures blog regarding blank Comboboxes but I believe that my spreadsheet is already set up the way that Debra suggests. Could you take a look and confirm for me please.

http://blog.contextures.com/archives...in-excel-2013/

Kind regards
Ben

**Izandol** · 03-14-2016, 06:08 AM

You may use:

Please Login or Register  to view this content.

**BennyL** · 03-14-2016, 06:29 AM

Thanks Izandol
I'm a newbie to Excel, where should I insert that code?
Ben

**tony h** · 03-14-2016, 11:57 AM

Ok . Now I have a difference
after the line (in the selection change) .ListFillRange = str

I added (rather over the top but ..)
Debug.Print "str=/"; str; "/ .ListFillRange=/"; .ListFillRange; "/end"

In 2007 you can see that .ListFillRange has been populated correctly with the value from str
str=/Type/ .ListFillRange=/Type/end

Whereas in 2013 the value is lost - but str contains the expected value
str=/Type/ .ListFillRange=//end

**tony h** · 03-14-2016, 12:30 PM

This one has surrogate name of TypeB defined as Type and adds a "B" to str in selection change

**jaslake** · 03-14-2016, 02:14 PM

Hi Tony

Are you inferring your most recent File produces expected results in Excel 2013?

**BennyL** · 03-14-2016, 02:19 PM

Tony

Thanks for your time earlier.

The Combobox works as it should now so I can not thank you enough, however, I have found a strange bug when used on a Surface Pro 4 (which is required as the spreadsheet is designed to be used by site engineers). I am unable to write into any cells once a selection has been made in any Combobox, even though it all works fine beforehand.

Any ideas what this might be?

Thanks again, Ben

**tony h** · 03-15-2016, 04:02 PM

Hi,

I have asked elsewhere if anyone can check it out on a surface pro

**tony h** · 03-24-2016, 03:36 PM

Did you find anything about the Surface Pro bug?

**BennyL** · 03-25-2016, 03:03 AM

Hi Tony
No nothing as of yet. I have also started a new thread specifically for the Surface Pro issue

**tony h** · 03-25-2016, 04:15 AM

Good idea. I'll see how it is going.

Data Validation Combobox Help

Re: Data Validation Combobox Help

Re: Data Validation Combobox Help

Re: Data Validation Combobox Help

Re: Data Validation Combobox Help

Re: Data Validation Combobox Help

Re: Data Validation Combobox Help

Re: Data Validation Combobox Help

Re: Data Validation Combobox Help

Re: Data Validation Combobox Help

Re: Data Validation Combobox Help

Re: Data Validation Combobox Help

Re: Data Validation Combobox Help

Re: Data Validation Combobox Help

Re: Data Validation Combobox Help

Re: Data Validation Combobox Help

Re: Data Validation Combobox Help

Re: Data Validation Combobox Help

Re: Data Validation Combobox Help

Re: Data Validation Combobox Help

Re: Data Validation Combobox Help

Re: Data Validation Combobox Help

Re: Data Validation Combobox Help

Re: Data Validation Combobox Help

Re: Data Validation Combobox Help

Re: Data Validation Combobox Help

Re: Data Validation Combobox Help

Re: Data Validation Combobox Help

Re: Data Validation Combobox Help

Re: Data Validation Combobox Help

Re: Data Validation Combobox Help

Re: Data Validation Combobox Help

Re: Data Validation Combobox Help

Re: Data Validation Combobox Help

Re: Data Validation Combobox Help

Re: Data Validation Combobox Help

Re: Data Validation Combobox Help

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Data Validation + ComboBox Autocomplete

Bookmarks

Bookmarks

Posting Permissions