Passing different value from combobox

[VitoHGrind]

I'd like to know how to send a different value to a cell based on what is selected in the combobox. Like a vlookup, but from within the box.
Something like :

Please Login or Register  to view this content.

This will be global and apply to every box in the worksheet.

Any thoughts?
Here's part of the file. I had to recreate, take some of the more conf. stuff out and I've also turned auto-calculate off to meet the size req.

[mikerickson]

Rather than using VB, link the combo box to $A$1, and use the spreadsheet formula
=INDEX(LookUpList,$A$1,2)

[davesexcel]

This could get you on track

Please Login or Register  to view this content.

[VitoHGrind]

I don't know if I was perfectly clear the first time, but what I want is something that will change the way that EVERY combobox behaves without having to doing it to every combobox.

Mike, I'm thinking that what you're suggesting is similar to what I currently have in that I link the combobox to a different cell than the one that I really want the value in. Then, use a function in the important cell to relate the value in the linked cell to what I want. I'm using Vlookup now, so I'm assuming Index is similar.

Dave, is there any way to put that in a module or in another worksheet function so that all the comboxboxes have that response. I know I could do a For Each obj type of loop, but I assume I'd have to use a command button to call that function, which might be ok. Mostly, I don't want to have to create a change event function for all the comboboxes. Plus if something gets added I think it'll be hell to change and update all those functions and cells and such.

I've only been using VB for about a week so bear with me, its all pretty new..

[davesexcel]

Hi VitoHGrind,
The code actually works in the comboBox from the controls toolbar, I know when I first started playing with excel I didn't understand all the gibberish,but, the example you gave us showed that the combo box was from the controls tool bar, that is why I supplied the code
it will look like this

Please Login or Register  to view this content.

if you were to right click with the mouse on the sheet tab and select view codes, and paste the above code there, you will see what I am referring to,
click the down arrow on the left drop down menu and you should be able to see all the combo boxes listed there...
you will have to adjust the code to meet your requirements

[VitoHGrind]

Thanks davesexcel,

I understand what you're saying, but the code you provided would have to be applied to each combobox in the worksheet. I don't want to have ComboBox1_change - ComboBox423_change in my function list.

This sheet is going to change, new things will be added and some things will be taken away. If I hardcode all the cell references/ranges I would have to go back and update nearly all of the comboboxes depending on where things are added.

What I would like is some sort of global function that would format the output of the comboboxes. I guess something like a submit button, that works similar to the reset button that I created.

Any thoughts on this?

[davesexcel]

Thanks davesexcel,

I understand what you're saying, but the code you provided would have to be applied to each combobox in the worksheet. I don't want to have ComboBox1_change - ComboBox423_change in my function list.

This sheet is going to change, new things will be added and some things will be taken away. If I hardcode all the cell references/ranges I would have to go back and update nearly all of the comboboxes depending on where things are added.

What I would like is some sort of global function that would format the output of the comboboxes. I guess something like a submit button, that works similar to the reset button that I created.

Any thoughts on this?

I am sure there is a way to do what you want, but I don't know how to do that,
It may be easier to use data validation for the drop down menu then you could use the worksheet change event, and use the active cell, that would take place of trying to create a global combobox code.

[VitoHGrind]

thanks davesexcel, I'll probably just keep using what I have now.

It works, not completely the way I want it to, but adding anything is pretty simple and the output is as desired.

Does anyone know how to access the linkedcell of a combobox? My thought is I can work something like a "combobox.linkedcell.value" but linkedcell is not a parameter.

Thoughts?

[mikerickson]

According to the Object Browser, .LinkedCell is a property of both ControlFormat and OLEObject.
Depending on if it came from the ToolBox or the Forms Menu, one of these should do it.

Please Login or Register  to view this content.

[VitoHGrind]

Thanks, you guys really helped a lot. I sort of put everything you said together and finally came up with this:

Please Login or Register  to view this content.

Its a little clunky and I had to create a "Submit" button because I couldn't figure out how to do it automatically, but it works great now.

Thanks again for you guys' help and direction.

Vito H. Grind

[VitoHGrind]

ok.. now I have the issue of updating. Is there a way to update just a single worksheet in a workbook?

I turned off auto-update because there's so much in the workbook it was bogging everything down. Now, when I update the whole thing, all the changes from "Submit" get overwritten by the actual values in the combobox and not their vlookup results.

I'd like to add a final line to SubmtSheet function to update a different sheet.
Something like "Sheets("Sheet2").update" but I have no idea what it would be.

Any thoughts?

[VitoHGrind]

Well, I thought I had completely made that .Update thing up, but apparently it does exist. Who knew?!

However, I still can't get the dependent cells to update properly using "Sheets("Sheet2").update. When I go to the cells, select the formula and press enter (forcing them to update), they work fine, but trying to use that line of code to update the whole sheet doesn't seem to work yet.

So again, Any thoughts?

[mikerickson]

300 Combo Boxes That's an awful lot. Perhaps Validation lists would serve you better.
For updating, perhaps the VB instruction

Please Login or Register  to view this content.

[VitoHGrind]

Actually there are about 250, but I figured I'd put a little cushion just in case we add something.

I assume that by validation you mean the drop down lists? We decided against those so that its obvious that there is something to click on as the drop down arrow doesn't appear till you select the cell.

So far I've tried .Update, .CalculateFull, and .Calculate using the form

Please Login or Register  to view this content.

but this actually resets the values of the linked cells to that of what's selected in the ComboBox, instead of leaving the updated vlookup values. Basically it updates the whole workbook.

Is there any other way I could do this?