WorkSheet Change Event - Need Help With Coding Syntax

[BJ]

1. User selects OptionButton1 (New) or OptionButton2 (Renew)
2. User inputs volumes of one to three products
3. Need to turn on the appropriate OptionButton (NewProd1, NewProd2, etc.)

Here is my code:

Private Sub Worksheet_Change(ByVal Target As Range)
With Worksheets("Input")
If Not Intersect(Target, .Range("FROI_Vol")) Is Nothing Then
If .Range("FROI_Vol").Value <> 0 Then
If NewProduct.Value = True Then
FROI.Value = True And FROI_Renew.Value = False
Else
FROI.Value = False And FROI_Renew.Value = True
End If
End If
End If
End With
End Sub

I can get the OptionButtons for the 'New' products to turn on, but the
OptionButtons for the 'Renew' products aren't turning on so maybe I'm not
using the ELSE statement appropriately?

Any help appreciated.

BJ

 

[Barb Reinhardt]

I've not used option buttons at all, but I'll give it a try

Private Sub Worksheet_Change(ByVal Target As Range)
With Worksheets("Input") 'If the worksheet that's changing is Input,
' you can use "With ME"
If Not Intersect(Target, .Range("FROI_Vol")) Is Nothing Then
If .Range("FROI_Vol").Value <> 0 Then
If NewProduct.Value = True Then
FROI.Value = True
FROI_Renew.Value = False 'do you need this.
'If I set one Option
Button to True
'The others change to false
Else
FROI.Value = False
FROI_Renew.Value = True
End If
End If
End If
End With
End Sub

 

[BJ]

Thanks Barb - works perfectly!

I've not used option buttons at all, but I'll give it a try

Private Sub Worksheet_Change(ByVal Target As Range)
With Worksheets("Input") 'If the worksheet that's changing is Input,
' you can use "With ME"
If Not Intersect(Target, .Range("FROI_Vol")) Is Nothing Then
If .Range("FROI_Vol").Value <> 0 Then
If NewProduct.Value = True Then
FROI.Value = True
FROI_Renew.Value = False 'do you need this.
'If I set one Option
Button to True
'The others change to false
Else
FROI.Value = False
FROI_Renew.Value = True
End If
End If
End If
End With
End Sub