Why does using a named range return no value?

Question

I have one workbook with several sheets. I populate the listboxes (pulling static data from cells) on the 2nd sheet, click a button and it runs fine.

When I populate the listboxes with a named range, the listbox populates the way I want, but I get an error because the code thinks that I didn't select anything in the listbox, even though I did. So it passes through "" instead of "title".

Is this a common issue? The named range isn't a problem because it passes through the data to the listbox and I know it's selecting data because as soon as the listbox loses focus, it spits out the contents of the cell into cell A1.

What's even stranger is that I have the contents of the listbox set to Msg1. So A1 gets populated with Msg1 (what I actually selected in the listbox). But when I try and use Msg1 in the code, it tells me that Msg1 is "". Again, this only happens when I use the dynamic named range, not with static data in cells K1:K9.

Private Function strEndSQL1 As String

Dim strSQL As String

    strSQL = ""
            'Create SQL statement
        strSQL = "FROM (SELECT * FROM dbo.Filter WHERE ID = " & TextBox1.Text & " And Source IN (" & Msg1 & ")) a FULL OUTER JOIN "
        strSQL = strSQL & "(SELECT * FROM dbo.Filters WHERE ID = " & TextBox2.Text & " And Source IN (" & Msg1 & ")) b "
        strSQL = strSQL & "ON a.Group = b.Group    
    strEndSQL = strSQL

End Function

You may have used a sheet-specific named range, or have duplicate named ranges in the workbook. — Lance Roberts
Well, I was able to get a named range to work just fine with a list box (though as soon as I click on a selection in the listbox I get A1 to populate, not when I lose focus). I can only think it's something in the setup of the named range or the listbox, but there are too many possibilities to pin it down. — Lance Roberts
If that were the case, how does the listbox populate? On top of that, it spits out data when it loses focus (I didn't include that code). The only difference is that it references a named range instead of the actual range. — Daniel

Dick Kusleika Dick Kusleika · Accepted Answer · 2010-05-13T21:22:15

I'm not sure how you're filling the listbox, or whether the listbox is from the Forms toolbar or the Control Toolbox. If it's the latter, here's an example for populating and retrieving values.

Sub FillListBox()

    Sheet1.ListBox1.List = Sheet1.Range("MyNamedRange").Value

End Sub

Sub MakeSQL()

    Dim sSql As String
    Dim Msg1 As String

    With Sheet1.ListBox1
        If .ListIndex > -1 Then
            Msg1 = .Value
        End If
    End With

    sSql = "SELECT * FROM MyTable WHERE ID=" & Msg1

    Debug.Print sSql

End Sub

Note that the Value property will depend on what you've set in the BoundColumn property.

Why does using a named range return no value?

1 Answers