Text box to populate based on drop-down list cell value while sheet is "protected".

Hello!

I have a code that i cannot get to work properly. Please note I have almost ZERO experience with Macros/ VBA and know very little terminology. I do know a little bit and have been able to figure out little things here and there.

Here is what i am trying to do.

I have a drop-down list in cell C2 in the "Input" sheet that has the option for value "Good Faith Estimate" or "Lump Sum Quote".
I have a text box in the "Quote" sheet that is named "TextBox6".
I have 2 cells (J16 and J18) in the "Wages & Rates" sheet that have some text in them.
I would like for "TextBox6" to populate with the text in cell 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 cell J18 on the "Wages & Rates" sheet if C2 in "Input" sheet has the value "Lump Sum" selected from the drop-down.

I have been able to get this code to work if i wanted to populate the values into a cell, however, i want to populate it into a text box instead.

Please note that i have the code placed into the Sheet1 (Input). Here is my code below:

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

'Different 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

Thank you very much! I can attach the file if need be, i just need some guidance on what you think.

Originally Posted by AlphaFrog

Your code should work for a textbox as well. Make sure the two Target values are exact matches to the C2 dropdown entries. Shouldn't they be "Good Faith Estimate" and "Lump Sum Quote" ?

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

Hi! Thank you for the reply. Yes those are the correct values i have in there i forgot to update when for my original post. So every time i try the macro i get this

Run-time error '438':

Object doesn't support this property or method

After selecting debug option, it highlights the row pertaining to the value. So if i select "Good Faith Estimate", then the error message shows up and once i hit debug then it hight lights this row:

If Target.Value = "Good Faith Estimate" Then Sheets("Quote").TextBox6.Value = Sheets("Wages & Rates").Range("J16").Value

Then it does the same if try it with "Lump Sum Quote"

If Target.Value = "Lump Sum Quote" Then Sheets("Quote").TextBox6.Value = Sheets("Wages & Rates").Range("J18").Value

Originally Posted by AlphaFrog

My guess is that you don't have an ActiveX-type textbox named Textbox6 on the Quote sheet.
- Double-check the exact name of the textbox.
- There are two types of textboxes; an ActiveX-type and a Forms-type. If you used a Form-type control, try this syntax.

If Target.Value = "Good Faith Estimate" Then Sheets("Quote").TextBoxes("Text Box 6").Text = Sheets("Wages & Rates").Range("J16").Value
If Target.Value = "Lump Sum Quote" Then Sheets("Quote").TextBoxes("Text Box 6").Text = Sheets("Wages & Rates").Range("J18").Value

Exellent! It worked! I just had to change ("Text Box 6") in your code to ("TextBox6") as you suggested and it worked great! Thank you so much.

I have another question that i can start for another thread for if you think i should but it still pretains to this code. How can this code be used if i have the sheets all protected? This workbook has many protected cells with formulas. Employees are meant to use this and be able to use the macros and edit cells that arent locked.

I dont know how to invorporate this yet:

SheetName.Unprotect Password:=yourPassword

And this at the end:

SheetName.Protect Password:=yourPassword

The 2 seperate codes below work but i do not like the way the .Unprotect and .Protect feature works.

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 = "Good Faith Estimate" Then Sheets("Quote").TextBoxes("TextBox6").Text = Sheets("Wages & Rates").Range("J16").Value
If Target.Value = "Lump Sum Quote" Then Sheets("Quote").TextBoxes("TextBox6").Text = Sheets("Wages & Rates").Range("J18").Value
End If

'Different 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