Code to reset combobox dependant on another combobox

**j.farr3ll** · 09-05-2011, 10:12 AM

I have a sheet which has two comboboxes using vlookups to return populations from a reference sheet. I am looking for a code which will link them so that when a value is selected in the first, other will automatically reset to the option none and vice versa. That might not make much sense so I have attached a sample worksheet....

On the same sheet I have also created a textbox where I want people to be able to enter their own population sizes. Here I'm looking for a code to display the value which the user types in this box in cell I11. I would also like this box to be linked to the other 2 so that they return to none when a value is entered here. Any help with these codes would be greatly appreciated...

Thanks,
James

**jaslake** · 09-05-2011, 12:07 PM

Hi j.farr3ll

Try this code in worksheet "Target population" module

Please Login or Register  to view this content.

Let me know of issues.

**j.farr3ll** · 09-06-2011, 09:14 AM

Thats perfect, thanks John

**jaslake** · 09-06-2011, 06:11 PM

Hi j.farr3ll
You're welcome...glad to be of help.

**j.farr3ll** · 09-07-2011, 04:00 AM

Originally Posted by jaslake

Hi j.farr3ll
You're welcome...glad to be of help.

Hi John,

The code works great, but when I go to close the workbook I get a VBA error: Run-Time error '91' (object variable or With Block variable not set).

If I click debug the following line of code is highlighted:

Please Login or Register  to view this content.

I have tried defining the variables using Dim, but this hasn't solved the problem. Do you know how to fix the error at all?

**jaslake** · 09-07-2011, 12:06 PM

Hi j.farr3ll

This appears to be a common problem and appears to be an Excel bug. This code will work around the issue.

In a General Module, at the very top line

Please Login or Register  to view this content.

In the This Workbook Module

Please Login or Register  to view this content.

Your Target Worksheet Module

Please Login or Register  to view this content.

Let me know of issues.

**j.farr3ll** · 09-07-2011, 12:24 PM

I have tried to use this code as you describe but am now getting a 'compile error: variable not defined' when I try to use the comboboxes. This is happening on the following line:

Please Login or Register  to view this content.

Any Ideas?

**jaslake** · 09-07-2011, 12:31 PM

Hi j.farr3ll

That would indicate that "bFlag" has not been defined. Have you done this

In a General Module, at the very top line

Please Login or Register  to view this content.

If you have...were you using bFlag previously...show me the code in the General Module.

**j.farr3ll** · 09-08-2011, 04:06 AM

Hi John,

Yes I hadn't correctly entered that line in a general module.

It is working perfectly now; no errors at all.

Thank you very much for your help,
James

**jaslake** · 09-08-2011, 08:21 AM

Hi James

You're welcome...glad I could be of help. If that satisfies your need, I'd appreciate it if you'll please mark your thread as "Solved".
To mark your thread solved do the following:
- Go to your first post on the thread
- Click edit
- Click Advance
- Just below the word "Title:" you will see a dropdown with the word No prefix.
- Change to Solved
- Click Save