Option buttons should display "yes" and "no&a

[karambos]

Hi,

presently, I have two option buttons whose captions are YES and NO respectively. Without any coding (because I don't know how to code properly) my option buttons are giving me TRUE and FALSE in their linked cells when I click on them.

However, I don't want TRUE or FALSE to be printed. I would like YES or NO to be printed.

Could you please give me a hint as to how one would go about changing this?

(10 minutes later...) I guess my question would be "How do you bind option buttons to values"

Thanks

karambos

 

[Von Pookie]

Are the buttons on a userform or on the spreadsheet?

 

[karambos]

Re: Option buttons should display "yes" and "

Hello Von Pookie,

thanks for getting back to me. I don't know what a userform is. I can explain how I got my option buttons, if that helps.

If you go to VIEW > TOOLBARS > CONTROL TOOLBAR you get a toolbar called "Control Toolbox" and I got my option buttons from there. Then, providing you're in something called "DESIGN MODE" (by clicking a sort of blue triangular thing in the toolbar), you can right click on the option button themselves to see the PROPERTIES. There is a field called "Value" and it can be set to TRUE or FALSE but it can't be set to YES and NO unfortunately. But the way I've done by combo boxes is to go to a field called "ListFillRAnge" and point that to some cells on another worksheet. I reckon it must be possible to do that to option buttons but I don't know how.

Thanks for any further help

karambos

 

[Von Pookie]

Hi!

You'll need some code to do what you're wanting.

Try this, it seemed to work for me:

While in design mode, right-click on each button and go to the properties. Make sure they both have the same "Group name" and "linked cell".

Then right-click on one of the buttons and select "View Code."
If there is any code there, just delete it and use the following:

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> OptionButton1_Click()

<SPAN style="color:#00007F">If</SPAN> OptionButton1.Value = "True" <SPAN style="color:#00007F">Then</SPAN>
    Range("A1").Value = "Yes"
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> optionbutton2_click()

<SPAN style="color:#00007F">If</SPAN> OptionButton2.Value = "True" <SPAN style="color:#00007F">Then</SPAN>
    Range("A1").Value = "No"
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

If your buttons are not linked to cell A1, simply change that in the code above to whatever cell you need.

Then exit design mode and test out your buttons. It should make the linked cell say "yes" or "no"

Hope it helps,

 

[karambos]

Re: Option buttons should display "yes" and "

Many thanks for the great effort. It kind of works.

This is what's happening. I cut and pasted your code and changed the cell I linked to. Still in design mode I changed both VALUEs to False. So neither option button is marked. I then go out of design mode.

Then I click on the "YES" option button and the "NO" option button gets marked black and the linked cell says "no". If I immediately click on the "YES" option button again, the NEITHER option buttons are marked black and the linked cell says FALSE in big letters. A third click on the "YES" option button and I get the initial result.

However, if, whilst neither option buttons are marked black and the linked cell says FALSE, I click on the "NO" option button, then the "YES" option button is marked black and the linked cell says "Yes".

I looked at the code and I added two lines. I'll paste it here:

Private Sub EskalationYesOptionButton_click()

If EskalationYesOptionButton.Value = "True" Then
   EskalationNoOptionButton.Value = "False"
    Range("c53").Value = "Yes"
End If

End Sub

Private Sub EskalationNoOptionButton_click()

If EskalationNoOptionButton.Value = "True" Then
   EskalationYesOptionButton.Value = "False"
    Range("c53").Value = "No"
End If

End Sub

You can see what I've added. I just thought I'd better make it absolutely clear to EXCEL. The results did not change.

I'm grateful for all you can do.

Karambos

 

[Von Pookie]

I did it again, and got the same results you did.

I just got it to work for me, though. This time, I simply left the "Linked Cell" property blank. I think I goofed there before.

When I left it blank and tried it, it worked.

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> EskalationYesOptionButton_Click()

    <SPAN style="color:#00007F">If</SPAN> EskalationYesOptionButton.Value = <SPAN style="color:#00007F">True</SPAN> <SPAN style="color:#00007F">Then</SPAN>
        Range("C53").Value = "Yes"
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> EskalationNoOptionButton_Click()

    <SPAN style="color:#00007F">If</SPAN> EskalationNoOptionButton.Value = <SPAN style="color:#00007F">True</SPAN> <SPAN style="color:#00007F">Then</SPAN>
        Range("C53").Value = "No"
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Hope it helps,

 

[karambos]

Re: Option buttons should display "yes" and "

Hello von Pookie,

has anyone ever told you that ray: YOU ROCK! ray:

Many thanks and I'll leave this thread with those famous words: keep on EXCELLING!

karambos

 

[ckaten]

Re: Option buttons should display "yes" and "no&a

What if I want to do this same thing but not link it to one cell, but instead have it find the next empty row and add to that line?

Private Sub commandbutton1_click()
Dim RowCount As Long
    Dim ctl As Control
    Dim Name As String

   Name = TextBox1.Value
   RowCount = Worksheets("Sheet1").Range("A1").CurrentRegion.Rows.Count
    With Worksheets("Sheet1").Range("A1")
        .Offset(RowCount, 0).Value = TextBox1.Value
        .Offset(RowCount, 1).Value = ComboBox1.Value
        .Offset(RowCount, 2).Value = ComboBox2.Value
        .Offset(RowCount, 3).Value = OptionButton1.Value
        .Offset(RowCount, 4).Value = OptionButton2.Value
        
    End With

For Each ctl In Me.Controls
If ctl.Tag = "clear" Then
ctl.Value = ""
End If

Next ctl

End Sub

This code does what I need, but I cannot change Optionbutton1 and Optionbutton2 to true="X" and false = ""