Listbox data display problem

[Henry]

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

[Bob Phillips]

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)

Post by Henry
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

[Henry]

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

Post by Bob Phillips
Try changing this line
sTest = "C2:" & strLastRow
to
sTest = "'" & Sheets(3).Name & "'!C2:" & strLastRow

[Dave Peterson]

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!

Post by Henry
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

Post by Bob Phillips
Try changing this line
sTest = "C2:" & strLastRow
to
sTest = "'" & Sheets(3).Name & "'!C2:" & strLastRow

[count]

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

Post by Dave Peterson
Go to sheet3.
select A1 and copy it.
back to sheet1
select an empty cell.
edit|paste special|click the paste link button.
=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)
[book1.xls]Sheet2!$C$2:$F$10
=====
And sheets(3) and sheets(1) refer to the relative position of the worksheets.
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!

Post by Henry
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

Post by Bob Phillips
Try changing this line
sTest = "C2:" & strLastRow
to
sTest = "'" & Sheets(3).Name & "'!C2:" & strLastRow

--
Dave Peterson

[Bob Phillips]

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)

Post by count
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

Post by Dave Peterson
Go to sheet3.
select A1 and copy it.
back to sheet1
select an empty cell.
edit|paste special|click the paste link button.
=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)
[book1.xls]Sheet2!$C$2:$F$10
=====
And sheets(3) and sheets(1) refer to the relative position of the worksheets.
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

Post by count

Post by Dave Peterson
should be worried about them renaming the sheets, too!

Post by Henry
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

Post by Bob Phillips
Try changing this line
sTest = "C2:" & strLastRow
to
sTest = "'" & Sheets(3).Name & "'!C2:" & strLastRow

--
Dave Peterson

[Dave Peterson]

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.)

Post by count
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

Post by Dave Peterson
Go to sheet3.
select A1 and copy it.
back to sheet1
select an empty cell.
edit|paste special|click the paste link button.
=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)
[book1.xls]Sheet2!$C$2:$F$10
=====
And sheets(3) and sheets(1) refer to the relative position of the worksheets.
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!

Post by Henry
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

Post by Bob Phillips
Try changing this line
sTest = "C2:" & strLastRow
to
sTest = "'" & Sheets(3).Name & "'!C2:" & strLastRow

--
Dave Peterson

[Henry]

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

Post by Bob Phillips
Try changing this line
sTest = "C2:" & strLastRow
to
sTest = "'" & Sheets(3).Name & "'!C2:" & strLastRow