Excel: Hide/Unhide Object on Click

[Pc1x1]

Good Evening Gentleman,

I am currently working on a User Form, and I been using thus far conditional formating, however I believe I may have to join the world of Macro Enabled worksheets to finish what I started.

Basically I have everything complete, except one thing, that I tried severals codes online, but am missing by a little. I understand VBA, I have done programming before, but its very limited, I am still on the copy and paste, modify phase, but I will be reading up on it on my spare time.

Anyhow, I made a layout that has a Yes or No, and I made an object Oval 3, over Yes, and object Oval 4, over No. Basically I want both hidden (so all you see/print is Yes or No) unless the USER clicks on where the object is, then that will unhide the object over the Yes or No. So when the USER prints, the oval will be over whatever Yes or No they selected. To complicate things if possible, I would like it so both can't be displayed at the same time, so by error the USER can't select ovals on both yes or no.

Thank you, and let me know if you need more clarification, I am hoping this is fairly easy to implement, I even thought of Icon Sets, but doesn't work in this. Also if theres a conditional alternative, I am open as well.

Excel 2007:

 

[mikerickson]

Instead of fiddling with ovals, you could make one shape and assign it to this macro. Click on the shape will toggle it between Yes and No.

Sub ToggleCaption()
    With ActiveSheet.Shapes(Application.Caller).TextFrame.Characters
        If .Text = "Yes" Then
            .Text = "No"
        Else
            .Text = "Yes"
        End If
    End With
End Sub

 

[Pc1x1]

Instead of fiddling with ovals, you could make one shape and assign it to this macro. Click on the shape will toggle it between Yes and No.

Sub ToggleCaption()
    With ActiveSheet.Shapes(Application.Caller).TextFrame.Characters
        If .Text = "Yes" Then
            .Text = "No"
        Else
            .Text = "Yes"
        End If
    End With
End Sub

I need the ovals, because this form will be hand written as well, not only digital. So it needs a Yes or No digitaly there, we could do Yes or No, then Yes, then No only on the form, instead of ovals? Maybe thats a compromise?

IE: Yes or No is default, if you click the cell again, it becomes a Yes, If you click again, a No. And so forth.

I kinda like to know how to do the Objects Hide and Unhide if possible though. Thanks!

 

[mikerickson]

I find that Double Click gives more control than clicking on a cell.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    With Target
        If .Address = "$A$1" Then
            Cancel = True
            .Value = Not (CBool(.Value))
        End If
    End With
End Sub

If you prefer the single click that could be put in the Worksheet_SelectionChange event.