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

 

[wigi]

Maverick99

The code for the userform, where cell A1 of the active sheet contains the DV:

Private Sub UserForm_Initialize()
    Me.ComboBox1.RowSource = [A1].Validation.Formula1
    Me.ComboBox1.Value = [A1]
End Sub

 

[Norie]

Maverick

How have you set the list for the data validation?

Would it really be too much to use the same method for the combobox on the userform - surely they have the same values.

 

[Maverick99]

Norie,
That's what I'm doing now. If the user changes an Index Row number, then I have to run the DV to generate the list of Project IDs for a researcher on that specific row. This is done, so that what a user sees on the table matches what is seen on the UserForm. However, those values are already available within the project id portion of the table (cells).

Since I just checked the post, didn't receive an e-mail, I see that wigi has suggested a way of doing this. But, I also know you have been on this board for a few years and may also know of an efficient way to accomplish this task.

Wigi,

As you can read from my post to Norie, the UserForm is up and visible, so I'm not sure that setting the rowsource will work...I'll try...but since the cells can change frequently, [A1], then [A5], then [A10]...etc., it's not going to fire off the the Userform Initialize every time. However, I haven't tried the .Validation.Formula1 or something that approximates that. Maybe that's the key.

Maverick

Maverick.

 

[wigi]

A userform in VBA also has aUserForm_Activate() event if you prefer that one.

Maybe it would be better to first try and explore the possibilities, and after that providing your feedback.

 

[Norie]

Can you tell us how you are setting the list for the data validation?

Are the items in the list determined on some factor/criteria?

 

[Maverick99]

Norie,

I'm using a Bubble Sort to generate the initial DV list. The criteria for the Sort is based upon the Name in another cell. In theory, if the name, let's say Cell "C1" changes, then the Bubble Sort is activated through an Event procedure and the List is placed in "D1." Since the Name in "C1" could change, so then does the DV List in "D1." Which all of that is working great.

Right now, if the Name in "C1" changes, I use the Bubble Sort to also send the values to the ComboBox, and that works great. But, if my UserForm is told to grab the data in row 1, I would rather just pull the DV data from Cell "D1" instead of rerunning the Bubble Sort, which is what I'm doing now. It works, with no issues, but is there an easier way to pull the DV data instead of pushing it through a Sort?

Maverick

 

[Norie]

Why would you need to rerun the bubble sort if the data for the combobox is the same as that for the DV?

How exactly are you storing it in D1?

 

[wigi]

but is there an easier way to pull the DV data instead of pushing it through a Sort?

Have you looked at my earlier code?

 

[Maverick99]

Wigi,

Yes I have. The past couple of days I've been scrambling since my computer at work "fried." I have a loaner at the moment, but as you know, it's never the same.

Actually, there are two ways I'm going to attempt this in a few moments. One is with yours and the other is:

cboDatavalidation.List = Split(rng.Validation.Formula1, ",")

cboDatavalidation is a name to represent a ComboBox.


Hopefully one of the two will be what I need.




</PRE>
Norie,



</PRE>
I'm storing the DV in a regular Drop-Down List. However, the list changes as the values in a particular cell changes.</P>









</PRE>