Solved: Combo box headache

[joelle]

Hello Experts,

I have a simple combobox that will pull a list price from, say, D2 : D4.
However, the list prices will change according to the region user selects:
D2 = 300,000 will change to 300,000 x 2 (if region cell = "E")
D3 = 400,000 same as above
D4 = 500,000 same as above

So my combobox does not work quite well-- when they type in "E", the box is blank when I want it to show 600,000 as what already show from the fillrange D2 : D4.

On top of this, my 2nd headache is how to make the list work with a locked
sheet (please see simple ws attached).
As always, I'm very thankful to any help you can give.
Nee

[Ken Puls]

Hi Nee,

When I open your file and enter E in the yellow cell, the combo box values change to the column D values... Is that what you meant?

As for working with a protected sheet, unlock cells B2:C2 before you protect the sheet. The target of the combobox needs to be unprotected as well as the input cell.

[joelle]

Hello Ken,
Thank you for your quick coming to my rescue. However, when I type in E, I dont see any value from the dropdown box when it should show 600K.
So I reset the boundcolumn and columncount to 1, and it shows 600K when I enter E. But when I type in N for region, I expect to see it flips back to 300K -- but no, the value stays the same -- meaning the value in the combo only changes once.
Also, what is the "The target of the combobox " ??
I did unlock the input cell for the region.

Thanks for putting up with me.
Nee

[Ken Puls]

Sorry, you mean you want the combo box to change immediately when you cahnge the value? Okay, I think I get you now. The ActiveX combobox will refresh the list, but it clears the value in the box when you change the value of B2.

So one question... why are you using an ActiveX combo box for this when you have no code?

I've deleted the ActiveX combo box and put in one from the Forms toolbar. If you right click it and check the Format Control, you'll see that I've unprotected it, linked the list to your list, and place the output of the combbox in cell C2. (It will come out as 1,2,3...) You can then use this later to look up values in tables if you want.

Let me know what you think.

[joelle]

Hello Ken,
My bad ... I forgot to mention that I "have" to use activex combo box because I need to hide it with couple of rows. We only show the dropdown if user clicks on an option (in real life, my ws is a bit more complex).
With the box from the form toolbar, it works so well but I cannot hide it with the hidden row it sits on. So, is there a way to hide this non-activex box when I hide the named-range (rows) that it sits on?

Many thanks,
Nee

[Ken Puls]

Sure!

Say your drop down is called "Drop Down 1":

'To hide it
ActiveSheet.Shapes("Drop Down 1").Visible = False

'To unhide it
ActiveSheet.Shapes("Drop Down 1").Visible = True
HTH,

[joelle]

Ken,
Amazing!!! this made me remember couple months ago when you helped me solve a vba sticky problem by your very fast turnaround in postingback.
Youve been helping lots of people you might already forget ... not the ones that you helped and helped with no time in between (like me) cannot forget that moment.
I bow to your kindness !

I wish you and your family and everyone else on this board a most Wonderful Thanksgiving. Stuff well!
Myself, I feel so thankful to you -- thanks Ken, again.

Warm regards,
Nee

[Ken Puls]

Hey Nee,

No sweat, really. Happy to be of help. :)

Thanks for saying thanks, though. I'm not alone in appreciating the feedback wehn we help out. :)