View Full Version : Forms & OptionButtons

07-13-2006, 05:41 AM
Hi,I have a form that I use to record tasks. On the form is a set of option buttons and what I want to be able to do is click a button and the value of the checked optionbutton is copied onto a worksheet.I have the form created, and the code to copy the value is already in place. All I need is a good way to be able to recognise which option button is selected. All I have at the moment for this is If OptionButton1.value = True then Value = ...., but that seems a little bit of a rubbish way to do it.Any ideas?

07-13-2006, 06:12 AM
Have you got your option buttons in a "Frame" control?
If so it's on click event procedure would give you the value of the option buton pressed.

07-13-2006, 07:05 AM
Ok...I've added all the option buttons into a frame control, but I can't work out how to get the value of the option button pressed.

07-13-2006, 07:16 AM
Well I am not that familiar with Excel forms and Frame control, but if it is the same as access if you place some VBA in the Frame Control "After Update" Event Procedure like
msgbox frame0.value - assuming your frame is frame0, change the number to suit.
then it should give you the value of the button clicked based on how you set the buttons in the frame.

07-13-2006, 07:23 AM
Ok, there's no AfterUpdate event procedure for Frames, but Frame1_Exit is an option. However, there's also no Frame1.Value option....

07-13-2006, 08:08 AM
Sorry, I mislead you there, it looks as if Excel Option groups just aren't as good as Access ones.
I have looked at the Excel VB help and my Books, they all show examples using the true or false which doesn't seem very user friendly, but it appears to be the only way. My book VBA Fundementals shows a for next loop looping through the frame's buttons check for "true".
Wouldn't like to do it in Access would you?

07-13-2006, 09:13 AM
Have you looked at the Option Button's "On Click" event procedure, that can be used to trigger events like this

Private Sub OptionButton1_Click()
If OptionButton1 Then Cells.Interior.Color = RGB(0, 0, 255)
End Sub

Private Sub OptionButton2_Click()
If OptionButton2 Then Cells.Interior.Color = RGB(0, 255, 0)
End Sub

Private Sub OptionButton3_Click()
If OptionButton3 Then Cells.Interior.Color = RGB(255, 0, 0)
End Sub

You could put the value of each button in your cell using that, like this -

Private Sub OptionButton1_Click()
If OptionButton1 Then ActiveCell.Value = 1
End Sub

07-13-2006, 09:58 AM
Here's an alternative, but any better?:dunno

Private Sub CommandButton1_Click()
Dim c
For Each c In Me.Controls
If Left(c.Name, 6) = "Option" Then
If c.Value = True Then
Select Case Right(c.Name, 1)
Case 1
MsgBox c.Name
Case 2
MsgBox c.Name
Case 3
MsgBox c.Name
End Select
End If
End If
End Sub