Retrieve Cell Data Validation List into a UserForm ComboBox

[Maverick99]

If I have a Cell that has a drop-down Data Validation (DV) List within it, and a UserForm with a ComboBox, designed to replicate what is in that specific cell, how can I transfer those values to a UserForm ComboBox, without calling the procedures to regenerate those values?

I tried a frmUser.cbobox = .Cell.Value

It worked in part, but the Combobox showed the entire String to the User.

The purpose is if a Cell (could be any) shows "123" and the DV has a list of:

123
456
789

Then I'd like the ComboBox to show "123" and the entire list that is available in the Cell drop-down list.

Right now, replicating the procedure process of generating the cell values works fine, but it would not seem to be the most efficient way of transferring items from one known source (Cell) to another (Combobox). I'm using cells within a 2007 Table if that makes any difference.

Thanks,
Maverick

 

[Norie]

I was kind of asking what format the list you were using for the DV was stored in, and I think you've actually answered that.

If it was delimited, as I thought it might, Split was what I was going to suggest.

Either that or some way of extracting the individual list items so they could be added to the combobox.was going to recommend if the list was delimited.

 

[Maverick99]

Wigi,
I found the RowSource Method a little difficult to use, in fact I couldn't get it work as I needed. However, the formula below does exactly as I need. The [ ]'s are Defined Names and represent the position of the PID cell from the UserForm. Therefore, as the Table Index Number Changes, then the ComboBox is now automatically updated with the DV List contained in that specific cell.

  Me.cbo_PID.List = Split(Sheet3.Cells([n3frmIDXRow], [nProject_IDCol]).Validation.Formula1, ",")

This is important, because if the PID needs to be changed, I don't have to rerun the Bubble Sort based upon a Lab Name in another Cell. Until now, if the Bubble Sort wasn't run, then the PIDs from another row, or None at all, would be displayed and the user couldn't properly select the correct Number to represent the LAB. This number is actually what would get charged for services rendered.

However, your post provided me with the direction that I should take. I hadn't used the .Validation before, so thanks in providing me with another option to look at.

I may also be posting another issue that deals with Hiding a Row that has a Comment in one of the Cells. Up until now, when I tried to hide a row that a Comment would overlap, Excel wouldn't allow that to happen. But, now I need to Hide the actual row with the Comment, or maybe use VBA to Set Freeze Panes (which I haven't done before either), and then Scroll within a certain area. This is to keep the spreadsheet from becoming to Large for the user to navigate. But, that will be a little later.

Thanks again for your assistance...

Cheers,

Maverick