VBA code to fill in from a drop-down menu

**dugong** · 05-15-2012, 12:02 PM

Hi

I am really (really, really..) rusty on my VBA and have looked at numerous examples for the last few hours but cannot find one that is 1) exactly what I need; or 2) most importantly, simple enough for me to understand what it is doing!

I have a yearly sheet (i.e. "2010", "2011", "2012" etc) with columns called Manager (col A), Dept (Col B) and outcome (Col C). There is a drop-down in Column A that is pulling the manager's from a list on another page called "Lists". Lists contains the lists for the 3 columns in same Columns - A, B and C.

When the manager is selected in column A, I need to populate the department name in column B automatically from the list in Column B on sheet called "Lists". The user populates Col C from the drop-down.

I had it set up doing a vlookup which did exactly what I wanted! The problem was that excel was calculating every time something changed in the file and stayed 'in limbo' until it did its calculations! Unfortunately this is not practical for my workplace

Can anyone help provide a macro (that works on the cell - Worksheet change?) and fills-in Col B when the cell in Col A is populated from the drop-down? I thought about doing a loop for the 45 departments but then that may be making the same problem of running the loop and sending file 'in limbo'.

I tried to attach an example file but the button wont let me...(??? not sure why??? as options just say IMG is off!)

Any help is appreciated.

(If not too much bother, can you add comments too so I understand what it is doing as I am trying to learn?)

I will keep looking when I get home tonight but if anyone can spare a moment that would be great.

dugong

**Pulsus** · 05-15-2012, 12:08 PM

Try to disable the automatic calculating for your workbook, just when they are saving the file.

**AlvaroSiza** · 05-15-2012, 01:13 PM

To turn off automatic calculation:

Office Button >> Excel Options >> Formulas >> 'Calculation Options' >> 'Workbook Calculation' >> 'Manual'

Under this setting you can press F9 when you are ready for a calculation to occur.

**dugong** · 05-15-2012, 04:00 PM

Hi

Thanks for the replies but the file will be loaded onto a server that others can access so I can't really set it to be manually recalculated.

I have been looking at lots of VBA Macro examples but am not sure how to do a macro to, once the cell is filled from drop-down, to fill in the adjacent cell with information in a list from another spreadsheet.

Any help once again appreciated...