option buttons in a userform

[mortgageman]

If an OptionButtonx_Clicks() is an event handler and (to my way of thinking at least) should only trigger when the event happens (i.e. the user clicks that button), why does the macro require an "If optionbuttonx" in the very top of the code? Shouldn't the code only be triggered if the button was clicked in the first place?

Gene, "The Mortgage Man", Klein

 

[Fat Cat]

You don't need an If OptionButton statement in an OptionButton_Click() routine. It will trigger execution and run its code as soon as it is clicked

eg:

Private Sub OptionButton1_Click()

MsgBox ("you clicked my 1st option button")
ComboBox1.Visible = True


End Sub

Private Sub OptionButton2_Click()

MsgBox ("you clicked my 2nd option button")
ComboBox1.Visible = False

End Sub

Thus, you can either use the OptionButton_Click() event to run code when it is clicked

or

you can leave out the OptionButton_Click() event altogether and then just get your other program code to check which option button has a TRUE or FALSE value (using If OptionButtonx.Value = True Then) in order to decide what to do.

 

[mortgageman]

Well I just took out the if optionbutton1 (and the end if as well) and there was no change in the operation, so I guess you are right. I wonder why Halface (who knows a lot in my estimation and showed me how to do these userforms in the first place) put it in the the first userform that he showed me how to do? Is it considered good form or something? Is there any use for it at all?

Gene, "The Mortgage Man", Klein

 

[Smitty]

Is it considered good form or something? Is there any use for it at all?

Since I don't see him around (probably out moose hunting), I'd venture to guess that Dan put together some code specific to your issue at the time. Most code and the way that you do it is largely driven by 1) personal preference and 2) the situation at hand. Code does have a habit of evolving as projects progress, so a lot of times a code snippet offered on the board is pretty broad based and may be pared down as the project gains scope on your end.

If you post the code in question, it might be clearer.

Smitty

 

[mortgageman]

Here are all four option buttons. I have made changes since Dan-HalfAce-Urton helped me with it. (In other words, I take the blame for any of the dumb stuff). The real question is why the "If OptionButtonx" in each of the four macros?

Gene, "The Mortgage Man", Klein


Private Sub OptionButton1_Change()
'// This choice is no advance payments
Dim AdvanceStart As Integer
AdvanceStart = Application.CountIf(Range("bopaid"), 1) + _
Application.CountIf(Range("bopaid"), 100) + 2
If OptionButton1 Then
'// save old state for undo capability
'// oldbopaid was decleared as public in module 1
For i = 1 To 360 ' change this hard code later
oldbopaid(i) = Range("bopaid")(i)
Next i
'//
Range("principle_coupon") = 0
Range("numofpayments_coupon") = 0
Range("interestsaved_coupon") = 0
Range("bopaid")(AdvanceStart - 1) = 1
Range("ExcelBankName") = Me.Controls("TextBox1").Value
Range("ExcelAccountNumber") = Me.Controls("TextBox2").Value
Unload UserForm3
ActiveWorkbook.Save
End If
End Sub
Private Sub OptionButton2_Change()
Dim AdvanceStart As Integer
AdvanceStart = Application.CountIf(Range("bopaid"), 1) + _
Application.CountIf(Range("bopaid"), 100) + 2
If OptionButton2 Then
'// save old state for undo capability
'// oldbopaid was decleared as public in module 1
For i = 1 To 360 ' change this hard code later
oldbopaid(i) = Range("bopaid")(i)
Next i
'//
Range("principle_coupon") = Range("AdvanceRange")(AdvanceStart)
Range("numofpayments_coupon") = 1
Range("bopaid")(AdvanceStart - 1) = 1
Range("bopaid")(AdvanceStart) = 100
Call bohide
Range("isaved").Value = Evaluate("=sumproduct(interestrange,--(bopaid = 100))")
Range("interestsaved_coupon") = Range("isaved").Text
Range("ExcelBankName") = Me.Controls("TextBox1").Value
Range("ExcelAccountNumber") = Me.Controls("TextBox2").Value
Call bohide
Unload UserForm3
Call Print_Prepay_Coupon
ActiveWorkbook.Save
End If
End Sub

Private Sub OptionButton3_change()
Dim AdvanceStart As Integer
AdvanceStart = Application.CountIf(Range("bopaid"), 1) + _
Application.CountIf(Range("bopaid"), 100) + 2
If OptionButton3 Then
'// save old state for undo capability
'// oldbopaid was decleared as public in module 1
For i = 1 To 360 ' change this hard code later
oldbopaid(i) = Range("bopaid")(i)
Next i
'//
Range("principle_coupon") = Range("AdvanceRange")(AdvanceStart + 1)
Range("numofpayments_coupon") = 2
Range("bopaid")(AdvanceStart - 1) = 1
Range("bopaid")(AdvanceStart) = 100
Range("bopaid")(AdvanceStart + 1) = 100
Call bohide
Range("isaved").Value = Evaluate("=sumproduct(interestrange,--(bopaid = 100))")
Range("interestsaved_coupon") = Range("isaved").Text
Range("ExcelBankName") = Me.Controls("TextBox1").Value
Range("ExcelAccountNumber") = Me.Controls("TextBox2").Value
Call bohide
Unload UserForm3
Call Print_Prepay_Coupon
ActiveWorkbook.Save
End If
End Sub

Private Sub OptionButton4_change()
Dim AdvanceStart As Integer
AdvanceStart = Application.CountIf(Range("bopaid"), 1) + _
Application.CountIf(Range("bopaid"), 100) + 2
If OptionButton4 Then
'// save old state for undo capability
'// oldbopaid was decleared as public in module 1
For i = 1 To 360 ' change this hard code later
oldbopaid(i) = Range("bopaid")(i)
Next i
'//
Range("principle_coupon") = Range("AdvanceRange")(AdvanceStart + 2)
Range("numofpayments_coupon") = 3
Range("bopaid")(AdvanceStart - 1) = 1
Range("bopaid")(AdvanceStart) = 100
Range("bopaid")(AdvanceStart + 1) = 100
Range("bopaid")(AdvanceStart + 2) = 100
Call bohide
Range("isaved").Value = Evaluate("=sumproduct(interestrange,--(bopaid = 100))")
Range("interestsaved_coupon") = Range("isaved").Text
Range("ExcelBankName") = Me.Controls("TextBox1").Value
Range("ExcelAccountNumber") = Me.Controls("TextBox2").Value
Call bohide
Unload UserForm3
Call Print_Prepay_Coupon
ActiveWorkbook.Save
End If
End Sub

 

[Smitty]

Wow Gene,

Looks like Dan put a lot of work in on this one, so I'm gonna' defer to him.

// oldbopaid was decleared as public in module 1

And all of the named ranges...

Smitty

 

[Fat Cat]

I think I have figured it out.

You have an OptionButton1_Change() event which will fire off each time there is any change in the status of the OptionButton1.

When you click on OptionButton1, its status will CHANGE from unselected to selected (ie: from FALSE to TRUE).

However, when you click on OptionButton2, another change occurs in the status of OptionButton1, this time from selected to unselected.

Thus a Change event occurs whenever it is selected or deselected.

The use of the If OptionButton1 Then statement allows you to add code to execute when the button is selected and you can also insert an Else statement to execute different code if the change is to deselect the option button.

eg:

Private Sub OptionButton1_Change()
If OptionButton1 Then
    Cells(4, 2) = "true"
Else
    Cells(4, 2) = "false"
End If
End Sub

Thus HalfAce is a well clever cookie afterall.

cheers

Mark

 

[mortgageman]

Private Sub OptionButton1_Change()
If OptionButton1 Then
Cells(4, 2) = "true"
Else
Cells(4, 2) = "false"
End If
End Sub

If I understand you correctly I could have code like this in my OptionButton1_Change macro

If OptionButton1
{code for button1}
Else if Optionbutton2
{code for button2}
Else if Optionbutton3
{code for button3}
End if
{rest of code}

Thereby eliminating the need for macros for every option button. Is that correct?

Gene, "The Mortgage Man", Klein

 

[Fat Cat]

Nope, sorry.

you should read it like;

Private Sub OptionButton1_Change()

If OptionButton1 = True Then
{code to do if option1 is selected}
Else
{code to do if option1 is deselected}
End If

The way you have written your example, the logic would work only if OptionButton1 was originally selected and you then selected one of the other buttons, or it was deselected and you then selected option1. It wouldn't trap any change in the other option buttons, for example if option2 was selected and you then clicked on option3. Since option1 didn't change status under this scenario the code wouldn't fire off at all.

 

[HalfAce]

Yes, Fat Cat has it right.
The statement "If OptionButton1 Then" is just short for saying
"If OptionButton1 = True Then", thus limiting your code to only execute when the optionbutton is actually ticked, and not just whenever its status changes.

And... just to give credit to whom it's due, Gene had the majority of his code already up & running by the time i saw it so he deserves most of the credit. I remember working on it but to be honest I don't remember exactly what we were doing to it. (Yeah, it's tough getting to be my age )

Deer & moose fear me!!! - (I, in turn, eat them!)