Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Listbox data display problem
I have a multi-column listbox on sheet one of an Excel 2003 workbook.
I'm importing two .CSV files on pages two and three of thw workbook, respectively. My boss wants to have the sheet load with the data already visible and accessible, so he vetoed using a UserForm. From sheet three, I'm selecting a range and want to populate the listbox on sheet three. I've got the correct range selected, but when I try to populate the listbox, it comes up blank. I can't seem to get the data to display in the listbox. When I step through the code with the debugger, I can see that the LISTCOUNT is correct, but still, you can't see the results. I'm attaching the code below. Too bad that the RowSource propert is not available in this case... The code is from a command button I added in order to test the code, so I would have problems wrestling with the workbook's OPEN event. Any clues to this mysterious (to me, at least) behavior would be greatly appreciated. Henry DPM Mellon = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = Private Sub cmdPopulateListBox_Click() Dim MyRange As Variant Dim DestRange As Range Dim lnFoo Application.ScreenUpdating = False Sheets(3).Activate intLastRow = ActiveSheet.Range("A65536").End(xlUp).Row strLastRow = "F" & Trim(Str(intLastRow)) sTest = "C2:" & strLastRow Sheets(3).Range(sTest).Select lnFoo = MsgBox("MyRange = " & sTest, vbOKOnly, "Test") MsgBox "The selection object type is " & TypeName(Selection) Sheets(1).Activate ListBox1.Activate ListBox1.Locked = False ListBox1.ListFillRange = "" ListBox1.ListFillRange = sTest ListBox1.Locked = True Application.ScreenUpdating = True End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Listbox data display problem
Try changing this line
sTest = "C2:" & strLastRow to sTest = "'" & Sheets(3).Name & "'!C2:" & strLastRow -- HTH RP (remove nothere from the email address if mailing direct) "Henry" wrote in message ... I have a multi-column listbox on sheet one of an Excel 2003 workbook. I'm importing two .CSV files on pages two and three of thw workbook, respectively. My boss wants to have the sheet load with the data already visible and accessible, so he vetoed using a UserForm. From sheet three, I'm selecting a range and want to populate the listbox on sheet three. I've got the correct range selected, but when I try to populate the listbox, it comes up blank. I can't seem to get the data to display in the listbox. When I step through the code with the debugger, I can see that the LISTCOUNT is correct, but still, you can't see the results. I'm attaching the code below. Too bad that the RowSource propert is not available in this case... The code is from a command button I added in order to test the code, so I would have problems wrestling with the workbook's OPEN event. Any clues to this mysterious (to me, at least) behavior would be greatly appreciated. Henry DPM Mellon = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = Private Sub cmdPopulateListBox_Click() Dim MyRange As Variant Dim DestRange As Range Dim lnFoo Application.ScreenUpdating = False Sheets(3).Activate intLastRow = ActiveSheet.Range("A65536").End(xlUp).Row strLastRow = "F" & Trim(Str(intLastRow)) sTest = "C2:" & strLastRow Sheets(3).Range(sTest).Select lnFoo = MsgBox("MyRange = " & sTest, vbOKOnly, "Test") MsgBox "The selection object type is " & TypeName(Selection) Sheets(1).Activate ListBox1.Activate ListBox1.Locked = False ListBox1.ListFillRange = "" ListBox1.ListFillRange = sTest ListBox1.Locked = True Application.ScreenUpdating = True End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Listbox data display problem
Wow- fast again!
This looks interesting. I'll have to give it a shot tomorrow morning. Can you tell me why the string should be formatted like this? I never would have come up with this. It sure doesn't look intuitive, and I haven't found any reference that even hints at this. Thanks! Henry Bob Phillips wrote: Try changing this line sTest = "C2:" & strLastRow to sTest = "'" & Sheets(3).Name & "'!C2:" & strLastRow |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Listbox data display problem
Go to sheet3.
select A1 and copy it. back to sheet1 select an empty cell. edit|paste special|click the paste link button. You'll see something like: =Sheet3!$A$1 or ='Sheet 3'!$A$1 Bob built the string that included those apostrophes. Another way is to let excel do the heavy lifting. Option Explicit Private Sub cmdPopulateListBox_Click() Dim MyRange As Range With Sheets(3) Set MyRange = .Range("c2:F" & .Cells(.Rows.Count, "A").End(xlUp).Row) End With With Sheets(1).ListBox1 .ColumnCount = 4 .ListFillRange = MyRange.Address(external:=True) End With End Sub MyRange.Address(external:=True) will return a string like: [book1.xls]Sheet2!$C$2:$F$10 ===== And sheets(3) and sheets(1) refer to the relative position of the worksheets. I'd try to use something like: worksheets("Sheet1") and worksheets("sheet 99") (if the user can't rename those sheets) It scares me more that the user could rearrange the sheets--yeah, yeah. I should be worried about them renaming the sheets, too! Henry wrote: Wow- fast again! This looks interesting. I'll have to give it a shot tomorrow morning. Can you tell me why the string should be formatted like this? I never would have come up with this. It sure doesn't look intuitive, and I haven't found any reference that even hints at this. Thanks! Henry Bob Phillips wrote: Try changing this line sTest = "C2:" & strLastRow to sTest = "'" & Sheets(3).Name & "'!C2:" & strLastRow -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Listbox data display problem
Try to use user-independent names for sheets.
In VBA in Properties window for the sheet change its Name from Sheet1 to WhatEver you want. From there on you can use WhatEver instead as an internal name. Users will be free to move / rename tabs. e.g. WhatEver.ListBox1.ColumnCount = 4 HTH Paul Uzytkownik "Dave Peterson" napisal w wiadomosci ... Go to sheet3. select A1 and copy it. back to sheet1 select an empty cell. edit|paste special|click the paste link button. You'll see something like: =Sheet3!$A$1 or ='Sheet 3'!$A$1 Bob built the string that included those apostrophes. Another way is to let excel do the heavy lifting. Option Explicit Private Sub cmdPopulateListBox_Click() Dim MyRange As Range With Sheets(3) Set MyRange = .Range("c2:F" & .Cells(.Rows.Count, "A").End(xlUp).Row) End With With Sheets(1).ListBox1 .ColumnCount = 4 .ListFillRange = MyRange.Address(external:=True) End With End Sub MyRange.Address(external:=True) will return a string like: [book1.xls]Sheet2!$C$2:$F$10 ===== And sheets(3) and sheets(1) refer to the relative position of the worksheets. I'd try to use something like: worksheets("Sheet1") and worksheets("sheet 99") (if the user can't rename those sheets) It scares me more that the user could rearrange the sheets--yeah, yeah. I should be worried about them renaming the sheets, too! Henry wrote: Wow- fast again! This looks interesting. I'll have to give it a shot tomorrow morning. Can you tell me why the string should be formatted like this? I never would have come up with this. It sure doesn't look intuitive, and I haven't found any reference that even hints at this. Thanks! Henry Bob Phillips wrote: Try changing this line sTest = "C2:" & strLastRow to sTest = "'" & Sheets(3).Name & "'!C2:" & strLastRow -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Listbox data display problem
But that doesn't work for a ListFillRange, unless you also incorporate
Dave's suggestion. -- HTH RP (remove nothere from the email address if mailing direct) "count" wrote in message ... Try to use user-independent names for sheets. In VBA in Properties window for the sheet change its Name from Sheet1 to WhatEver you want. From there on you can use WhatEver instead as an internal name. Users will be free to move / rename tabs. e.g. WhatEver.ListBox1.ColumnCount = 4 HTH Paul Uzytkownik "Dave Peterson" napisal w wiadomosci ... Go to sheet3. select A1 and copy it. back to sheet1 select an empty cell. edit|paste special|click the paste link button. You'll see something like: =Sheet3!$A$1 or ='Sheet 3'!$A$1 Bob built the string that included those apostrophes. Another way is to let excel do the heavy lifting. Option Explicit Private Sub cmdPopulateListBox_Click() Dim MyRange As Range With Sheets(3) Set MyRange = .Range("c2:F" & .Cells(.Rows.Count, "A").End(xlUp).Row) End With With Sheets(1).ListBox1 .ColumnCount = 4 .ListFillRange = MyRange.Address(external:=True) End With End Sub MyRange.Address(external:=True) will return a string like: [book1.xls]Sheet2!$C$2:$F$10 ===== And sheets(3) and sheets(1) refer to the relative position of the worksheets. I'd try to use something like: worksheets("Sheet1") and worksheets("sheet 99") (if the user can't rename those sheets) It scares me more that the user could rearrange the sheets--yeah, yeah. I should be worried about them renaming the sheets, too! Henry wrote: Wow- fast again! This looks interesting. I'll have to give it a shot tomorrow morning. Can you tell me why the string should be formatted like this? I never would have come up with this. It sure doesn't look intuitive, and I haven't found any reference that even hints at this. Thanks! Henry Bob Phillips wrote: Try changing this line sTest = "C2:" & strLastRow to sTest = "'" & Sheets(3).Name & "'!C2:" & strLastRow -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Listbox data display problem
The codename property is labeled "(Name)" (with the parentheses).
The Name propert (w/o the parentheses) is the name you see on the worksheet tab. I was gonna include that, but I thought that I'd be adding another level of complexity for the OP. But since you brought it up,... So this line: With Sheets(3) could change to: with Sheet3 (what ever matched that (Name) property.) count wrote: Try to use user-independent names for sheets. In VBA in Properties window for the sheet change its Name from Sheet1 to WhatEver you want. From there on you can use WhatEver instead as an internal name. Users will be free to move / rename tabs. e.g. WhatEver.ListBox1.ColumnCount = 4 HTH Paul Uzytkownik "Dave Peterson" napisal w wiadomosci ... Go to sheet3. select A1 and copy it. back to sheet1 select an empty cell. edit|paste special|click the paste link button. You'll see something like: =Sheet3!$A$1 or ='Sheet 3'!$A$1 Bob built the string that included those apostrophes. Another way is to let excel do the heavy lifting. Option Explicit Private Sub cmdPopulateListBox_Click() Dim MyRange As Range With Sheets(3) Set MyRange = .Range("c2:F" & .Cells(.Rows.Count, "A").End(xlUp).Row) End With With Sheets(1).ListBox1 .ColumnCount = 4 .ListFillRange = MyRange.Address(external:=True) End With End Sub MyRange.Address(external:=True) will return a string like: [book1.xls]Sheet2!$C$2:$F$10 ===== And sheets(3) and sheets(1) refer to the relative position of the worksheets. I'd try to use something like: worksheets("Sheet1") and worksheets("sheet 99") (if the user can't rename those sheets) It scares me more that the user could rearrange the sheets--yeah, yeah. I should be worried about them renaming the sheets, too! Henry wrote: Wow- fast again! This looks interesting. I'll have to give it a shot tomorrow morning. Can you tell me why the string should be formatted like this? I never would have come up with this. It sure doesn't look intuitive, and I haven't found any reference that even hints at this. Thanks! Henry Bob Phillips wrote: Try changing this line sTest = "C2:" & strLastRow to sTest = "'" & Sheets(3).Name & "'!C2:" & strLastRow -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Listbox data display problem
Thanks, Bob- this worked just fine. I can see other messages that were
posted before I got a chance to respond. I've been putting in long hours, so if there's been a little lag time, please bear with me. So far this Excel/VBA project has been technically interesting, and a nice diversion, but it's also been a decent hair-pulling exercise, too. I appreciate the help, from you and all the others. Henry Bob Phillips wrote: Try changing this line sTest = "C2:" & strLastRow to sTest = "'" & Sheets(3).Name & "'!C2:" & strLastRow |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ListBox to display data between two dates | Excel Discussion (Misc queries) | |||
display and change listbox data | Excel Programming | |||
Multicolumn listbox data display | Excel Programming | |||
Data Validation Listbox problem | Excel Programming | |||
Listbox Display | Excel Programming |