Drop Down Menu Problems

Why use Vlookup ? i mean, if for example you have a textbox on page 2 you could use...

Private Sub ComboBox1_Change()
TextBox2 = ComboBox1
End Sub

To enter in TextBox2 the value selected in ComboBox1.

Juan Pablo

I apologize Juan I cannot get it to work.

Do you think that you can give me more detailed instructions on how to do it?

Thank you kindly,
Sean

I think you need first to explain a little more on what you're trying to do, what type of controls are you using, is there any code involved ? that way i can make a clearer explanation

Juan Pablo

Ok Juan I will try to make it as clear as I can.

Description Edited for Clarity:

-I have two pages within my Excel sheet named Page 1 and Page 2.

-On page 1 I have a drop down menu with the names of my co-workers. The drop down menu is named, "Drop Down 317"

-What I want to do is that when a person selects their name on the drop down menu I want their name to automatically appear in a field or whatever is necessary on Page 2.

I hope this is somewhat better Juan. I am somewhat of a beginner to the VBS etc. so I will just need it described to me in a step to step fashion. I thank you very, very much for your help.

When you say Page 1 and Page 2 you are talking about worksheets, right ?

And how did you insert that drop down ? is it a validation list ? or is it a form drop down ? or an activex one ?

Mhm, i assume by the name that is a form drop down, if so, why don't you set the Linked Cell to the one you want in Page 2 ? if you need to link it to another control then link it to any cell (i.e, A5) and set the other control link cell to A5 as well, that way, when you change the drop down in Page 1 it'll change the control in Page 2

Juan Pablo

Hello again Juan,

Here is the answer provided to me by Caleb I know it will work but I just don't know how to use the vlookup function in this instance:

"Well, I assume you're getting your list for the drop down menu (combo box) from a list of cells somewhere on the workbook. In the combo box's format control, you can choose a "Cell Link" that will return the ROW of your selection in a different cell that you choose. Of course, you don't want the ROW (1,2,3, etc) but the actual name. You CAN do this by giving each row in your original lookup range a corresponding number in the cell immediately to the left of each person's name and then doing a vlookup from the Cell Link cell on that number to return the correct name. There's probably an easier way to do that, but it's what I can think of immediately."

Thanks again Juan, I apologize for my stupidity.
Sean

Don't worry about it.

For example, in Page 1 i have this items in A1:A4

Apples
Bananas
Grapes
Pears

So, you put your combo box and set the Input Range to A1:A4, and it begins, to work, right ?

Now, set the linked cell to anywhere you want, i put it in B1. If you select Apples, for example, B1 will return 1. Now, in C1, to get the "actual" value selected put this formula

=INDEX($A$1:$A$4,$B$1,1)

If you selected Apples, C1 will turn to Apples.

So, now onto Page 2. Let's say that you have another Drop Down there...

Now, in the Input Range of that second Drop Down select ONLY C2, if you want, to be more "precise", select a linked cell (Whatever). Now goto that cell and put 1. That way this Drop Down will ALWAYS show the value selected in Page 1.

If you are taking it even further, i'd suggest protecting the worksheet (Page 2) so that the user can't change the value of the linked Cell to somethine different to 1.

Does this help ?

Juan Pablo

Hello Juan!

Yes it works finally, thanks alot I really appreciate.

But ofcourse I have two more questions. Here is my final code:

=INDEX('Page 1'!$B$64:$B$72,'Page 1'!$A$65,1)

What I want to know is if I can can add "Page 2" after the code. Like right now if I select my name on Page 1 then on Page 2 it reads, "Sean H."
What I want it to read is "Sean H. Page 2"

Is this possible?

Also one last thing, how can I change the colour of selected rows to my own custom colour via hex code like #eeeeee or the RGB or whatever? As you know there are only a certain limit of colours in the excel colour pallete.

Thanks again, you are the man!
Sean

Just something like this...

=INDEX('Page 1'!$B$64:$B$72,'Page 1'!$A$65,1) & " Page 2"

Juan Pablo

Thanks again Juan,

Sean H.

Didn't see Color part, here it is...

If you have them selected then use:

Selection.Interior.Color = RGB(153,253,247)

or

Selection.EntireRow.Interior.Color = RGB(1,1,1)

just use the last part to change color

.Interior.Color = RGB(1,2,3)

Juan Pablo