Results 1 to 16 of 16

get caption of optionbutton in userform from a cell in worksheet

Threaded View

  1. #1
    Registered User
    Join Date
    01-25-2012
    Location
    Brisbane
    MS-Off Ver
    Excel 2003
    Posts
    43

    get caption of optionbutton in userform from a cell in worksheet

    Hi Experts

    I have approximately 152 option buttions in 76 frames of a userform i.e. 2 optionbuttons in one frame and laid out as per the following example. I want to change the caption of all of them to what ever is sitting in range C14 to E89 of worksheet "Raj".
    e.g. I want the following
    In Frame1
    Caption of OptionButton1 = C14
    Caption of OptionButton2 = E14
    .
    In Frame2
    Caption of OptionButton1 = C15
    Caption of OptionButton2 = E15
    .
    .
    In Frame76
    Caption of OptionButton151 = C89
    Caption of OptionButton152 = E89

    By looking around I found the following code but it looks too complex for me and if someone can modify this to my needs and give me a brief explanation would really be app

    Dim OControl As Object 
    Dim i As Long 
    Const CTRL_PREFIX = "OPTIONBUTTON" 
     
    For Each OControl In UserForm1.Frame1.Controls 
         '*** SPECIFY OPTIONBUTTON CONTROL ****
        If UCase(TypeName(OControl)) = CTRL_PREFIX Then 
             '*** LIMIT SCOPE OF OPTIONBUTTON TO SELECT ***
            If UCase(Left(OControl.Name, Len(CTRL_PREFIX))) = CTRL_PREFIX Then 
                i = CLng(Mid(OControl.Name, Len(CTRL_PREFIX) + 1)) 
                 '**** IF STARTING CELL A1 IS NOT EMPTY, REPLACE CAPTION WITH CELL
                 'VALUE ***
                If Len(ActiveSheet.Cells(i, 1).Value) > 0 Then 
                    OControl.Caption = ActiveSheet.Cells(i, 1).Value 
                Else 
                     '*** ELSE MAKE OPTIONBUTTON NOT VISIBLE ***
                    OControl.Visible = False 
                End If 
            End If 
        End If 
    Next OControl
    one more thing my optionbutton are not renamed, they have the original name i.e. OptionButton1 and so on.

    I do not want to make them invisible, if there is not text it should just be equal to "TBA".

    Regards
    Raj
    Last edited by raj.bris; 03-09-2013 at 08:35 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1