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?

I always use the Worksheets property to specify the worksheet... that way I
never have to worry about spaces. For example...

MsgBox WorkSheets("DCI Data").Range("A1").Value

This also has the flexibility to change things on the fly. For example...

WS = Array("DCI Data", "Sheet1", "Sheet3")
For X = LBound(WS) To UBound(WS)
With Worksheets(WS(X))
MsgBox .Range("A1").Value
End With
Next

--
Rick (MVP - Excel)


"Merlynsdad" wrote in message
...
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?

This is too obvious a question, Dad, but I gotta start somewhe Why not
refer to it in your program as "DCI Data"? Maybe if you post the lines from
your program where you refer to it, your question will be clearer.

--- "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?

Dim ws as Worksheet
Set ws = Sheets("DCI data")

Msgbox ws.Range("A1")


If this post helps click Yes
---------------
Jacob Skaria


"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?

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

OK, but how would you refer to this sheet in the ListFillBox property of an
Excel list box?

"Jacob Skaria" wrote:

Dim ws as Worksheet
Set ws = Sheets("DCI data")

Msgbox ws.Range("A1")


If this post helps click Yes
---------------
Jacob Skaria


"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?

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
.

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

Actually, I found an easier way to do it. Here's the code:

Worksheets("QueryDate").lstTo.ListFillRange = "'[real1.xls]DCI
data'!$B$6:$B$1696"

It is in the workbook_open code in another workbook and fills a listbox with
data from real1.xls. The single quote (or apostrophe) seems to do the trick.
Thanks for your info.

"Dave Peterson" wrote:

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
.

I thought you wanted to avoid the problem when the user renamed the
worksheet????

Merlynsdad wrote:

Actually, I found an easier way to do it. Here's the code:

Worksheets("QueryDate").lstTo.ListFillRange = "'[real1.xls]DCI
data'!$B$6:$B$1696"

It is in the workbook_open code in another workbook and fills a listbox with
data from real1.xls. The single quote (or apostrophe) seems to do the trick.
Thanks for your info.

"Dave Peterson" wrote:

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
.


--

Dave Peterson

They've been told not to rename any more sheets. :-)

"Dave Peterson" wrote:

I thought you wanted to avoid the problem when the user renamed the
worksheet????

Merlynsdad wrote:

Actually, I found an easier way to do it. Here's the code:

Worksheets("QueryDate").lstTo.ListFillRange = "'[real1.xls]DCI
data'!$B$6:$B$1696"

It is in the workbook_open code in another workbook and fills a listbox with
data from real1.xls. The single quote (or apostrophe) seems to do the trick.
Thanks for your info.

"Dave Peterson" wrote:

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
.


--

Dave Peterson
.