VBA For Drop Down List

[vdespard]

I would like to create a VBA that takes the text that the user selects in a drop down list in cell B11 (there are only two options in the drop down list in this example) and then, based on that selection, changes cell D11 - either pulls a value from another tab and shades the cell gray, or sets the value to $0 and shades the cell white. The two text options in B11 are "Manual Entry" (this selection would set D11 to $0) and "Pull From Rehab Details" (this selection would pull a value from a cell in another tab). Can anybody help me?

 

[My Aswer Is This]

We would need to know what this means:
"this selection would pull a value from a cell in another tab"
What other tab and from what cell?

And what does this mean:
"shades the cell white"
do you mean you want the interior color of the cell to be white?


You would need a script something like this:

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window
Put this script in your sheet where you enter your values.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 And Target.Row = 11 Then
If Target.Value = "Manual Entry" Then Target.Offset(0, 2).Value = "$0": Target.Offset(0, 2).Interior.Color = vbWhite
If Target.Value = "Pull From Rehab Details" Then
Target.Offset(0, 2).Value = Sheets(2).Range("B3").Value: Target.Offset(0, 2).Interior.Color = vbGreen
End If
End If
End Sub

 

[vdespard]

Thank you for your help!!

Regarding, "this selection would pull a value from a cell in another tab"....the tab name is "Rehab", and the cell is B41

Regarding, "do you mean you want the interior color of the cell to be white?"...yes, that is what I need...it appears to be working as you wrote it...

I have altered the code slightly...because cell B11 is a merged cell with C11, the offset is 0, 1 instead of 0, 2.

And I changed the gray background to match the background in the other cells in the sheet...I use an RGB formula...

Updated code is below. The only remaining thing is to pull the value in B41 from the Rehab tab if "Pull From Rehab Details" is selected:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 And Target.Row = 11 Then
If Target.Value = "Manual Entry" Then Target.Offset(0, 1).Value = "$0": Target.Offset(0, 1).Interior.Color = vbWhite
If Target.Value = "Pull From Rehab Details" Then
Target.Offset(0, 1).Value = Sheets(2).Range("B3").Value: Target.Offset(0, 1).Interior.Color = RGB(217, 217, 217)
End If
End If
End Sub