We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Cell DV Dropdown to UserForm Combobox

Medium Priority
531 Views
Last Modified: 2012-08-13
If I have a Cell that has a drop-down 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 running through 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.

However, if the Cell 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 available in a drop-down.

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.

Comment
Watch Question

Commented:
Are you looking to keep the values in the cells and show them in the combobox, or to migrate them out of the cell and put them into the combobox "permanently"?

And to show them properly in the combobox, I think you will need to convert them into an array.

this might work:

Sub AddDVListToComboBox(DVList as string)
  Dim strArrayList() as string
  Dim iCntr as integer

  strArrayList = split(DVList, ",")
  For iCntr = 0 to UBound(strArrayList)
    ComboBox1.AddItem(strArrayList(iCntr))
  Next
End Sub
Most Valuable Expert 2012
Top Expert 2012

Commented:
My approach is similar, plus displays the current data validation range value...  note, Range("E9") has a data validation and is hard coded in this example, for demonstration purposes.

 
Private Sub UserForm_Initialize()
Dim rng As Range
Dim strList() As String
Dim i As Integer

    Set rng = Range("E9")
    
    strList = Split(rng.Validation.Formula1, ",")
    
    For i = 0 To UBound(strList)
        cboDatavalidation.AddItem strList(i), i
    Next i

    cboDatavalidation.Value = rng.Value
    
End Sub

Open in new window


See attached,

Dave

PS - the only alternative to looping, is to store the data validation list somewhere under a named range, then you can set the listfillrange attribute of the combobox to that named range - presto - one command...
comboUserFormDV-r1.xls
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
Dave:
>>"the only alternative to looping, is to store the data validation list somewhere under a named range"

That's not true:

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

Open in new window


will work just fine. ;)

Rory

Author

Commented:
While I just got back, the answer to the question is that I want to keep the cell data as is, unless it needs to be changed due to a change within another cell. This query is to find out if it can just be replicated in a ComboBox, in a straightforward manner.  Since I have to use a Bubble Sort to generate the DV list for the Cell, I'm currently using it for the ComboBox as well. So, getting it to the ComboBox is not the issue.  Just wanted to know if there was anything more efficient.  After doing some reading late last night, would using something like:

.cbo =[Cell].validation.formula1

work?

I haven't used it before and don't know the ramifications of whether that is even an option to use.
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
What is .cbo?

Author

Commented:
I was in such a hurry, I just realized Rory used something similar.  Rory, is it possible to take it straight to a ComboBox with the intermediate step?

Author

Commented:
Sorry....WithOUT the intermediate step.
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
What intermediate step?

Author

Commented:
.cbo is what I abbreviated for ComboBox. as it is the prefix for my ComboBox Names. The  intermediate step would be the strList.

 strList = Split(rng.Validation.Formula1, ",")
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
That may be exactly what I'm looking for.  I'm in the process of running another test, and will get to it within a few minutes, but it looks like I may be able to save doing a lot of Bubble Sorts.
Most Valuable Expert 2012
Top Expert 2012

Commented:
@rorya - thanks for the tip on direct assignment (re: cboBox.List = string array)

@Cook09 - I have a generic routine you can use to populate comboboxes, ensuring distinct and sorted values.

See article here: http:/A_5062.html, and if you are interested, I would recommend using the routine:

Sub loadMyComboBoxUnique(cBox As ComboBox, Optional Sorted As Variant = False, Optional mLink As Variant = False)

The way this particular set of functions is written is on the presumption that the ComboBox has a named range (or address) in its listFillRange setting (the original goal being to sustain linkage to a named range/address, even though the contents would be a distinct/sorted list).  While I've developed follow-ons to this, especially with respect to Data Validation, this is not availabe in the set of functions in this Article, and Part II is in development which will not have a named range/address requirement for functionality - so, let me know if you're at all interested.

Again, if you're interested, and your data validation range is referencing an address range or named range, then you should be able to readily use the function mentioned...

Dave

Author

Commented:
Thanks, I'll look at it in just a little bit.  My computer at work just fried, so I've had to scrounge another one quickly, and get it set up. Plus, I have an implementation training to do in about an hour so things are pretty busy at the moment...but I do want to look at it.

Author

Commented:
Thanks David, I appreciated the article. I downloaded it and will get to it because it seems to address other issues that may help.  It will take me a while to digest it all.  Fortunately, Rory's solution was exactly what I needed and now has saved me a lot of processing time not having to duplicate Bubble Sorts.

Cook

Author

Commented:
It nailed it.-- a "10".  Exactly, what was needed.
Most Valuable Expert 2012
Top Expert 2012

Commented:
Great

Please vote on the article if you found it helpful!

Tkx

Dave
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.