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