I am trying to create a dropdown based on another value in VBA, Column K would drive the results of the dropdown that will appear in column M.
If column K = "Yes", then Column M = "In Scope: Use Direct" (no dropdown needed, just text value).
If column K = "No", then Column M = "Unknow" (displayed, and dropdown choices for user to change to, order does not matter: "In Scope: Inactivate, In Scope: Obsolesce, In Scope: Replicate, In Scope: Tailor, In Scope: Use Direct, Out of Scope, Unknown")
I thought I had figured it out with this code, but it is not working right. Every time I run it, it shuts down Excel. There must be a way to do what I need to.
Sub Dropdown()
Dim ws As Worksheet: Set ws = Worksheets("Impact")
'declare and set the worksheet you are using, amend as required
lastRow = ws.Cells(ws.Rows.Count, "K").End(xlUp).Row
'get the last row with data on Column K
For i = 2 To lastRow 'loop from Row 2 to Last
If ws.Cells(i, "K").Value = "No" Then
With ws.Cells(i, "M").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="In Scope: Inactivate, In Scope: Obsolesce, In Scope: Replicate, In Scope: Tailor, In Scope: Use Direct, Out of Scope, Unknown"
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = True
End With
Else
With ws.Cells(i, "M").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="In Scope: Use Direct"
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = True
End With
End If
Next i
End Sub
Good day,
The code does not tell much seeing you have two conditions
I suggest you attach the file (non sensitive data) with the macro to look at.
You say Excel just shuts down, have you tried stepping through the code step by step and see where it goes wrong?
@Hans Hallebeek,
I am somewhat new to VBA and do not understand how to debug and step thru the code as of yet. Attached is a sample file. I left the headers in tact, but removed the data from the columns that are not relevant to this macro. The focus is on K (Applicable in MAP) and M (Future Implementation Method). As I stated I want the dropdown to appear in Column M, but ONLY when Column K is equal to No. AND I want the current value of "Unknown" to remain displayed when the dropdown is added. I do not want a dropdown in column M when the value in column K is Yes.
First of all the macro throws an error the moment you run it because you have not dimensiond all the variables.
Another thing is that when you are using a real Listobject table like you have, it is a good rule to keep the contents in the columns the same.
It will really mess up things if you add a dropdownlist to one row and not to another oner, I don't say i cannot be done but it'snot really the best way, I would choose for a right-clich action that dispalyas a list to select from but that's my idea.
I will see if I can figure out why it crashes and let you know if I find anything
I took a look, just dimensioned the two missing variables and it works.
I just made some minor changes to the VBA code and it still works
@Hans Hallebeek,
I honestly don't know if it will work on my live data. I tried to use the macro on a copy of my workbook, and I just had the "spinny" showing it is working. I pulled up the Task Manager after a minute to close out Excel. My table has almost 200K rows of data. Would this be the reason? I tried 3-4 times, an d the result was the same, so I did the Task Manager after a minute, as that is way too long to process. Any ideas?
Hi Sherry,
I went a little further
I have not added comments explaining the code but this will do the job
I only don't understand why you have the same value used in Yes in the No list
Not without the data.
The way it's now, it works at least the V2 has some more features
You have to copy the vba contents from the sheet as well as the module to your workbook and make sure all your data is in the actual Table1 that you have there.
If you can attach the file it's would be better but if it contains data you can't share you can tell me and I'll send you a link where you can upload it to so that I can test
