Hi All,

I've created several Option Buttons (activeX) on my Excel sheets.
I'm creating a generic hide-when function that uses the Option Button's name as reference. Now, I cannot read the name of the Option Button that user selected. I want something like me.value. I don't want to use me.OptionButton1.Name (thats not generic)

here's the sub the button should call, where tempname is the name of the Option Button;

//Caller:
Private Sub P02_Q01_Y_Click()
        Call ShowHideQuestions(Me.Name) <-- doesnt work.. returns sheet name
End Sub

Sub ShowHideQuestions(tempName As String)

On Error GoTo ErrHandler

'Dim tempName As String
Dim rangeName As String
Dim XrangeName As String
Dim XrangeName_XTRA As String
Dim ShowHide As String
Dim blProtected As Boolean
Dim ThisRow As Long
Dim lngCounter As Long
Dim lngLastRow As Long

Application.ScreenUpdating = False

blProtected = False
'tempName = Application.Caller

rangeName = Left(tempName, 7)
ShowHide = Right(tempName, 1)

lngLastRow = ActiveSheet.Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
ThisRow = 1

If ActiveSheet.ProtectContents = True Then
    blProtected = True
    ActiveSheet.Unprotect (pwd)
End If

Select Case ShowHide
    Case Is = "S" 'Special Case
        Exit Sub
    Case Is = "Y" 'Yes Flag
        Range(rangeName).Rows.RowHeight = 16.5
    Case Is = "N" 'No Flag
        Range(rangeName).Rows.RowHeight = 0
    Case Is = "X" 'X show first option hide second
        Range(rangeName & "_X").Rows.RowHeight = 16.5
        Range(rangeName & "_Z").Rows.RowHeight = 0
    Case Is = "Z" 'X hide first option show second
        Range(rangeName & "_X").Rows.RowHeight = 0
        Range(rangeName & "_Z").Rows.RowHeight = 16.5
    Case Else
        Exit Sub
End Select

Dim strCellValue
Dim strUserCellValue
Dim intInString
Dim strBlockVal As String
Dim strHiddenBlock As String
For lngCounter = 1 To lngLastRow

    strCellValue = (Range("A" & lngCounter).Value)
    intInString = InStr(UCase(strCellValue), UCase(rangeName))

    If intInString > 0 Then
        'MsgBox UCase(Mid(strCellValue, 2))
        If Left(UCase(strCellValue), 5) = UCase("XOPT_") Then
            strUserCellValue = (Range("B" & lngCounter).Value)
            strBlockVal = strBlockVal & strUserCellValue
            strCellValue = strCellValue & "_X"
' If strHiddenBlock = "" Then
' strHiddenBlock = strCellValue
' End If
            If ShowHide = "Y" And strUserCellValue <> "" Then
                Range(strCellValue).Rows.RowHeight = 12.75
            Else
                Range(strCellValue).Rows.RowHeight = 0
            End If
        End If
    End If
Next

If blProtected = True Then
    ActiveSheet.Protect (pwd)
End If

Application.ScreenUpdating = True

Exit Sub

ErrHandler:
Application.ScreenUpdating = True
MsgBox Err & "' " & Err.Description

Exit Sub

End Sub


Plz Help

I think the simplest solution would simply be to cycle through the options buttons using a For Each structure and then store the name of the currently selected option button. That way you could then pass it the way you want ie myOpt.value

I've found a solution and hope you can cope with it.

First of all try it out in an new workbook so you get used at the structure
There are 2 ways to place an Option Button on a worksheet.
You can select the toolbar "Control Toolbox" or the toolbar "Forms" by RightClicking in the Excel menu.
It is important to select the toolbar "Forms" here and from there place a couple of option buttons on the sheet.
Now RightClick at the first option button you placed on the sheet . Look just above the cel "A1" in the name box ,LeftClick in it and change the name in the one you like ( P02_Q01_Y ) and don't FORGET to press the ENTER button.
If you like you can change the text at the right side of the option button by again rigthclicking en select Edit text.
Now your ready to couple macros to your option buttons
RightClick at the first option button you create and select Assign Macro , at that moment you find a macro name ( P02_Q01_Y_Click ) press on the New button and fill
the sub P02_Q01_Y_Click() so it looks like

sub P02_Q01_Y_Click()
    Dim opt As OptionButton
    For Each opt In ActiveSheet.OptionButtons
            If opt.Value = 1 Then Call ShowHideQuestions(opt.Name)
    Next
End Sub

Repeat this structure for all your OptionButtons

Have fun with it.