Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
WorkSheet Change Event - Need Help With Coding Syntax
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
WorkSheet Change Event - Need Help With Coding Syntax
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 -- HTH, Barb Reinhardt "BJ" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
WorkSheet Change Event - Need Help With Coding Syntax
Thanks Barb - works perfectly!
"Barb Reinhardt" wrote: 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 -- HTH, Barb Reinhardt "BJ" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cell value change to trigger macro (worksheet change event?) | Excel Programming | |||
Worksheet change event I think? | Excel Programming | |||
Change Cell from Validated List Not Firing Worksheet Change Event | Excel Programming | |||
Worksheet Change Event | Excel Programming |