Cell value determining visiblity of txtbox object

[Skimmer29]

Hi there!

I have built a dashboard with an interactive graph that uses regular option buttons (not ActiveX) to allow the user to select the data/chart for a specific department. All this works just fine, but what I'd also like to do is change the "comments" text to match each department's graph.

I have two questions:
1. Is it possible in VBA (in a worksheet change event for example) to say: If the cell linked to the option buttons has the value of 1 (which in turn charts department 1), then make visible textbox 1, if the cell has the value of 2, then display textbox 2, etc. for all option buttons (there are 5 in total).

OR

2. Must I change the option buttons to ActiveX option buttons and say something like this: (note: the chart/textboxes on are different sheets than the option button's linked cell...)

Private Sub OptionButton1_Click()

If Me.OptionButton1.Value = True Then
ActiveWorkbook.Sheets("Sheet2").Activate
ActiveSheet.Range("A1").Value = 1

ActiveWorkbook.Sheets("sheet1").Activate
Me.TextBox1.Visible = True
Me.TextBox2.Visible = False
Me.TextBox3.Visible = False
ActiveSheet.Range("A1").Select
End If

End Sub

I tried this code and it did work, but was incredibly slow for some reason.

Any suggestions?? Or is there another way altogether??

Thanks so much!
Kim

 

[Gary McMaster]

Hi Kim,

Maybe "Application.Caller" will work for you.

To try the sample below, paste the code in a standard module and "Assign Macro" "Test" to each of the 5 option buttons (right click on the option buttons and pick "Assign Macro").

Hope it helps.

Gary

Public Sub Test()

Dim oTextBox As TextBox

Debug.Print Application.Caller

For Each oTextBox In ActiveSheet.TextBoxes
    oTextBox.Visible = False
Next oTextBox

'Change option button and text box names to suit
Select Case Application.Caller

Case "Option Button 1"
    'Or use 1 text box and change text instead of visibility
    ActiveSheet.TextBoxes("Text Box 1").Visible = True
    
Case "Option Button 2"
    ActiveSheet.TextBoxes("Text Box 2").Visible = True
    
Case "Option Button 3"
    ActiveSheet.TextBoxes("Text Box 3").Visible = True
    
Case "Option Button 4"
    ActiveSheet.TextBoxes("Text Box 4").Visible = True
    
Case "Option Button 4"
    ActiveSheet.TextBoxes("Text Box 5").Visible = True
    
Case Else
    'Do nothing
End Select

End Sub