Populating a LISTBOX based on sheet name

[anusha]

Hello,

I am new to this forum and a novice at VBA programming.

I created my first app this week and have a few questions.

1. I created a list box which needs to be populated based on another listbox selection value.

For eg.

If the fist list box selection is "SUBS" from the listbox, then listbox2 should be populated with data from column A in a sheet named "SUBS"

If the fist list box selection is "BHCK" from the listbox, then listbox2 should be populated with data from column A in a sheet named "BHCK"

How do I do this?

Thanks in advance for any help.

~Anusha

[Killian]

Hi Anusha and welcome to VBAX

in each case, first clear the listbox, then loop though the values you need and add them with the AddItem method
For the first list box, you loop through each worksheet name in the worksheets collection
For the second, you use the selected item in listbox 1 for the worksheet name and loop through each cell in the range on that sheet[VBA]Private Sub UserForm_Initialize()
'Load the first listbox with worksheet names
Dim ws As Worksheet

ListBox1.Clear
For Each ws In ActiveWorkbook.Sheets
ListBox1.AddItem ws.Name
Next

End Sub

Private Sub ListBox1_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, _
ByVal X As Single, ByVal Y As Single)
'load the second listbox with value in defined range
'using Range("A1:A10") for this example
Dim c As Range

ListBox2.Clear
'refer to the sheet name by using the selected value in listbox1
For Each c In ActiveWorkbook.Sheets(ListBox1.Value).Range("A1:A10")
ListBox2.AddItem c.Value
Next

End Sub[/VBA]I've assumed these are on a user form. If they're worksheet objects, you'll need to use a different event to load the first listbox (like worksheet_acitvate)

Hope that helps

[anusha]

Where do I put this code? Can I send you the file? How do I do I send it?

Thanks in advance for your help.

[Killian]

I've attached an example.
If you need to post back an attachment, click the "Go Advanced" button below the Quick Reply text window, scroll down to the Additional Options section and select Manage Attachments. (If you're sending a workbook, you'll need to compress it to a ZIP file first)

[anusha]

Wow ... Thanks. Now I have more questions..

1. I included my workbook.

2. In the Analysis workbook: There is a combobox in D1 and a listbox in E1

when I make a selction from D1 it should loop through the sheets t the bottom and find the name just like you did. So, I won't have a load data button. It should do the clear and load the data to E1 when I make a combobox selction in D1. So do I put your code in in the D1 VBA code?

3. Also, how can I get I4 to reflect the selection in the E1 selection box?

Thanks for all your help.

~Anusha

[Killian]

OK, have a look at the code behind teh ANALYSIS sheet... I've made the changes I think you need and added some comments. Hopefully it's clear what's going on.
I use the sheet's activate event to populate the combo box
The combo box's change event to populate the list box
The list box's click event to update cell I4

If you want to keep the code this simple (not a bad idea) you need to make sure that each sheet you add to the combobox has a range of the same name you want to go into the listbox

[anusha]

Awesome !!! Thank you so much ofr helpng me through this project.

~Anusha

[MOS MASTER]

Hi and Welcome to VBAX Anusha!

Don't forget to mark your thread solved if it has been.

[anusha]

K,

The one problem I see is the get data button now does not run macro2 and Maco two should heve the information from the listbox and combbox in order to run...

Sorry I didn't mention this earlier...

What do you recommend?

[anusha]

K, I have attached the file adding macro2 to the get data botton VBA code. But I guess the macro does not know where to get the information for the E1 selection VarItem. I assume it know VarSeries. Are these definitions in the macro code still good?

Thanks for your continued help.

~Anusha

[Killian]

Ahh... I think I see what you mean.

Well the problem is that we need to make sure that listbox1 and combobox1 are always initialized.
To do this, I've added[VBA]Listbox1.ListIndex = 0[/VBA]to the combobox change event code to make sure each time it's re-populated, the first item is selected.
Also, when the work book os opened, we need to make sure the ANALYSIS sheet's activate code is fired to populate the combo box, so I've added some code in the workbook_open event to activate sheet 1, then sheet "ANALYSIS", which should do the trick.
I didn't look too closely at what marco2 does... Does that fix it?

[anusha]

K,

The get button now works. But the macro behind it has a SQL error. I assume it the variables need to be adjusted to pull the information correctly.. Here's the macvro code behind the get data button.[vba]Sub Macro2()

Dim varDate As String
varDate1 = Range("g2").Value

Dim varDate2 As String
varDate2 = Range("h2").Value

Dim varDate3 As String
varDate3 = Range("i2").Value

Dim varItem As String
varItem = Worksheets("ANALYSIS").ListBox1.Text

Dim varSeries As String
varSeries = Worksheets("ANALYSIS").ComboBox1.Text
If dtl = True Then
Worksheets("ANALYSIS").Range("I4") = Worksheets("ANALYSIS").ComboBox1.Value
End If

Dim varGroup As String
varGroup = Range("f2").Value
'
' Macro2 Macro
' Macro recorded 9/9/2005 by M1AFD00
'
'
With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DSN=M1DB2P;UID=m1afd00;;MODE=SHARE;DBALIAS=M1DB2P;", Destination:=Range _
("A10"))
.CommandText = Array( _
"select " & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "c.nm_lgl," & Chr(13) & "" & Chr(10) & "c.auth_frd_Updt ," & Chr(13) & "" & Chr(10) & "a.id_rssd, " & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "E." & varItem & "," & Chr(13) & "" & Chr(10) & "B." & varItem & ", " & Chr(13) & "" & Chr(10) & "A." & varItem & "" & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "from " & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "fdrP.cuv_" & varSeries & "01 a, " & Chr(13) & "" & Chr(10) & "fdrP.cuv_" & varSeries & "01 b, " & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "fdrP.cuv_" & varSeries & "01 E," & Chr(13) & "" & Chr(10) & "nicua.cuv_attr_mr c" & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "where" _
, _
"" & Chr(13) & "" & Chr(10) & " A.dt = " & varDate1 & " " & Chr(13) & "" & Chr(10) & "AND A." & varGroup & " " & Chr(13) & "" & Chr(10) & "AND B.DT=" & varDate2 & " " & Chr(13) & "" & Chr(10) & "AND A.ID_RSSD = B.ID_RSSD " & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "AND E.DT=" & varDate3 & " " & Chr(13) & "" & Chr(10) & "AND B.ID_RSSD = E.ID_RSSD " & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "AND A.id_rssd = c.id_rssd and c.dt_end = 99991231 " _
)
.Name = "Query from M1DB2P"
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub[/vba]Thanks again ...

[Killian]

OK, well I'm not going to be able to debug your SQL query since I don't have access to your data, but whats happening here is that the .commandtext property of your query is being built up to include the variables - as long something is selected in the combo and list box, you should have values there. I just tested it and it seems fine.

I'm, a bit confused as to why the command text is an array - I would expect it to be a simple string. I've tidied it up a little and added it in as a string variable so you can see a bit more clearly how it's working. Try this...[VBA]Sub Macro2()

Dim varDate As String
varDate1 = Range("g2").Value
Dim varDate2 As String
varDate2 = Range("h2").Value
Dim varDate3 As String
varDate3 = Range("i2").Value
Dim varItem As String
varItem = Worksheets("ANALYSIS").ListBox1.Text
Dim varSeries As String
varSeries = Worksheets("ANALYSIS").ComboBox1.Text
Dim varGroup As String
varGroup = Range("f2").Value

'initialize the SQL string to pass to the query
Dim strSQL As String
strSQL = "SELECT " & "c.nm_lgl," & Chr(13) & _
"c.auth_frd_Updt," & Chr(13) & _
"a.id_rssd," & Chr(13) & _
"E." & varItem & "," & Chr(13) & _
"B." & varItem & "," & Chr(13) & _
"A." & varItem & Chr(13) & _
"FROM " & Chr(13) & _
"fdrP.cuv_" & varSeries & "01 a, " & Chr(13) & _
"fdrP.cuv_" & varSeries & "01 b, " & Chr(13) & _
"fdrP.cuv_" & varSeries & "01 E," & Chr(13) & _
"nicua.cuv_attr_mr c" & Chr(13) & _
"WHERE" & Chr(13) & _
"A.dt = " & varDate1 & Chr(13) & _
"AND A." & varGroup & Chr(13) & _
"AND B.DT=" & varDate2 & Chr(13) & _
"AND A.ID_RSSD = B.ID_RSSD" & Chr(13) & _
"AND E.DT=" & varDate3 & " " & Chr(13) & _
"AND B.ID_RSSD = E.ID_RSSD " & Chr(13) & _
"AND A.id_rssd = c.id_rssd and c.dt_end = 99991231"

With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DSN=M1DB2P;UID=m1afd00;;MODE=SHARE;DBALIAS=M1DB2P;", _
Destination:=Range("A10"))
.CommandType = xlCmdSql
.CommandText = strSQL
.Name = "Query from M1DB2P"
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub[/VBA]

[anusha]

WooHoooooooo !!!!

Thank you soooooo much. It is working. But give me a day or so to test it in case I have more questions.

Thanks again...

I am absolutely delighted.

~Anusha

[anusha]

One very last ... Why does the combo box keep shrinking whenever I open it? I have to readjust the size every time?

It currently looks the way it should in the jpg.

[Killian]

Cool, glad it's working out for you

I noticed that listbox resizing thing - very strange... I don't do too much stuff with worksheet controls, so I may be missing something obvious but you can fix it with a quick addition to the code[VBA]Private Sub ComboBox1_Change()
Dim c As Range

With ListBox1
If ComboBox1.Value <> "" Then
.Clear
For Each c In ActiveWorkbook.Sheets(ComboBox1.Value).Range(ComboBox1.Value)
.AddItem c.Value
Next c
.ListIndex = 0
.Width = 80 '<--that should do it
End If
End With
End Sub
[/VBA]

[anusha]

K,

Sorry to ask for help again ... I am battling with how to send the selection from the listbox to the query. It was easy when all the items were from the same table. Now, the table name could be anything.

This means I will have to read off clumn B in the sheets such as "BHCF" and when this sheeet is slected and an item from it, I need to also now figure out how to read the next coulmn B to pull the table name, and send it to Macro2.

SELECT
c.nm_lgl,
c.auth_frd_Updt,
a.id_rssd,
a."&VarItem&" ---> to get this BHCK2170

FROM
fdrP.cuv_"&series&""&tablenum&" a, ---> to get this BHCF01 a,
nicua.cuv_attr_mr c

WHERE
A.dt in ("&Vardate1&", "&Vardate2&", "&Vardate3&") --> to get this(20040930, 20040630, 20040331)

AND A.ID_RSSD = C.ID_RSSD
AND A.id_rssd = c.id_rssd
and c.dt_end = 99991231

How do I do this?

Thanks in advance.

~Anusha

[Killian]

Hi again...
No need to apologise for asking for help - it's pretty much what we do here

So I think I understand what you wanted...
I've added another column to the list box (you'll see you can scroll it right now) and the combo box change code adds the table value one to the right of the original value) so we can get to it in Macro2.
In Macro2 I've added a another variable that initializes from that column in the listbox and I've had a play around with the SQL string building code to reflect your changes (I think).

If you need to fine-tune it, I've put just the SQL string building code in Module1. This uses Debug.Print to output the string to the Immediate window (ctrl G in the VBE) so you can just run that to check what it's doing.

[anusha]

Thanks for your time. But the macro does not run. I am getting a runtime error. I also modified the query and took off a.id_rssd = a.id_rssd

and the Icing on the cake would be to pull the info in column c of the selected sheet for series and display the item descrition in I5.

So what am mI doing wrong with the macro?

Sub Macro2()
Dim varDate As String
varDate1 = Range("g2").Value
Dim varDate2 As String
varDate2 = Range("h2").Value
Dim varDate3 As String
varDate3 = Range("i2").Value
Dim varItem As String
varItem = Worksheets("ANALYSIS").ListBox1.Text
Dim varTable As String
varTable = Worksheets("ANALYSIS").ListBox1.List(Worksheets("ANALYSIS").ListBox1.ListIn dex, 1)
Dim varSeries As String
varSeries = Worksheets("ANALYSIS").ComboBox1.Text
Dim varGroup As String
varGroup = Range("f2").Value

'initialize the SQL string to pass to the query
Dim strSQL As String
strSQL = "SELECT " & vbLf & _
"c.nm_lgl," & vbLf & _
"c.auth_frd_Updt," & vbLf & _
"a.id_rssd," & vbLf & _
varItem & vbLf & _
"FROM " & vbLf & _
"fdrP.cuv_" & varTable & " a" & vbLf & _
"nicua.cuv_attr_mr c" & vbLf & _
"WHERE" & vbLf & _
"A.DT IN (" & varDate1 & ", " & varDate2 & ", " & varDate3 & ")" & vbLf & _
"AND A.id_rssd = c.id_rssd" & vbLf & _
"AND C.dt_end = 99991231"

Debug.Print strSQL

With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DSN=M1DB2P;UID=m1afd00;;MODE=SHARE;DBALIAS=M1DB2P;", _
Destination:=Range("A10"))
.CommandType = xlCmdSql
.CommandText = strSQL
.Name = "Query from M1DB2P"
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub


+++++++++++++++++++ I MESSED UP THE QUERY +++++++++++

This is the query that is correct. And I have to incorporate the VarTable...
I'll give it a shot with you.

With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DSN=M1DB2P;UID=m1afd00;;MODE=SHARE;DBALIAS=M1DB2P;", Destination:=Range _
("A10"))
.CommandText = Array( _
"select " & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "c.nm_lgl," & Chr(13) & "" & Chr(10) & "c.auth_frd_Updt ," & Chr(13) & "" & Chr(10) & "a.id_rssd, " & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "E." & varItem & "," & Chr(13) & "" & Chr(10) & "B." & varItem & ", " & Chr(13) & "" & Chr(10) & "A." & varItem & "" & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "from " & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "fdrP.cuv_" & varSeries & "01 a, " & Chr(13) & "" & Chr(10) & "fdrP.cuv_" & varSeries & "01 b, " & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "fdrP.cuv_" & varSeries & "01 E," & Chr(13) & "" & Chr(10) & "nicua.cuv_attr_mr c" & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "where" _
, _
"" & Chr(13) & "" & Chr(10) & " A.dt = " & varDate1 & " " & Chr(13) & "" & Chr(10) & "AND A." & varGroup & " " & Chr(13) & "" & Chr(10) & "AND B.DT=" & varDate2 & " " & Chr(13) & "" & Chr(10) & "AND A.ID_RSSD = B.ID_RSSD " & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "AND E.DT=" & varDate3 & " " & Chr(13) & "" & Chr(10) & "AND B.ID_RSSD = E.ID_RSSD " & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "AND A.id_rssd = c.id_rssd and c.dt_end = 99991231 " _
)

[anusha]

K,

Here's how I hard coded contents of I5 prior to going VBA.

=LOOKUP(ANALYSIS!I4,BHCK!A:A,BHCK!C:C)

How do I incorporate this into the VBA macro?

~Anusha