VBA to select value from cell with data validation....

[largeselection]

So this is kind of an interesting one for me...

Essentially I have some cells which have data validation referencing a named range. The named range is on another worksheet and consists of Cube Members. I have a bunch of cube functions which reference the selected cube member from the data validation cell.

I created a userform to try and make the selection of a bunch of parameters more simple and one of them is this value for the data validation cell. The issue that I'm having is that while my userform combobox does reference the named range (and thus the cube member list) when I pass it to the worksheet in the data validation cell I'm passing it as a string (cell.value = combobox.value) and it no longer is representing that as a selection from the named range of cube members...and thus the cube functions cease to function.

The question I have - is how can I use VBA to select a specific value from a data validation cell (because I think I need to select it from the data validation list rather than pasting the value of the title since it is a cube member). I've tried recording while selecting a member from the data validation list, but all that leaves me with is "Cell.select" with no indication that I've made a selection or changed the value in that cell. Is this possible?

Thanks in advance!

 

[ranman256]

yes, your recording in vb will only give definite spots where you went, but it can be altered to a more dynamic application.
Im sure it can do what you need, but Im still fuzzy on what you did exactly.

But if you need data moved/verified/etc, then vb can do it.

 

[largeselection]

Thanks for checking it out and responding!

Sorry if my description was unclear.

I'll try and clarify a little...My workbook has a named range which is range("A1:A10") for example. In those cells are cube member functions so for example they may display a name like "Tom", "Matt", "Judy" in the cells, but if you click in those cells to see the formula you would see something like "=CUBEMEMBER("Connection","[Employees].[EmployeeName].&[123456]").

Next, I have a cell (C1) which has a data validation to the named range. I have another cell which has a formula =CUBEVALUE("Connection",C1). This works great when I select a value from C1 using the data validation dropdown. And I'm assuming that it works because the Cube Value function is being passed the Cube Member function. So if I go to C1 and select the dropdown I can see the list "Tom, Matt, Judy", and if I select "Judy" then I think it is passing to the Cube Value function the reference that = "Judy" (or "=CUBEMEMBER("Connection",[Employee]...[123459]")).

However, the next layer of complexity, I've now added a userform which has a combobox which populates based on the named range that is in the data validation. I then try to pass the selected value in that combobox to cell C1, and that's where it fails. My hunch is that this is failing because I am passing just the string of what is showing in the combobox and thus losing the cubemember function that it needs because I'm using Range("C1").value = Combobox.value. So what I think might be happening is that I am passing "Judy" to cell C1 rather than "=CUBEMEMBER("Connection",[Employee]...etc"".

Which brings me to the question of is it possible to use VBA to select a specific value from a data validation cell dropdown?

I know it's confusing, but I appreciate you bearing with me.