Combobox List Fill Range Code

[sarahrb1989]

I am new to using the Developer tab and Combobox's in Excel. I am working on a spreadsheet for work and want to use some code I found online. I want the combobox to pull data from multiple (dynamic, if possible) data validation lists (demographic info) on a separate sheet. The sheet I want to use the Combobox on is called, General Wait List (sheet1) and the sheet that has the demographics list options is called, demographic options (sheet3). Here is where you can find the sample file: http://www.contextures.com/excelfiles.html#DataVal

Search for the following:
DV0057 - Data Validation Combobox Codes -- Double-click a cell that contains a data validation list, and a combobox appears, showing a list of descriptions. Select a descriptions, and that descriptions numeric code is entered in the cell. Lists are stored in named ranges on a separate sheet. Excel 2007/2010 format; macros must be enabled. DataValComboboxCodes.zip 30 kb 02-May-12

I can't figure. out what range to list or code. I'm also not sure if I should be listing the range in the properties tab of the combobox itself or just edit the code. Any help would be greatly appreciated!!!

Please let me know if there is any other info needed.



Here is the portion of code I'm attempting to use:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Dim wsList As Worksheet
Set ws = ActiveSheet
Set wsList = Sheets("GENERAL WAIT LIST")
Cancel = True
Set cboTemp = ws.OLEObjects("ComboBox1")
On Error Resume Next
With cboTemp
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
End With
On Error GoTo errHandler
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
str = Target.Validation.Formula1
str = Right(str, Len(str) - 1)
With cboTemp
.Visible = True
.Left = Target.Left
.Top = Target.Top
.Width = Target.Width + 15
.Height = Target.Height + 5
.ListFillRange = str & "Codes"
.LinkedCell = Target.Address
End With
cboTemp.Activate
End If

errHandler:
Application.EnableEvents = True
Exit Sub
End Sub

 

[Worf]

Welcome to the Board

I do not quite understand what you want to do. If there are multiple lists, how can we know which ones to use?
The sample sheet has two 2-column lists, how many lists do you have? On the combo box sheet, the control will pick a list depending on what column it is; how many columns do you have on this sheet? The sample sheet has two, weekday and month. Please describe your worksheets’ layout.
Can you successfully use the sample file as offered by the site?

 

[sarahrb1989]

Thank you. I am using 5 lists of demographic options (gender, race, ethnicity, etc. ). The sample file has two lists and so I know it is an option to use more than one. The sample file does work successfully. I want the combo box to use the info from the data validation lists I created for the different demographic information just like in the sample file. I have gotten part of the code to work where the combo box will jump over the cell that has the data validation list already in it, after double clicking BUT the combo box list is blank. I hope this makes sense. It is easiest if you check the sample file that is on the website.

Welcome to the Board

I do not quite understand what you want to do. If there are multiple lists, how can we know which ones to use?
The sample sheet has two 2-column lists, how many lists do you have? On the combo box sheet, the control will pick a list depending on what column it is; how many columns do you have on this sheet? The sample sheet has two, weekday and month. Please describe your worksheets’ layout.
Can you successfully use the sample file as offered by the site?

 

[dmt32]

Hi,
Try changing this line

Set wsList = Sheets("GENERAL WAIT LIST")

to the name of worksheet with your lists

Set wsList = Sheets("Demographic Options")

each of your named ranges for the data validation lists needs another named range using Validation Name & "Codes"

E.G
Validation Named Range "MonthList"
ComboBox Named Range "MonthListCodes"

The Combobox Named Range is over two columns & returns the values shown in first column only.

I also noticed a possible error in one of the codes in downloaded example

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet
Application.EnableEvents = False
Application.ScreenUpdating = False


If Application.CutCopyMode Then
  'allows copying and pasting on the worksheet
  GoTo errHandler
End If


Set cboTemp = ws.OLEObjects("TempCombo")


  On Error Resume Next
  With cboTemp
    .Top = 10
    .Left = 10
    .Width = 0
    .ListFillRange = ""
    .LinkedCell = ""
    .Visible = False
    .Value = ""
    .Object.Value = ""
  End With


errHandler:
  Application.ScreenUpdating = True
  Application.EnableEvents = True
  Exit Sub


End Sub

Delete line shown in RED & replace with line in BLUE

Hope Helpful

Dave

 

[sarahrb1989]

Thank you, I will try this. What I'm not sure about is what code to list in here since I am trying to pull from multiple lists. .ListFillRange = "what do I put here?"

As an example I have the columns set up like this:
Gender Codes Gender
1 Male
2 Female
3 Other

The problem is I have 5 of them and they are on another sheet. I don't know how to get the code to find the sheet the lists are on and then pull from those different lists to use on the first sheet where the data is being inputted.

Hope this makes sense.

Hi,
Try changing this line

Set wsList = Sheets("GENERAL WAIT LIST")

to the name of worksheet with your lists

Set wsList = Sheets("Demographic Options")

each of your named ranges for the data validation lists needs another named range using Validation Name & "Codes"

E.G
Validation Named Range "MonthList"
ComboBox Named Range "MonthListCodes"

The Combobox Named Range is over two columns & returns the values shown in first column only.

I also noticed a possible error in one of the codes in downloaded example

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet
Application.EnableEvents = False
Application.ScreenUpdating = False


If Application.CutCopyMode Then
  'allows copying and pasting on the worksheet
  GoTo errHandler
End If


Set cboTemp = ws.OLEObjects("TempCombo")


  On Error Resume Next
  With cboTemp
    .Top = 10
    .Left = 10
    .Width = 0
    .ListFillRange = ""
    .LinkedCell = ""
    .Visible = False
    .Value = ""
    .Object.Value = ""
  End With


errHandler:
  Application.ScreenUpdating = True
  Application.EnableEvents = True
  Exit Sub


End Sub

Delete line shown in RED & replace with line in BLUE

Hope Helpful

Dave

 

[dmt32]

Thank you, I will try this. What I'm not sure about is what code to list in here since I am trying to pull from multiple lists. .ListFillRange = "what do I put here?"

leave it as published & make no change

As an example I have the columns set up like this:
Gender Codes Gender
1 Male
2 Female
3 Other

The problem is I have 5 of them and they are on another sheet. I don't know how to get the code to find the sheet the lists are on and then pull from those different lists to use on the first sheet where the data is being inputted.

Hope this makes sense.

I thought I had explained what to do in my post.

this line

Set wsList = Sheets("Demographic Options")

in the procedure refers to the worksheet with your validation lists.

The code will refer to the correct list based on it's validation named range by these two lines of code

str = Target.Validation.Formula1
str = Right(str, Len(str) - 1)

The ListfillRange for the combobox is then added as shown below

      With cboTemp
            .Visible = True
            .Left = Target.Left
            .Top = Target.Top
            .Width = Target.Width + 15
            .Height = Target.Height + 5
            .ListFillRange = str & "Codes"
            .LinkedCell = Target.Address
        End With

This refers to the named range for the combobox list ending in "Codes"

You can add as many lists as required - just ensure that you add both Validation Named Range & Combobox Named Range which must be the same name suffixed with "Codes" to the name manager.

Dave

 

[sarahrb1989]

Thank you, I will try this.

leave it as published & make no change



I thought I had explained what to do in my post.

this line

Set wsList = Sheets("Demographic Options")

in the procedure refers to the worksheet with your validation lists.

The code will refer to the correct list based on it's validation named range by these two lines of code

str = Target.Validation.Formula1
str = Right(str, Len(str) - 1)

The ListfillRange for the combobox is then added as shown below

      With cboTemp
            .Visible = True
            .Left = Target.Left
            .Top = Target.Top
            .Width = Target.Width + 15
            .Height = Target.Height + 5
            .ListFillRange = str & "Codes"
            .LinkedCell = Target.Address
        End With

This refers to the named range for the combobox list ending in "Codes"

You can add as many lists as required - just ensure that you add both Validation Named Range & Combobox Named Range which must be the same name suffixed with "Codes" to the name manager.

Dave

 

[sarahrb1989]

I've tried it and the comb box is still not pulling the information. Where do I add the Validation Named Range and where do I add the Combobox Named Range? Can you please check and tell me which one is correct? .ListFillRange = str & "city_of_residence_codes"
.ListFillRange = "city_of_residence_codes"
.ListFillRange = city_of_residence & "city_of_residence_codes"

Also how do I add more than one? Just use a comma? Please forgive me, I don't know anything about code.

 

[dmt32]

Hi,
The ONLY changes you should need to make to the published code are to these lines


1 - the sheet with your data validation lists

Set wsList = Sheets("ValidationLists")

Change the name shown in RED as required

2 - the name of your combobox

Set cboTemp = ws.OLEObjects("TempCombo")

Change the name shown in RED as required

3 - Combobox ListFillRange Named Range Naming Convention

With cboTemp
            .Visible = True
            .Left = Target.Left
            .Top = Target.Top
            .Width = Target.Width + 15
            .Height = Target.Height + 5
            .ListFillRange = str & "Codes"
            .LinkedCell = Target.Address
        End With

In the published example, naming convention is as follows:

- Data Validation Named Range "MonthList"
- ComboBox Named Range "MonthListCodes"

If you have a different naming convention where you place an underscore between the names

- "city_of_residence_codes"

then adjust this line as follows

.ListFillRange = str & "_codes"

Dave

 

[sarahrb1989]

Okay, so it is working now. The only problem is that it is showing me the code for each demographic and not the full description. I'm guessing something just needs to be re arranged. For example, in the combo box drop down menu it is showing M instead of Male.

I want the drop down menu to show the full demographic name and upon inputting it switch to the code. For example, I want it to show Male in the drop down and then when input, it change to the _codes, M.

Any advice is greatly appreciated!!

[CODE]Option Explicit
' Developed by Contextures Inc.
' [URL="http://www.contextures.com"]www.contextures.com[/URL]
Private Sub TempCombo_KeyDown(ByVal _
        KeyCode As MSForms.ReturnInteger, _
        ByVal Shift As Integer)
    'Hide combo box and move to next cell on Enter and Tab
    Select Case KeyCode
        Case 9
            ActiveCell.Offset(0, 1).Activate
        Case 13
            ActiveCell.Offset(1, 0).Activate
        Case Else
            'do nothing
    End Select
End Sub
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Dim wsList As Worksheet
Set ws = ActiveSheet
Set wsList = Sheets("Demographics Options")
Cancel = True
Set cboTemp = ws.OLEObjects("Combobox2")
  On Error Resume Next
  With cboTemp
    .ListFillRange = ""
    .LinkedCell = ""
    .Visible = False
  End With
On Error GoTo errHandler
  If Target.Validation.Type = 3 Then
    Application.EnableEvents = False
    str = Target.Validation.Formula1
    str = Right(str, Len(str) - 1)
    With cboTemp
      .Visible = True
      .Left = Target.Left
      .Top = Target.Top
      .Width = Target.Width + 15
      .Height = Target.Height + 5
      .ListFillRange = str & "_Codes"
      .LinkedCell = Target.Address
    End With
    cboTemp.Activate
  End If
  
errHandler:
  Application.EnableEvents = True
  Exit Sub
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet
Application.EnableEvents = False
Application.ScreenUpdating = False
If Application.CutCopyMode Then
  'allows copying and pasting on the worksheet
  GoTo errHandler
End If
Set cboTemp = ws.OLEObjects("ComboBox2")
  On Error Resume Next
  With cboTemp
    .Top = 10
    .Left = 10
    .Width = 0
    .ListFillRange = ""
    .LinkedCell = ""
    .Visible = False
    .Object.Value = ""
  End With
errHandler:
  Application.ScreenUpdating = True
  Application.EnableEvents = True
  Exit Sub
End Sub

[/CODE]