PDA

View Full Version : Solved: Highlighting Option Buttons



phendrena
05-26-2009, 09:56 AM
Hi,

i'm after some suggestions on how to do the following in a more efficent manner :-
Private Sub optJan_Click()
With Me
.optJan.Font.Bold = True
.optFeb.Font.Bold = False
.optMar.Font.Bold = False
'--- etc to Dec
end with
End Sub

When the user clicks the option button it is changed to a 'Bold' font and the others are set to a 'normal' font. Instead of coding 12 seperate sets is there a better way to do this?

Thanks,

Bob Phillips
05-26-2009, 10:51 AM
Private Sub optJan_Click()
Call HighlightButton(1)
End Sub

Private Sub optFeb_Click()
Call HighlightButton(2)
End Sub

Private Sub HighlightButton(MonthNum As Long)
With Me
.optJan.Font.Bold = MonthNum = 1
.optFeb.Font.Bold = MonthNum = 2
.optMar.Font.Bold = MonthNum = 3
'--- etc to Dec
End With
End Sub

Kenneth Hobs
05-26-2009, 11:32 AM
Of course xld's example is a good way to go.

In my approach, using the standard names:
In a Module:
Sub UnBoldBoldOBs(obj As String)
Dim i As Integer
With ActiveSheet
For i = 1 To 3
.OLEObjects("OptionButton" & i).Object.Font.Bold = False
Next i
.OLEObjects(obj).Object.Font.Bold = True
End With
End Sub
In the Sheet's code:
Private Sub OptionButton1_Click()
UnBoldBoldOBs OptionButton1.Name
End Sub

Private Sub OptionButton2_Click()
UnBoldBoldOBs OptionButton2.Name
End Sub

Private Sub OptionButton3_Click()
UnBoldBoldOBs OptionButton3.Name
End Sub

For your method of option button names using my method, in a Module:
Sub UnBoldBoldOBs2(obj As String)
Dim i As Integer
With ActiveSheet
For i = 1 To 3
.OLEObjects("opt" & Format(DateSerial(2000, i, 1), "mmm")).Object.Font.Bold = False
Next i
.OLEObjects(obj).Object.Font.Bold = True
End With
End Sub

In the Sheet's code:
Private Sub optJan_Click()
UnBoldBoldOBs2 optJan.Name
End Sub

Private Sub optFeb_Click()
UnBoldBoldOBs2 optFeb.Name
End Sub

Private Sub optMar_Click()
UnBoldBoldOBs2 optMar.Name
End Sub

phendrena
06-01-2009, 03:15 AM
Thank you both for the replies.
Problem solved very nicely. :)