Problems with a Combo Box
 

Hi,

I have set up a number of combo boxes and am running some _change macros
from them (see code below)

Private Sub Q2_Combo_Change()
Application.EnableEvents = False
If Sheets("Answers").Range("C2") = "Yes" Then MsgBox "Watch list"
Application.EnableEvents = True
End Sub

The combo box data is based on a variable range using an offset function:

=OFFSET(Lookups!$B$2,0,0,COUNTA(Lookups!$B:$B)-1,1)

This is to cover people changing the list in the future and the combo box
needing to auto update the list. For some values in this list, they are on a
watch list. Cell "C2" monitors for combo box output equal to an item on the
watch list. Problem is that when the target cell in "C2" equals "Yes" (based
on the combo box selection) the msg box appears twice.

Any ideas how to make it only appear once?

Rich

Problems with a Combo Box
 

What is the formula in C2?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Rich" wrote in message
...
Hi,

I have set up a number of combo boxes and am running some _change macros
from them (see code below)

Private Sub Q2_Combo_Change()
Application.EnableEvents = False
If Sheets("Answers").Range("C2") = "Yes" Then MsgBox "Watch list"
Application.EnableEvents = True
End Sub

The combo box data is based on a variable range using an offset function:

=OFFSET(Lookups!$B$2,0,0,COUNTA(Lookups!$B:$B)-1,1)

This is to cover people changing the list in the future and the combo box
needing to auto update the list. For some values in this list, they are
on a
watch list. Cell "C2" monitors for combo box output equal to an item on
the
watch list. Problem is that when the target cell in "C2" equals "Yes"
(based
on the combo box selection) the msg box appears twice.

Any ideas how to make it only appear once?

Rich

Problems with a Combo Box
 

Cell C2 contains a lookup formula which essentially equates to "Yes" or "No"
based on the value of another drop-down box.

"Bob Phillips" wrote:

What is the formula in C2?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Rich" wrote in message
...
Hi,

I have set up a number of combo boxes and am running some _change macros
from them (see code below)

Private Sub Q2_Combo_Change()
Application.EnableEvents = False
If Sheets("Answers").Range("C2") = "Yes" Then MsgBox "Watch list"
Application.EnableEvents = True
End Sub

The combo box data is based on a variable range using an offset function:

=OFFSET(Lookups!$B$2,0,0,COUNTA(Lookups!$B:$B)-1,1)

This is to cover people changing the list in the future and the combo box
needing to auto update the list. For some values in this list, they are
on a
watch list. Cell "C2" monitors for combo box output equal to an item on
the
watch list. Problem is that when the target cell in "C2" equals "Yes"
(based
on the combo box selection) the msg box appears twice.

Any ideas how to make it only appear once?

Rich