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

 

[dmt32]

Hi,
you need to ensure that the named Range for Combobox is across both columns

e.g.

Name: DayListCodes

='Demographics Options'!$B$2:$C$8


Dave

 

[sarahrb1989]

It is, I double checked every list. I'm guessing it is hiding the wrong column?

 

[dmt32]

In design mode, check your combobox ColumnCount value is 2 & BoundColumn is 1

Dave

 

[sarahrb1989]

Okay, I updated the column count value to 2. Now the combo box drop down is giving me both columns information but not hiding the first column. Thanks again for all of your help!

 

[dmt32]

sorry forgot to include

ColumnWidths 0 pt;100 pt

Dave

 

[sarahrb1989]

It is working! Thank you.

One last thing if you don't mind! When I click enter, it does not move to the next cell. It is making me click out of it. Could you check this part of the 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

 

[dmt32]

Oh my gosh, thank you so much! It is finally working. I have been trying to get this done for two weeks. I really appreciate all your help!

That's ok glad we got there in the end

Merry Christmas

Dave

 

[sarahrb1989]

One last thing if you don't mind! When I click enter, it does not move to the next cell. It is making me click out of it. Could you check this part of the code?


Option Explicit
' Developed by Contextures Inc.
' www.contextures.com
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

 

[dmt32]

If you have named your combobox differently to the published name shown in RED then you will need to update the procedure with correct name.

Private Sub TempCombo_KeyDown(ByVal _
        KeyCode As MSForms.ReturnInteger, _
        ByVal Shift As Integer)

Dave

 

[sarahrb1989]

Thank you!! Happy Holidays!