1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

  3. 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

Discussion in 'Ask an Excel Question' started by Ateeb Ali, Dec 20, 2018.

  1. Ateeb Ali

    Ateeb Ali Member

    Messages:
    91
    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

    Attached Files:

  2. pecoflyer

    pecoflyer Active Member

    Messages:
    291
    Cross post reported
  3. Ateeb Ali

    Ateeb Ali Member

    Messages:
    91
    This is fresh post, when not getting answer, i have crossed post to another forum later
  4. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    2,105
    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
    Ajesh likes this.
  5. Ateeb Ali

    Ateeb Ali Member

    Messages:
    91
    Noted and deleting the post
  6. Peter Bartholomew

    Peter Bartholomew Well-Known Member

    Messages:
    584
    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?
  7. Ateeb Ali

    Ateeb Ali Member

    Messages:
    91
    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 (vb):

    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: Dec 20, 2018
  8. Belleke

    Belleke Active Member

    Messages:
    507
    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 (vb):
    ActiveSheet.Shapes.Range(Array("Option Button 1")).Characters.Text = Range("A1")
    does the same as
    Code (vb):
    ActiveSheet.Shapes.Range(Array("Option Button 1")).Select
    Selection.Characters.Text = Range("A1")
  9. Ateeb Ali

    Ateeb Ali Member

    Messages:
    91
    Thanks but your code not working dear
  10. Peter Bartholomew

    Peter Bartholomew Well-Known Member

    Messages:
    584
    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.

    Attached Files:

  11. Ateeb Ali

    Ateeb Ali Member

    Messages:
    91
    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
  12. pecoflyer

    pecoflyer Active Member

    Messages:
    291
  13. Ateeb Ali

    Ateeb Ali Member

    Messages:
    91
  14. Peter Bartholomew

    Peter Bartholomew Well-Known Member

    Messages:
    584
    Sometimes option buttons are just plain boring …

    Attached Files:

    Ajesh likes this.
  15. Ajesh

    Ajesh Active Member

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

    Code (vb):

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

    Ateeb Ali Member

    Messages:
    91
    Thanks dear, this is fine but I want to hide this button when cell value is blank
  17. Peter Bartholomew

    Peter Bartholomew Well-Known Member

    Messages:
    584
    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 (vb):
    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 (vb):
    Sheet1.Shapes(1).Visible = msoFalse
    Last edited: Dec 21, 2018
  18. Ateeb Ali

    Ateeb Ali Member

    Messages:
    91
    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 (vb):

    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: Dec 22, 2018
  19. Peter Bartholomew

    Peter Bartholomew Well-Known Member

    Messages:
    584
    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 (vb):
    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?

    Attached Files:

    Thomas Kuriakose likes this.
  20. Ateeb Ali

    Ateeb Ali Member

    Messages:
    91
    This is perfect, you really awesome, thanks a lot

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

Share This Page