Option Button Caption to be picked from Cell Vlue

[Ateeb Ali]

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

 

[pecoflyer]

Cross post reported

 

[Ateeb Ali]

This is fresh post, when not getting answer, i have crossed post to another forum later

 

[bosco_yip]

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

 

[Ateeb Ali]

Noted and deleting the post

 

[Peter Bartholomew]

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]

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

​

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

 

[Belleke]

Lesson nr. one in VBA.
Avoid using Select and /or Activate, it is rarely required and it slows down your code big time.
This

ActiveSheet.Shapes.Range(Array("Option Button 1")).Characters.Text = Range("A1")

does the same as

ActiveSheet.Shapes.Range(Array("Option Button 1")).Select
Selection.Characters.Text = Range("A1")

 

[Ateeb Ali]

Thanks but your code not working dear

 

[Peter Bartholomew]

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.

 

[Ateeb Ali]

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

 

[pecoflyer]

Take a moment to read and understand this https://www.excelguru.ca/content.php?184

 

[Ateeb Ali]

Take a moment to read and understand this https://www.excelguru.ca/content.php?184

read it, thanks

 

[Peter Bartholomew]

Sometimes option buttons are just plain boring …

 

[Ajesh]

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

ActiveSheet.Shapes.Range(Array("Option Button 1")).TextFrame.Characters.Text = Range("A1").Value

Thanks/Ajesh

 

[Ateeb Ali]

Thanks dear, this is fine but I want to hide this button when cell value is blank

 

[Peter Bartholomew]

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.

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

Sheet1.Shapes(1).Visible = msoFalse

 

[Ateeb Ali]

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;

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

 

[Peter Bartholomew]

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.

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?

 

[Ateeb Ali]

This is perfect, you really awesome, thanks a lot

All previous post have been done already, thanks to the forums.