Solved: How to reference an option box in a checkbox?

[Hamond]

Hi

Found this forum helpful to solve a useform problme recently but now I have another.

I have a option box in a userform called "Monthly_Click", (there are more than one). In the same userform I also have a checkbox called "YoYDifference_Click"

When this checkbox is selected I want to do a conditional code run based on which option box as been selected similar to the following:

[VBA] Sub YoYDifference_Click1() 'checkbox select code
If Monthly_Click = True Then 'option box
Call Transform_Monthly_to_YoY_Difference 'code in module
If Quarterly_Click = True Then 'option box
Call Transform_QTR_to_YoY_Difference 'code in module
End If
End If
End Sub
[/VBA]

Running this is currently giving me a compile error: Expected function or variable.

Is it possible to do what I need with the approach I'm attempting or is there another way?

Many Thanks,

Hamond

[GTO]

Hi

...Running this is currently giving me a compile error: Expected function or variable.

Is it possible to do what I need with the approach I'm attempting or is there another way?

Many Thanks,

Hamond

Hi Hamond,

I am signing out, but just spotted your question and thought I'd give a quick stab at it.

Presuming you want the code to run only upon ticking the checkbox (not when clearing/un-ticking it, you'd want to check its value before proceeding. Also, I'm guessing that the choice is between Monthly and Quarterly, in which case you don't want the second IF nested in the first. Maybe:

Private Sub YoYDifference_Click()
 
    If Me.YoYDifference.Value Then
        If Monthly.Value Then
            Call Transform_Monthly_to_YoY_Difference
        ElseIf Quarterly.Value Then
            Call Transform_QTR_to_YoY_Difference
        End If
    End If
End Sub

I do not see what line in your present code would toss the error.

Hope that helps,

Mark

[Hamond]

Thanks Mark

After putting me option boxes into a frame, the following seems to work:

[VBA]
Sub YoYDifference_Click()
If Monthly.Value = True Then
Call Transform_Monthly_to_YoY_Difference
ElseIf Quarterly.Value = True Then
Call Transform_QTR_to_YoY_Difference
End If
End Sub
[/VBA]

However I actually I two more additional option boxes one for daily data and other for weekly data, i.e I need four conditions in the code. So presumably I need mutiple if or some kind of select statement?

Also I am little confused, why does the first line work and the second line not work?

If Monthly.Value = True Then
If Monthly_Click.value = True Then

I though the second line would be the formal way to reference?

Thanks,

Hamond

[GTO]

...However I actually I two more additional option boxes one for daily data and other for weekly data, i.e I need four conditions in the code. So presumably I need mutiple if or some kind of select statement?

You could use more ElseIf's, or a Select Case like:

    Select Case True
    Case OptionButton1.Value
        MsgBox "Option 1 was selected"
    Case OptionButton2.Value
        MsgBox "Option 2 was selected"
    Case OptionButton3.Value
        MsgBox "Option 3 was selected"
    Case OptionButton4.Value
        MsgBox "Option 4 was selected"
    End Select

...Also I am little confused, why does the first line work and the second line not work?

If Monthly.Value = True Then
If Monthly_Click.value = True Then

I though the second line would be the formal way to reference?

'Monthly' is the Object, in this case, an Option button. Monthly_Click is the name of the event procedure.

Hope that helps,

Mark

[shrivallabha]

The Compiler Error probably comes as the 'If' Loop does not handle the 'Else' part i.e.
[VBA]If Monthly_Click = False Then[/VBA]
which compiler searches and does not find.

[Hamond]

Great. That makes sense. Thanks for your help.

Hamond