Reset of radio button values without calling _Click() function

[MarkyB]

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

 

[Bob Phillips]

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]

Hi Bob, I have used your changes but it still loops through both functions
indefinitely. Does it matter if I give both event handlers the same name?

I have stepped through the code and fReEntry always has an initial value of
False and then get changed to True, and then continues through my code.

Clicking Yes in the MSgBox works perfectly, it's only if you click No that
he loop occurs.

 

[Rick Rothstein \(MVP - VB\)]

Try changing the declaration of fReEntry from this...

Dim fReEntry As Boolean

to this instead....

Static fReEntry As Boolean

and see if that makes it work.

Rick

 

[MarkyB]

Sorry guys, I am now out of my VBA comfort zone and struggling slightly.

Changed Dim to Static and functions better but not quite right.

If No is clicked on the MsgBox, the code correctly skips down to the setting
of the buttons but once it has hit the line
btnIntProduct.Value = True
it switches to the other _Click() function and then brings up the MsgBox for
that function.
If Yes is clicked on the second MsgBox then the current selection(s) are
cleared, which I don't want to happen. If No is clicked then the current
selection(s) are not cleared and the radio buttons are back to front!

Mark

 

[Bob Phillips]

Then in that case, declare a singe fReEntry public variable before any of
the procedures and remove the other declarations.


Public fReEntry As Boolean

'other macros

Private Sub btnIntGroup_Click()
If Not fReEntry Then

fReEntry = True

'... this proc code

fReEntry = False
End If
End Sub


Private Sub btnIntProduct_Click()
If Not fReEntry Then

fReEntry = True

'... this proc code

fReEntry = False
End If
End Sub

--
---
HTH

Bob


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

 

[MarkyB]

Bob/Richard many thanks, all works as I would expect now. And I have learnt
more.
Mark