This is the single most useful bit of code I have ever come across for Excel. The code allows you to write modules that are fired during cell entry. So, you are able to fire off lots of different events due to one selection being made in your dropdown list.
This is my scenario:
I do laser cutting and when I do costing I need to be able to select one material from a list of 10. The list of 10 materials may have different thicknesses.
So
Plywood
1mm
2mm
3mm
UHI
1.2mm
2.5mm
3mm
Now, each material / thickness has a related board size and price which gives me a calculated cost / cm sq.
When I select a particular material I have to restrict the selection of thickness to only those specified for the material type and once I select the material thickness I have to populate the material length, width, price and calculated price / cm sq fields.
I couldn't figure out how to do this because Vlookup etc is not clever enough to restrict the secondary selections.
This code allows you to do anything you like once data has been entered into a cell.
Run the example and you'll quickly figure out how useful the code could be.
XL: How to Run a Macro When Certain Cells Change
http://support.microsoft.com/kb/142154
Did you like this article? Share it with your favourite social network.