Show different text boxes based on drop down

Closed
SteveC - Apr 15, 2010 at 02:35 PM
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
- Apr 16, 2010 at 03:09 PM
Hello,
I am working with Excel 2007. I am trying to create a spread sheet that has two visible sheets. The first sheet is an option picker it has several drop down boxes and based on the option you select on that sheet prices are calculated from a hidden data sheet. I want to have another sheet that shows a text box discribing the option you selected on the first sheet. There are a max of 4 different options on the first sheet per selection. So I need have the proper text box on the discriptions sheet show up after the drop down selection is made. I tried to do this with a formula but my text is over 255 chariters so I need to use a text box or some other method to do this.

Can anyone help Please???


Thank you,
Steve

3 replies

rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
Apr 15, 2010 at 06:13 PM
Could you please upload a sample file on some shared site like https://authentification.site and post back here the link to allow better understanding of how it is now and how you foresee.
0
Thank you so much for assisting. I have posted a file as you requested. I have removed some of the details for privacy, but the functionality is still there. I have unlocked and unhid the pages, normally the Data sheet is hidden. I would like to keep the Text boxes on that page if possible but it doesn't really matter as long as it works. As you will see if you select the dropdown list next to any of the Modules it gives you an option to select. From that selected option I need to display a discriptive text box on a separate sheet. Each of the options have a different discription and I want to have that auto populate based on the choice made on the first sheet.
https://authentification.site/files/21969144/Tempsheet.xlsm

Thank you very much!
Steve
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
Apr 16, 2010 at 03:09 PM
Ok try this

1. open VBE by pressing ALT + F11
2. Double click on your sheet Price Calculator where you will be doing the selection of module
3. Paste the code below


Private Sub Worksheet_Change(ByVal Target As Range)


Select Case Target

Case Is = "S"
sText = " this is " & vbCrLf & "S"

Case Is = "I"
sText = " this is " & vbCrLf & "I"

Case Is = "W"
sText = " this is " & vbCrLf & "W"
Case Else
Exit Sub
End Select

Dim objWS As Worksheet

Set objWS = Sheets("Descriptions")

objWS.Shapes.Item("TextBox 1").TextFrame.Characters.Text = sText

set objWS = nothing
End Sub
0