Creating a list that changes depending on another cell

[Shippwreck]

Hi All,

I'll get to the point. I am using lists in a spreadsheet to force users to select only valid entries for cells. I have one column of cells where the users enter a code. I would like the cell to columns over to have a list that is dependant on the first cell.

I have created a macro that when it runs it looks at the active cell and says if the cell equals a certain value then select the cell two columns across and use these .... values as the list for that cell.

That works great, except i would like to make that macro run as soon as the user presses enter to move to the next cell (or clicks somewhere else in the sheet)

But as you may of guessed that is what i'm having problems doing, i have tried to make it a function but without much success, the code for the macro is below:

Sub Calc()

If ActiveCell = "101" Then

ActiveCell.Next.Select
ActiveCell.Next.Select

With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="pigs, horses"
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = True
End With
End If
End Sub

As you might be able to tell i recorded and then edited it.

Anyway any help would be much appreciated,

Thank you in advance

http://www.orlandoluxuryvilla-pool.com

 

[PHV]

Take a look at the Worksheet_SelectionChange event procedure and at the Application.EnableEvents property.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Shippwreck]

Hi,

Thank you for your response, but I am still relatively new to VBA and as such although I have had a look at both commands that you suggested I do not relaly understand them. I have looked in the VBA Help and had a search on Google.

Could you be a little bit more specific as to how to solve the problem.

Thanks for your help

http://www.orlandoluxuryvilla-pool.com