Add your own event enabler

Private Sub btnIntGroup_Click()
Dim fReEntry As Boolean
Dim answer As Long

If Not fReEntry Then

fReEntry = True

Range("A1").Select ' selects top left most cell
' Finds product Interest placeholder
Cells.Find(What:="ProdInt", _
After:=ActiveCell, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByColumns, _
MatchCase:=True).Activate

If btnIntGroup.Value = True Then
If ActiveCell.Offset(2, 3).Value < "" Then
answer = MsgBox("Are you sure you would like to change" & _
"from product to group selection?", vbYesNo)
If answer = vbYes Then
ActiveCell.Offset(2, 3).Value = "" 'clears the dump
cell
ActiveCell.Offset(0, 3).Value = "Any" 'resets cell with
data Validation
With Sheets(2)
Sheets(2).Cells(2, 22).ClearContents
Sheets(2).Cells(2, 22).Value = "ProductGroup" 'changes
Validation List
End With
Else
btnIntGroup.Value = False
btnIntProduct.Value = True
End If
Else
With Sheets(2)
Sheets(2).Cells(2, 22).ClearContents
Sheets(2).Cells(2, 22).Value = "ProductGroup"
End With
End If
Else: End If

fReEntry = False
End If
End Sub

and similarly for product


--
---
HTH

Bob


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



"MarkyB" wrote in message
...
I use two radio buttons to dictate which list of values another cell uses
for
data validation. i.e. btnIntGroup selects one list and btnIntProduct
selects
another. If a user then selects a value from a list, another button will
dump
this value into a seperate cell.

I need to add validation to the selecting of these buttons once a user has
already selected an item from a list. I can validate if the user wishes to
switch from Group to Product (and vice versa) but if they do not, when I
reset the radio button values it calls the _click() function and then gets
stuck in a loop between the _Click() functions for both buttons.

How can I change the values of these buttons back to how they were
originally WITHOUT calling the other _click() function?

Code for one button (the other one is practically the same):

Private Sub btnIntGroup_Click()
Dim answer As Long

Range("A1").Select ' selects top left most cell
' Finds product Interest placeholder
Cells.Find(What:="ProdInt", After:=ActiveCell, LookIn:=xlValues,
LookAt:=xlWhole, SearchOrder:=xlByColumns, MatchCase:=True).Activate

If btnIntGroup.Value = True Then
If ActiveCell.Offset(2, 3).Value < "" Then
answer = MsgBox("Are you sure you would like to change from
product to group selection?", vbYesNo)
If answer = vbYes Then
ActiveCell.Offset(2, 3).Value = "" 'clears the dump
cell
ActiveCell.Offset(0, 3).Value = "Any" 'resets cell
with
data validation
With Sheets(2)
Sheets(2).Cells(2, 22).ClearContents
Sheets(2).Cells(2, 22).Value = "ProductGroup" 'changes
validation list
End With
Else
' PROBLEM IS THESE NEXT TWO LINES AS IT WILL CALL THE OTHER BUTTONS
_Click() FUNCTION
btnIntGroup.Value = False
btnIntProduct.Value = True
End If
Else
With Sheets(2)
Sheets(2).Cells(2, 22).ClearContents
Sheets(2).Cells(2, 22).Value = "ProductGroup"
End With
End If
Else: End If
End Sub

I hope this is clear enough.

Mark