[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 510
  • Last Modified:

Cell DV Dropdown to UserForm Combobox

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.

0
Cook09
Asked:
Cook09
  • 8
  • 4
  • 3
  • +1
1 Solution
 
rspahitzCommented:
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
0
 
dlmilleCommented:
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
0
 
Rory ArchibaldCommented:
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
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
Cook09Author 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.
0
 
Rory ArchibaldCommented:
What is .cbo?
0
 
Cook09Author 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?
0
 
Cook09Author Commented:
Sorry....WithOUT the intermediate step.
0
 
Rory ArchibaldCommented:
What intermediate step?
0
 
Cook09Author 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, ",")
0
 
Rory ArchibaldCommented:
Oh - yes you can skip that:
   cboDatavalidation.List = Split(rng.Validation.Formula1, ",")

Open in new window

0
 
Cook09Author 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.
0
 
dlmilleCommented:
@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
0
 
Cook09Author 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.
0
 
Cook09Author 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
0
 
Cook09Author Commented:
It nailed it.-- a "10".  Exactly, what was needed.
0
 
dlmilleCommented:
Great

Please vote on the article if you found it helpful!

Tkx

Dave
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

  • 8
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now