Named Range Breaks Code

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

Answer 1

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.