Solved: Copy Option Button Caption to Cell

[MLI]

Hi All

Hope somebody can help me. I would like to copy a Option Button's Caption into a Excel Database cell when selected. I want to populate the cells in Row10 by just selecting one of 4 Option Buttons (Button1 = "Now", Button2 = "Weekly", Button3 = "2 Weekly" & Button4 = "Monthly")
Below is part of the code I've used to initialize the form and I don't know if the "ws.Cells(iRow, 10).Value = Me.OptionButton1.Value" is correct and also what the "Private Sub OptionButton1_Click()" should look like:oNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0cm; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:Arial; mso-fareast-font-family:"Times New Roman"; mso-bidi-font-family:"Times New Roman";} @page Section1 {size:612.0pt 792.0pt; margin:72.0pt 90.0pt 72.0pt 90.0pt; mso-header-margin:36.0pt; mso-footer-margin:36.0pt; mso-paper-source:0;} div.Section1 {page:Section1;} -->

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.txtAssessor.Value
ws.Cells(iRow, 2).Value = Me.txtDate.Value
ws.Cells(iRow, 3).Value = Me.txtJSATitle.Value
ws.Cells(iRow, 4).Value = Me.txtWorkCentre.Value
ws.Cells(iRow, 5).Value = Me.txtPlanNo.Value
ws.Cells(iRow, 6).Value = Me.txtJSARiskScore.Value
ws.Cells(iRow, 7).Value = Me.txtKeyStep.Value
ws.Cells(iRow, 8).Value = Me.txtCorrAct.Value
ws.Cells(iRow, 9).Value = Me.txtRiskScore.Value
ws.Cells(iRow, 10).Value = Me.OptionButton1.Value

'clear the data
Me.txtAssessor.Value = ""
Me.txtDate.Value = ""
Me.txtJSATitle.Value = ""
Me.txtWorkCentre.Value = ""
Me.txtPlanNo.Value = ""
Me.txtJSARiskScore.Value = ""
Me.txtKeyStep.Value = ""
Me.txtCorrAct.Value = ""
Me.txtRiskScore.Value = ""
Me.OptionButton1.Value = ""
Me.txtAssessor.SetFocus


Thanks

[Kenneth Hobs]

Welcome to the forum!

You probably want to use the Caption property rather than Value. The Tab property can hold some other values if needed.
e.g.
ws.Cells(iRow, 10).Value = OptionButton1.Caption



You probably don't need to clear the values. Unload Me should work unless you have some other need.

[MLI]

Thanks Kenneth, much appreciated.

[MLI]

Hi Kenneth.

The code you have sent me works fine with just one option button but I would like to have a choice out of 5 buttons... any suggestions?

[Kenneth Hobs]

Not sure what you mean by "choice". You can add other buttons if needed. If you need to get the value an do something, you can check the Value property for True. Select Case is typically used for that scenario.

[mikerickson]

You could put this in the Change event for one of your OptionButtons.
Since changing the option from Button2 to Button3 also fires Button1's Change event, one Change routine will take care of all the option buttons.
Private Sub OptionButton1_Change()
Dim i As Long
For i = 1 To 5
With Me.Controls("OptionButton" & i)
If .Value Then Cells(iRow, 10).Value = .Caption
End With
Next i
End Sub

[MLI]

Hi mikerickson

Thank you for your input, I have tried the code but got the following Error Message when selecting "OptionButton1".

"Cells(iRow, 10).Value = .Caption"

Error - Run-Time Error '1004'
Application Defined or Object Defined Error.

[nst1107]

You are probably getting the error because "Cells(iRow, 10)" isn't a valid range. Try referencing a worksheet object, such as (referring to your example at the top) "ws.Cells(iRow, 10)".

[MLI]

Hi nst1107

The "Error" is sorted but when I press "OptionButton1" the value gets added to column 10 every time regardless which button I select.

[nst1107]

Just a guess, but check to see that all your option buttons are in the same group. (Check the properties of the option buttons in the VBE) Option buttons have to be in the same group in order that, when you set one to true, the rest are set to false. If you don't want this, of course you could still use option buttons, but check boxes would be better.

[mikerickson]

It shouldn't behave like that. Do you have all the dots from the code above.

Try this,
Private Sub OptionButton1_Change()
Dim i As Long
For i = 1 To 5
If Me.Controls("OptionButton" & i).Value Then
Cells(iRow, 10).Value = Me.Controls("OptionButton" & i).Caption
End If
Next i
End Sub

[MLI]

Hi mikerickson

If you have the time I would appreciate if you can have a look at my code. I am totaly lost at this moment.


Private Sub cmdAddRecord_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("JSA Data")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.txtAssessor.Value
ws.Cells(iRow, 2).Value = Me.txtDate.Value
ws.Cells(iRow, 3).Value = Me.txtJSATitle.Value
ws.Cells(iRow, 4).Value = Me.txtWorkCentre.Value
ws.Cells(iRow, 5).Value = Me.txtPlanNo.Value
ws.Cells(iRow, 6).Value = Me.txtJSARiskScore.Value
ws.Cells(iRow, 7).Value = Me.txtKeyStep.Value
ws.Cells(iRow, 8).Value = Me.txtCorrAct.Value
ws.Cells(iRow, 9).Value = Me.txtRiskScore.Value
ws.Cells(iRow, 10).Value = OptionButton1.Caption

'clear the data
Me.txtAssessor.Value = ""
Me.txtDate.Value = ""
Me.txtJSATitle.Value = ""
Me.txtWorkCentre.Value = ""
Me.txtPlanNo.Value = ""
Me.txtJSARiskScore.Value = ""
Me.txtKeyStep.Value = ""
Me.txtCorrAct.Value = ""
Me.txtRiskScore.Value = ""
End Sub


Private Sub UserForm_Activate()

With Application
Me.Top = .Top
Me.Left = .Left
Me.Height = .Height
Me.Width = .Width
End With
End Sub


Private Sub cmdClose_Click()
Unload Me
End Sub


Private Sub OptionButton1_Change()
Dim i As Long
For i = 1 To 6
If Me.Controls("OptionButton" & i).Value Then
Cells(iRow, 10).Value = Me.Controls("OptionButton" & i).Caption
End If
Next i
End Sub

[mikerickson]

The iRow variable is scoped to only the cmdAddRecord routine. In the OptionButton routine, it would always be 0, which will cause an error.
(Using Option Explicit by setting the VBEditor>Preferences>Edit to Require Variable Declaration helps prevent scoping confusions.)

If you make iRow a module wide variable, then the cell will be filled at the moment that any Option Button is selected.

If you delete the OptionButton1_Change routine after moving
For i = 1 To 6
If Me.Controls("OptionButton" & i).Value Then
Cells(iRow, 10).Value = Me.Controls("OptionButton" & i).Caption
End If
Next iinto the cmdAddRecord routine, the declaration of iRow can stay where it is, but the cell will be filled only when the command button is pushed.

[MLI]

I would like to thank everybody that helped me with this problem. I truly appreciate you time and efford, the code is running 100%.