Macro Triggered By Combo Box

[fredjones]

Hello,

I have a combo box that's embedded in a cell. It has three values: (blank), Yes and No. I would like to trigger a macro after a user selects an option from this combo box. I have the following code, but I can't seem to get it to work:

Please Login or Register  to view this content.

Any help would be great.

Cheers

[OnErrorGoto0]

Assuming the name is correct, that should work. Is it even being triggered?

[fredjones]

Hi Rory,

It doesn't appear to be triggering. I've attached a sample of it

[OnErrorGoto0]

Ah - that is a data validation drop down - those do not have events and cannot have macros assigned to them. You will need a worksheet_change event instead. Right click the sheet tab, choose View Code and then paste this in, instead of what you have currently:

Please Login or Register  to view this content.

[fredjones]

Thanks Rory. Worked like a charm

[fredjones]

Hi again,

I've just encountered a problem when I try to combine three of these as separate sub functions.

Please Login or Register  to view this content.

When I trigger any of the three combo boxes I receive the error: 'Compile error: Ambiguous name detected: Worksheet_Change.'

Does that mean I have to somehow combine these under one sub function? Note, are you allowed to include wildcards in code? For instance in my second and third statements I use Yes# and No#. This is because I have multiple answers with Yes and no that I want the case trigger to apply to. Is this ok?

Thanks again.

[fredjones]

Sorry to bump this, but is there an easy solution to the above?

[OnErrorGoto0]

You will have to combine them into one Change event. I'm not 100% clear on the wildcard issue - do you mean the cell contains 'Yes' somewhere in it?

[snb]

You don't need any module.
You should 'unmerge' F5 and G5 first.
I'd prefer only 2 values in the validationlist: yes and no.
the only code you need is:

Please Login or Register  to view this content.

[fredjones]

You will have to combine them into one Change event. I'm not 100% clear on the wildcard issue - do you mean the cell contains 'Yes' somewhere in it?

Yes that's correct.

SNB - my final spreadsheet has these combo boxes spread out over the full sheet. And in between there are other combo boxes with Yes/No answers. For user clarity I would prefer to keep these combo box answers in their existing format, rather than simplify them to 'yes/no'.

Cheers.

[OnErrorGoto0]

Please Login or Register  to view this content.

should do it. (untested)

[fredjones]

Excellent. Thanks OnErrorGoTo0. I removed the module1 from the second and third parts and it worked perfectly. Really appreciate your efforts on this. Although you don't know me, rest assured you made my day.

[OnErrorGoto0]

Glad to help.