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)

 

[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

 

[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!

 

[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

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!

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]

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)

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" <[email protected]> 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!

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]

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

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

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!

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

 

[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