Is this code in the same workbook as the code (including that real1.xls makes it
look like it may not be)...

If it is in the same workbook...
Dim myRng as range
set myrng = dcidata.range("b6:b1696")

....
worksheets("QueryDate").lstfrom.listfillrange = myrng.address(external:=true)

(but you didn't use the code name for QueryDate <vbg.)

========
If those worksheets (QueryDate and DCI Data) are in different workbooks, then it
becomes a bit more complex.

There are ways to use the codename in a separate workbook directly, but this
depends on the security settings for the user. So I stay away from that.

I'd use:

Dim FoundIt as boolean
dim wks as worksheet
dim myRng as range

founddcidata = false
with workbooks("real1.xls")
for each wks in .worksheets
if lcase(wks.codename) = lcase("DciData") then
foundit = true
exit for 'stop looking
end if
next wks
end with

if foundit = false then
'not found!
msgbox "the real1.xls workbook doesn't look right. Where's that sheet!!!"
exit sub '????
end if

set myrng = wks.range("b6:b1696")

worksheets("QueryDate").lstfrom.listfillrange = myrng.address(external:=true)

==========

If your (and all the user's!) security settings are ok, you don't have to loop:

Dim wks As Worksheet
dim myRng as range

With Workbooks("reall.xls")
Set wks = .Worksheets(CStr(.VBProject _
.VBComponents("DCIData").Properties("Name")))
End With

set myrng = wks.range("b6:b1696")

worksheets("QueryDate").lstfrom.listfillrange _
= myrng.address(external:=true)

=========
When I was devoping for others, I never figured that their security settings
would be what I needed. I always looped.

====

Ps. All this stuff is untested and uncompiled. Watch for typos--especially
reall.xls. I couldn't tell if that was reall (two L's) or rea11 (two one's) or
what???


Merlynsdad wrote:

I love this - it works perfectly! I changed the codename from Sheet8 to
DCIData and left the Name as "DCI Data". One question: how do I change the
following line of code, which has the original sheet Name as DCIdata, now
that the Name has changed to "DCI Data" and the codename has changed from
Sheet8 to DCIData?

Worksheets("QueryDate").lstFrom.ListFillRange =
"[real1.xls]DCIdata!$B$6:$B$1696"

"Dave Peterson" wrote:

You could protect the workbook's structure so that the user can't change the
name of sheets--but that means that can't move, copy, rename, delete, insert
sheets, either.

Or don't use the name that the user can change.

Instead, use the codename for that sheet.

If you open your workbook, then go into the VBE (alt-f11) and show the project
explorer (hit ctrl-r), you can select your worksheet (expand the branches if you
have to).

Then hit F4 to see the properties window.

You'll see a list of properties including Name and (Name).

The Name property is the name that the user can change on the sheet tab. The
(Name) property is the codename for the sheet.

So instead of having code like:

Worksheets("DCI Data").range("A1").value = "hi there"
you'd use the code name:
Sheet1.range("a1").value = "hi there"

In fact, you can type over the (Name) property with something that is
mnemonically significant. I'd rename that "DCI Data" codename to "DCIData" (no
spaces!).

Then my code would look like:
DCIData.range("A1").value = "hi there"

=====
Be aware that the codename can be changed--you'd want to protect the workbook's
project:

Inside the VBE
Tools|VBAProject Properties|protection tab

And even this protection can be broken for those who really want to.

But it does make it more difficult.



Merlynsdad wrote:

I have a sheet name in Excel that is "DCIdata". The file owner has now
changed it to "DCI Data". I have several references to the sheet in VBA as
DCIdata!. How do I refer to this sheet now that it has a space in it?

--

Dave Peterson
.


--

Dave Peterson