Userform Optionboxes - use VBA to set which ones are selected when userform opens

[AmandaCatRuth]

I have a worksheet that tracks jobs per month. I have a button used to create a report.

This button opens a userform. There are two frames of optionboxes. One contains the months January-December. The other contains years, currently 2010-2016.

I want the optionboxes for the current month and year to be automatically selected when the userform opens, as those will be the ones most used. The following works perfectly well:

Private Sub userform_activate()


If Month(Now) = 1 Then MonthButton1.Value = True
If Month(Now) = 2 Then MonthButton2.Value = True
If Month(Now) = 3 Then MonthButton3.Value = True
If Month(Now) = 4 Then MonthButton4.Value = True
If Month(Now) = 5 Then MonthButton5.Value = True
If Month(Now) = 6 Then MonthButton6.Value = True
If Month(Now) = 7 Then MonthButton7.Value = True
If Month(Now) = 8 Then MonthButton8.Value = True
If Month(Now) = 9 Then MonthButton9.Value = True
If Month(Now) = 10 Then MonthButton10.Value = True
If Month(Now) = 11 Then MonthButton11.Value = True
If Month(Now) = 12 Then MonthButton12.Value = True


If Year(Now) = 2010 Then YearButton2010.Value = True
If Year(Now) = 2011 Then YearButton2011.Value = True
If Year(Now) = 2012 Then YearButton2012.Value = True
If Year(Now) = 2013 Then YearButton2013.Value = True
If Year(Now) = 2014 Then YearButton2014.Value = True
If Year(Now) = 2015 Then YearButton2015.Value = True
If Year(Now) = 2016 Then YearButton2016.Value = True


End Sub

I can't help but think I don't need 19 rows of "if" statements to accomplish this - it's sloppy code. Month(Now) and Year(Now) both produce an integer value. I should be able to link that to the button name and have one small slice of code for the month and another for the year. However, repeated slams of my head against my desk have not produced the method by which to do so.

Could someone please advise me on how to tidy this up?

Thank you very much.

PS - Merry Christmas.

 

[My Aswer Is This]

Try this:

Private Sub UserForm_Initialize()
ComboBox1.Value = Format(Now(), "mmm")
End Sub

 

[Alan_P]

Hi,

Is something like this what your after?

MonthButton = "MonthButton" & Month(Now)

Me.Controls(MonthButton) = True

YearButton = "YearButton" & Year(Now)

Me.Controls(YearButton) = True

Hope this helps,
Cheers,
Alan.

 

[Alan_P]

or even shorter...

Me.Controls("MonthButton" & Month(Now)) = True
Me.Controls("YearButton" & Year(Now)) = True

 

[My Aswer Is This]

O yes. And this for the combobox for the year:

Private Sub UserForm_Initialize()
ComboBox1 = Format(Date, "yyyy")
ComboBox2.Value = Format(Now(), "mmm")

End Sub

The first one is for year second one for Month

 

[AmandaCatRuth]

or even shorter...

Me.Controls("MonthButton" & Month(Now)) = True
Me.Controls("YearButton" & Year(Now)) = True

This worked perfectly, thank you!

Now how do I shorten this...

If MonthButton1.Value = True Then CallMonth = 1
If MonthButton2.Value = True Then CallMonth = 2
If MonthButton3.Value = True Then CallMonth = 3
.
.
.

 

[Alan_P]

No prob

I assume this bit is required after some selection has been made by the user and not on the UserForm Initilize?

try

For i = 1 To 12
    If Me.Controls("MonthButton" & i) = True Then
        CallMonth = i
    End If
Next

 

[AmandaCatRuth]

No prob

I assume this bit is required after some selection has been made by the user and not on the UserForm Initilize?

try

For i = 1 To 12
    If Me.Controls("MonthButton" & i) = True Then
        CallMonth = i
    End If
Next

Correct. Again, perfect. Thank you very much!

 

[Alan_P]

No prob happy to help

 

[RoryA]

Just FYI, you may as well exit the loop once you found a True value:

For i = 1 To 12
    If Me.Controls("MonthButton" & i) = True Then
        CallMonth = i
        Exit For
    End If
Next