PDA

View Full Version : Getting VBA to decide which cell to use



Sir Babydum GBE
12-24-2017, 05:50 PM
So I thought I was doing ok...

I have a trigger in the "Worksheet_Change(ByVal Target As Range)" code so that when someone picks a number from a validation list, a macro is run which uses the value of the active cell (which is of course the cell where the dropdown was just used) in a vlookup, and returns the results in another cell of the same column.

The problem now is that if the user decides to enter the text manually, he's going to hit the Enter key, which triggers the code... but now the active cell has shifted down one.

So what are my options please?

I know I could change the code so that it uses the value of the cell one above the (now) active cell, but then that would fail if the user uses the dropdown choices

p45cal
12-24-2017, 06:11 PM
use Target instead; that's the cell(s) which has just been changed.