Using Formula to show if specific Option box is True

[Pistoleiro]

Hi All,


I'm not to sure about this one as I use option boxes fairly infrequently. My challenge is this
1) I have 2 option boxes showing an enabled disabled setting
2) I would rather not have these linked to a cell.
3) I would like to find whether 1 of these option boxes is true or false using a formula

Anyone with any ideas on how to do this. I'm as stuck as a pig in a sand pit.


Thanks very much

 

[Mike Blackman]

Hi,

I'm not really sure what your after but would something like this work?

In standard Module

Function OptionButtonCheck()

    With Sheets("sheet1")
        If .OptionButton1.Value = True Then
            OptionButtonCheck = .OptionButton1.Caption
        Else
            OptionButtonCheck = .OptionButton2.Caption
        End If
    End With
    
    Application.Volatile
    
End Function

In the sheet that contains the bottons code module

Private Sub OptionButton1_Click()

Calculate

End Sub

Private Sub OptionButton2_Click()

Calculate

End Sub

You would call this function using:

=OptionButtonCheck()

 

[Andrew Poulsom]

Sorry, it's not possible with a formula using built-in functions. You would need some VBA code. Where are your OptionButtons from?

 

[SteveO59L]

Surely the correct method is to link to a cell?

 

[Pistoleiro]

Aye, the correct method is to link to a cell however I was hoping for something where it wouldn't be necessary to change any protection that I've already coded in to the worksheet events. As the spreadsheets are setup with a certain amount of security, I guess unlocking that particular cell shouldn't cause too many problems.

I've changed it to link to a cell with data validation ensuring it can only ever be one of the two values.

Thanks for your help with this. I thought it was not possible by using built in formula's. Doesn't hurt to try and get around it though . Thanks again all.