Editable Text Based on Drop Down List

[rxharp419]

Hello,

I would like to have a drop-down list in A1.

Based on the value selected in the drop-down list in A1, I would like a specific parage of text to pop up in A2. I would like for this text in A2 to be editable so minor things can be changed as needed.

Is this possible?

 

[My Aswer Is This]

In your original post you had two sheet.
The first sheet you did not say what it's name was.
The second sheet was named "Wages & Rates<strike></strike>"

I don't know what your talking about now you want C2 on two different sheets

When you talk about sheets you have to say:

Sheet name: "Wages & Rates<strike></strike>"
Sheet named "Input"

You cannot just say Input sheet.

And you keep moving the "Goal Post"

 

[rxharp419]

In your original post you had two sheet.
The first sheet you did not say what it's name was.
The second sheet was named "Wages & Rates<strike></strike>"

I don't know what your talking about now you want C2 on two different sheets

When you talk about sheets you have to say:

Sheet name: "Wages & Rates<strike></strike>"
Sheet named "Input"

You cannot just say Input sheet.

And you keep moving the "Goal Post"

Okay, I will try to explain this better.

I have a drop down list in cell C2 in the "Input" sheet that has the option for value "Estimate" or "Lump Sum".

I have a text box in the "Quote" sheet that is now named "TextBox6".

I have 2 cells in "Wages & Rates" sheet that have some text in them. J16 and J18.

I would like for TextBox6 to populate with the text in J16 on the "Wages & Rates" sheet if C2 in "Input" sheet has the value "Estimate" selected from the drop-down.

And

I would like for TextBox6 to populate with the text in J18 on the "Wages & Rates" sheet if C2 in "Input" sheet has the value "Lump Sum" selected from the drop-down.


Your previous code works fine if text is populating into a cell, but it does not work at all when i try it for a text box. I even tried creating new textboxes and changing the names to match exactly. Every time i try with the first way you had before we ever discussed text boxes works great with the old targets.

However, with the changes i mentioned above, can you kindly assist with making this possible for the text box please?

 

[My Aswer Is This]

Put this script in the sheet named "Input"

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C2")) Is Nothing Then
If Target.Value = "Estimate" Then Sheets("Quote").TextBox6.Value = Sheets("Wages & Rates").Range("J16").Value
If Target.Value = "Lump Sum" Then Sheets("Quote").TextBox6.Value = Sheets("Wages & Rates").Range("J18").Value
End If
End Sub

 

[rxharp419]

Put this script in the sheet named "Input"

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C2")) Is Nothing Then
If Target.Value = "Estimate" Then Sheets("Quote").TextBox6.Value = Sheets("Wages & Rates").Range("J16").Value
If Target.Value = "Lump Sum" Then Sheets("Quote").TextBox6.Value = Sheets("Wages & Rates").Range("J18").Value
End If
End Sub

THank you! I already have a macro in this sheet:

<style type="text/css"> p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000 ; background-color: #ffffff } p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000 ; background-color: #ffffff ; min-height: 13.0px} p.p3 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; background-color: #ffffff ; min-height: 13.0px} p.p4 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #008f00 ; background-color: #ffffff } p.p5 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #011993 ; background-color: #ffffff } span.s1 {color: #011993 } span.s2 {color: #000000 } </style>Private Sub Worksheet_Change(ByVal Target As Range)

Dim wsI As Worksheet, wsQ As Worksheet, x As Long


'limit change monitoring to C3


If Target.Count > 1 Then Exit Sub
If Target.Address <> "$C$3" Then Exit Sub


Set wsI = Sheets("Input")
Set wsQ = Sheets("Quote")

Application.ScreenUpdating = False
x = Target.Value * 17

With wsI
.Unprotect
.Rows("8:1027").Hidden = True
If x > 0 Then .Cells(8, 1).Resize(x).EntireRow.Hidden = False
.Protect
End With

With wsQ
.Unprotect
.Rows("9:1028").Hidden = True
If x > 0 Then .Cells(9, 1).Resize(x).EntireRow.Hidden = False
.Protect
End With

Application.ScreenUpdating = True
End Sub

How would i combine them? I researhed and could not find any good information on this and as you have discorvered i have very little information about this stuff. Thank you again.

 

[rxharp419]

Put this script in the sheet named "Input"

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C2")) Is Nothing Then
If Target.Value = "Estimate" Then Sheets("Quote").TextBox6.Value = Sheets("Wages & Rates").Range("J16").Value
If Target.Value = "Lump Sum" Then Sheets("Quote").TextBox6.Value = Sheets("Wages & Rates").Range("J18").Value
End If
End Sub

I will start with I did exatly as you explained. I put the code into the Input sheet by going to "view code" and entering it in. The very first thing that happened when i selected "Estimate" from the dropdown was run time error 438 "object doesnt support this property or method". I clicked on debug and this row was highlighted blue: <style type="text/css"> p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #011993 ; background-color: #ffffff } </style>If Target.Value = "Estimate" Then Sheets("Quote").TextBox6.Value = Sheets("Wages & Rates").Range("J16").Value

My other macro in the same sheet works fine but i removed it to try this one since i dont know how to have 2 macros on the same sheet or how to merge them like i explained in previous post to you.

 

[My Aswer Is This]

You have two sheet change event scripts running off the same cell change event "C3"
You cannot do that. Your saying:

x = Target.Value * 17

Which means you plan to put a number in "C3"
But then in my script you say your going to enter "Estimate" or "Lump Sum" in "C3"

 

[rxharp419]

You have two sheet change event scripts running off the same cell change event "C3"
You cannot do that. Your saying:

x = Target.Value * 17

Which means you plan to put a number in "C3"
But then in my script you say your going to enter "Estimate" or "Lump Sum" in "C3"

Your script is relating to "C2" not C3.

 

[My Aswer Is This]

My miss.
Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
'My Script
If Not Intersect(Target, Range("C2")) Is Nothing Then
If Target.Value = "Estimate" Then Sheets("Quote").TextBox6.Value = Sheets("Wages & Rates").Range("J16").Value
If Target.Value = "Lump Sum" Then Sheets("Quote").TextBox6.Value = Sheets("Wages & Rates").Range("J18").Value
End If

'Your script
If Target.Address <> "$C$3" Then Exit Sub
If Target.Count > 1 Then Exit Sub
Dim wsI As Worksheet, wsQ As Worksheet, x As Long
'limit change monitoring to C3
Set wsI = Sheets("Input")
Set wsQ = Sheets("Quote")
x = Target.Value * 17
With wsI
.Unprotect
.Rows("8:1027").Hidden = True
If x > 0 Then .Cells(8, 1).Resize(x).EntireRow.Hidden = False
.Protect
End With
With wsQ
.Unprotect
.Rows("9:1028").Hidden = True
If x > 0 Then .Cells(9, 1).Resize(x).EntireRow.Hidden = False
.Protect
End With
Application.ScreenUpdating = True
End Sub

 

[rxharp419]

My miss.
Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
'My Script
If Not Intersect(Target, Range("C2")) Is Nothing Then
If Target.Value = "Estimate" Then Sheets("Quote").TextBox6.Value = Sheets("Wages & Rates").Range("J16").Value
If Target.Value = "Lump Sum" Then Sheets("Quote").TextBox6.Value = Sheets("Wages & Rates").Range("J18").Value
End If

'Your script
If Target.Address <> "$C$3" Then Exit Sub
If Target.Count > 1 Then Exit Sub
Dim wsI As Worksheet, wsQ As Worksheet, x As Long
'limit change monitoring to C3
Set wsI = Sheets("Input")
Set wsQ = Sheets("Quote")
x = Target.Value * 17
With wsI
.Unprotect
.Rows("8:1027").Hidden = True
If x > 0 Then .Cells(8, 1).Resize(x).EntireRow.Hidden = False
.Protect
End With
With wsQ
.Unprotect
.Rows("9:1028").Hidden = True
If x > 0 Then .Cells(9, 1).Resize(x).EntireRow.Hidden = False
.Protect
End With
Application.ScreenUpdating = True
End Sub

Okay so the other code still works fine. But yours still gives the same error message and when i hit debug this gets highlighted when I select "Estimate" in the drop down.

Sheets("Quote").TextBox6.Value = Sheets("Wages & Rates").Range("J16").Value

If I slect "Lump Sum", this gets highlighted

Sheets("Quote").TextBox6.Value = Sheets("Wages & Rates").Range("J18").Value

 

[My Aswer Is This]

You need to put the script in a sheet by itself and see if it works. Do not have your other script in the sheet.
Do you have a TextBox named "TextBox6" in a sheet named "Quote" ??
And in your other script it talks about protecting and unprotecting the sheet. If the sheet is protected this script will maybe create a error.