Position of a drop-down list

[Traima]

Hi all,

I'm having some trouble with my drop-down list in excel. I've created a
macro to update a list of valid entries (from somewhere else in my
worksheet). And every time I update the list of valid entries, the drop-down
list starts with the position at the bottom. So I'll have to scroll upwards
to find my valid entries.

How can I adjust my macro to make the starting position of scrollbar at the
top of the drop-down list?

(I've tried to record the action of opening the drop-down list and pulling
the scroll to the top, but it won't record.)

Thankful for any tips...


Traima

 

[Daniel.C]

Can you post your code ?
Daniel

 

[Debra Dalgleish]

When you click the drop down arrow in a cell with data validation, Excel
highlights the item in the list that matches the cell's contents, if
possible.

There's probably a blank cell at the bottom of your list of valid
entries. If the cell with the data validation drop down is blank, Excel
highlights that blank item, at the end of the drop down list.

You could use a dynamic named range, to prevent blank cells in the valid
items list. There are instructions here:

http://www.contextures.com/xlNames01.html

 

[Traima]

Hi,

Thanks for both your tips.

Debra - Yes, at this point, I'll have empty cells at the bottom of my input
for the list.

In the example in the instruction uses a fixed area of input of

Ann
Bert
Carl
Doris
Ethel
Fred
Geoff
Hans

My list will have different numbers of names each time. Maybe only like this:


Ann
Doris
Hans

In the procedure of defining the name of the area, I need to define the
lenght of the list - and that I won't know.


Daniel - I haven't used much coding in this part of my worksheet. I'm using
a fixed area of input in my list of valid entries, but I would like to know
if there is a way to create a macro which opens the drop-down and sets the
scrollbar at the top of the list.

Thanks again,

Traima

 

[Traima]

Hi again,

Once I translated the formula in the name definiton field correctly into
norwegian excel, your suggestion works out just fine, Debra!

Thanks to both of you

Traima

 

[Debra Dalgleish]

You're welcome! Glad you were able to figure it out.