• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Option Button Caption to be picked from Cell Vlue

Ateeb Ali

Member
Dear Sir
In attached file, I just need a way out that option button caption / name should be pick from cell values;

Like option button 1 should be linked with Cell A1 accordingly Option Button 7 linked with Cell A7
 

Attachments

bosco_yip

Excel Ninja
Please Don't Cross-Posting.

Generally, it is considered poor practice to cross post. That is to post the same question on several forums in the hope of getting a response quicker.

If you do cross-post, please put the other forum website link in your post.

Also if you have cross-posted and get an answer elsewhere, have the courtesy of posting the answer here so other readers can learn from the answer also, as well as stopping people wasting their time on your answered question.

Regards
Bosco
 

Peter Bartholomew

Well-Known Member
Since the post has not actually been deleted:
To the best of my knowledge, there is no way of changing the text on a control without VBA. What is possible however, is placing the control precisely over a cell. The text is removed from the transparent option box, allowing text from the underlying cell to show through. Conditional formatting can also be used to indicate status, reinforcing the message of the little black dot.

Did you get any useful feedback from your cross-post?
 

Ateeb Ali

Member
Following code working fine, just now one condition need to be added;
If cell values is A1:A2 only two option button should be displayed
If cell values is A1:A8, all 08 values to be displayed in option button

Screen Shot 2018-12-20 at 13.32.42.png
Code:
Sub optionbutton()
'
' optionbutton Macro
'

'
    ActiveSheet.Shapes.Range(Array("Option Button 1")).Select
    Selection.Characters.Text = Range("A1")
    ActiveSheet.Shapes.Range(Array("Option Button 2")).Select
    Selection.Characters.Text = Range("A2")
    ActiveSheet.Shapes.Range(Array("Option Button 3")).Select
    Selection.Characters.Text = Range("A3")
    ActiveSheet.Shapes.Range(Array("Option Button 4")).Select
    Selection.Characters.Text = Range("A4")
    ActiveSheet.Shapes.Range(Array("Option Button 5")).Select
    Selection.Characters.Text = Range("A5")
    ActiveSheet.Shapes.Range(Array("Option Button 6")).Select
    Selection.Characters.Text = Range("A6")
    ActiveSheet.Shapes.Range(Array("Option Button 7")).Select
    Selection.Characters.Text = Range("A7")

End Sub
 
Last edited by a moderator:

Belleke

Active Member
Lesson nr. one in VBA.
Avoid using Select and /or Activate, it is rarely required and it slows down your code big time.
This
Code:
ActiveSheet.Shapes.Range(Array("Option Button 1")).Characters.Text = Range("A1")
does the same as
Code:
ActiveSheet.Shapes.Range(Array("Option Button 1")).Select
Selection.Characters.Text = Range("A1")
 

Peter Bartholomew

Well-Known Member
I agree with Belleke. You have a starting point with something that works but some tidying up would be good.

Just incase anyone is interested, I have attached a non-macro workbook that uses conditional formatting and cell text changes to improve the visual impact of form option buttons.
 

Attachments

Ateeb Ali

Member
Sir, this is nice but my requirement is;

A list of 44 different colors with check boxes, user can select multiple colors from the list then they press button "selected"
All selected box text is copied as options buttons on next sheet where user can select any of one color in the range

this is example
 

Ajesh

Active Member
Not tested but i think you need to change it to:

Code:
ActiveSheet.Shapes.Range(Array("Option Button 1")).TextFrame.Characters.Text = Range("A1").Value
Thanks/Ajesh
 

Peter Bartholomew

Well-Known Member
It may be useful to iterate through your range of captions (by range name and cell index, never direct cell address) assigning text to your option buttons as required.
Code:
Sub OptionButton_Click()
Dim i As Long
    With Sheet1
        For i = 1 To 2
            .Shapes(i).TextFrame.Characters.Text = _
            Range("Captions").Cells(i).Value
        Next i
    End With
End Sub
To hide a button, simply set its visible property to msoFalse
Code:
Sheet1.Shapes(1).Visible = msoFalse
 
Last edited:

Ateeb Ali

Member
Sit this is not happening, what I did
I have set range name A1:A7 of Sheet3 as "Captions"
then click Alt+F11 then paste following code;

Code:
Sub OptionButton_Click()
Dim i As Long
    With Sheet1
        For i = 1 To 2
            .Shapes(i).TextFrame.Characters.Text = _
            Range("Captions").Cells(i).Value
        Next i
        Sheet1.Shapes(1).Visible = msoFalse
    End With
End Sub
It is not renaming options buttons as well as not hiding it
 
Last edited by a moderator:

Peter Bartholomew

Well-Known Member
Hi. I wouldn't expect that this is what you want but since it is always easier to start with something that works …

The main problem with my previous suggested code was that I had assumed the Option Buttons would be on Sheet1 (using the codename for the worksheet). Your workbook has one sheet with the codename Sheet2 and, rather confusingly, a tab name "Sheet3". Consequently, I decided to revert to ActiveSheet for the moment though this may need to be changed in future.

The code is triggered both by changing a cell within the 'Caption' range and by clicking any option button. Any button that corresponds to a blank cell in the caption list will be hidden.

Code:
Sub ResetButtons()
Dim i As Long, N As Long
Dim Text As String

    N = Range("Captions").Rows.Count
    With ActiveSheet
        For i = 1 To N
            If Range("Captions").Cells(i) = vbEmpty Then
                .Shapes(i).Visible = msoFalse
            Else
                Text = Range("Captions").Cells(i).Value
                .Shapes(i).TextFrame.Characters.Text = Text
                .Shapes(i).Visible = msoTrue
            End If
        Next i
       
    End With
End Sub
I also note that you have some quite complicated code designed to handle controls on a form. Did you write this?
 

Attachments

Top