Solved: Connect Combobox to Named Range

[YellowLabPro]

I came across this procedure on the Contextures site to increase the DV List of 8 rows to show all the rows of the list, avoiding the scroll bar. But in this example the code uses a fixed list on the page. I would like to swap a Named Range, ColorDB1 for the list on the page reference.
Could someone show me where to change the code?

http://www.contextures.com/xlDataVal10.html

[VBA]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("ValidationLists")

Cancel = True
Set cboTemp = ws.OLEObjects("TempCombo")
On Error Resume Next
With cboTemp
'clear and hide the combo box
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
End With
On Error GoTo errHandler
If Target.Validation.Type = 3 Then
'if the cell contains a data validation list
Application.EnableEvents = False
'get the data validation formula
str = Target.Validation.Formula1
str = Right(str, Len(str) - 1)
With cboTemp
'show the combobox with the list
.Visible = True
.Left = Target.Left
.Top = Target.Top
.Width = Target.Width + 5
.Height = Target.Height + 5
.ListFillRange = str
.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 = True

Set cboTemp = ws.OLEObjects("TempCombo")
On Error Resume Next
With cboTemp
.Top = 10
.Left = 10
.Width = 0
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
.Value = ""
End With

errHandler:
Application.EnableEvents = True
Exit Sub

End Sub
'====================================
'Optional code to move to next cell if Tab or Enter are pressed
'from code by Ted Lanham
Private Sub TempCombo_KeyDown(ByVal _
KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)
Select Case KeyCode
Case 9 'Tab
ActiveCell.Offset(0, 1).Activate
Case 13 'Enter
ActiveCell.Offset(1, 0).Activate
Case Else
'do nothing
End Select
End Sub[/VBA]

[rory]

You shouldn't need to change the code as far as I can see. Just set up the DV using your named range instead of a fixed address (don't forget the = sign!)

[YellowLabPro]

I already have the Named Range working in the Validation list on my sheet. When I double click on the combobox, I only get one cell row and it is blank.

[rory]

It works fine for me. Did you use a combo from the Control Toolbox? Does your defined range point at a static address?

[YellowLabPro]

Rory,
Mind having a look?

[rory]

You changed the code! Replace this line:
[VBA].ListFillRange = ws.Range(str).Address[/VBA]

with the original:
[VBA].ListFillRange = str[/VBA]

and it should be fine.

[YellowLabPro]

Thanks Rory,
That worked. But I did not touch the code!
Just to make sure I am not imagining something I went to the site and the code is exactly as is copied from there.
Thanks for solving that though. I should write the site and let them know.

[rory]

Ah - I assumed the code you posted above was copied from the site. The version from your first post is correct! The other version would work if your named range were on the same sheet as your validated cells.

[Norie]

str, mmmm!!!!

Would that be the VBA function Str?

[YellowLabPro]

Norie,
Don't know.... but I get what you are saying.

Rory- Thanks.
I copied the code from my module, which was copied from the site. I did not touch the code per say. But I cannot explain how it got changed. The only thing I can offer as support to this, is after I could not get it to work, I changed the ListFill Property to the Named Range Name. I wonder if this overrides physical written code?????????????
I would not know where to even begin looking to change something like this, so it has to be related.... don't you think?

[rory]

Nope! Changing one could not change the other. Obviously your subconscious mind knew how to correct it; the trick is to stop thinking about what you are doing - it works for me every time!

[YellowLabPro]

ok, ok....