VBA Dropdown List

Hello Experts,

I would like to add a VBA dropdown list in the cell right below "Name".  If the cell to the right (named cell: 'RVStatus') says "Active" then I want the dropdown list to reference named range: EmpActiveAlpha, but if the cell says "In Active" - then I would like the dropdown list to reference named range: EmpInActiveAlpha.

error1.PNG
Thank you in advance for your help!
LVL 1
GeekamoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Wayne Taylor (webtubbs)Commented:
Why a VBA dropdown? You can do this with a Data Validation list....

1) From the Data tab on the ribbon, select "Data Validation"
2) Allow "list"
3) Enter this formula in Source...
    =IF(RVStatus="Active", EmpActiveAlpha, IF(RVStatus="In Active", EmpInActiveAlpha, ""))
4) Click OK.

Wayne
0
GeekamoAuthor Commented:
I received an error when I use the data validation. It basically says it resulted in an error. So instead of troubleshooting that, I just asked about VBA knowing it can be done that way too.
0
Wayne Taylor (webtubbs)Commented:
It can be done in VBA, but it's harder to maintain that a DV list.

Can you post a screenshot of the error?
0
Rodney EndrigaData AnalystCommented:
This should enter the DROPDOWN LIST using the proper NAMED RANGES you have in the ActiveWorkbook:

Sub EE_DropDownList()
Dim chkValue As String
Range("I13").Select     ' You can change this CELL as needed; DropDown list is applied in this cell.
chkValue = UCase(Range("N13").Value)     ' You can change this CELL as needed
If chkValue = "ACTIVE" Then
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=EmpActiveAlpha"
        .IgnoreBlank = True
        .InCellDropdown = True
    End With
Else    ' If ANYTHING other than <<ACTIVE>> is entered.
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=EmpInActiveAlpha"
        .IgnoreBlank = True
        .InCellDropdown = True
    End With
End If
End Sub

NOTE: You will need to change the CELL REFERENCES to a valid location for it to work properly.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.