View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
MarkyB MarkyB is offline
external usenet poster
 
Posts: 12
Default Reset of radio button values without calling _Click() function

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