Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old November 29th 04, 04:07 PM
Rich
 
Posts: n/a
Default Worksheet name and Backward compatibility

I'm not sure if MS programmers should be blamed for this problem or VBA
developers . . .

In any case, a VBA programmer developed a program in Excel. It, of course,
uses names of Worksheet tabs. Unfortunately, some of the worksheet tab names
have square brackets in them -- "[" and "]". (i.e., "Worksheet[1]")

Also, unfortunately, our company is upgrading most PC's to Office 2003. It
turns out that Excel 2003 doesn't like square brackets in worksheet names.
When you open a worksheet with square brackets in the tab name with 2003,
Excel gives you a warning, then proceeds to open the worksheet (without
apparently modifying the worksheet names).

However, when the worksheet is saved, it converts brackets to parens -- "("
and ")".

This e-mail boils down to two questions:
(1) Is there a way to prevent Excel 2003 from turning brackets to parens in
worksheet tabs, and
(2) if not, is the only fix doing a search and replace in the VBA code?

Thanks for any information . . .

Rich

P.S. From this problem, I learned to consider the use of indexing when
referring to Worksheets within a VBA program.



  #2   Report Post  
Old November 29th 04, 04:36 PM
David McRitchie
 
Posts: n/a
Default

It sure seems like a confusing situation since a workbook
name is included in square brackets. Some people don't
have file extensions visible, I'd certainly avoid doing it.
Similar to naming a worksheet that looks like a cellname,
or trying to set a defined name to something that looks like
a cellname something to keep clear of.

Thanks for the warning:
It sure seems like Excel 2003 is an experiment, and not
for serious use.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Rich" wrote in message ...
I'm not sure if MS programmers should be blamed for this problem or VBA
developers . . .

In any case, a VBA programmer developed a program in Excel. It, of course,
uses names of Worksheet tabs. Unfortunately, some of the worksheet tab names
have square brackets in them -- "[" and "]". (i.e., "Worksheet[1]")

Also, unfortunately, our company is upgrading most PC's to Office 2003. It
turns out that Excel 2003 doesn't like square brackets in worksheet names.
When you open a worksheet with square brackets in the tab name with 2003,
Excel gives you a warning, then proceeds to open the worksheet (without
apparently modifying the worksheet names).

However, when the worksheet is saved, it converts brackets to parens -- "("
and ")".

This e-mail boils down to two questions:
(1) Is there a way to prevent Excel 2003 from turning brackets to parens in
worksheet tabs, and
(2) if not, is the only fix doing a search and replace in the VBA code?

Thanks for any information . . .

Rich

P.S. From this problem, I learned to consider the use of indexing when
referring to Worksheets within a VBA program.




  #3   Report Post  
Old November 29th 04, 10:25 PM
Dave Peterson
 
Posts: n/a
Default

If you can get to the code, you can always:

Edit|Find
and search the "current project" for both [ and ].

And you may want to look at using the codename of the worksheet within your
code.

Next time you're in the VBE, hit ctrl-r to see the project explorer.
Expand your project.

Under the "Microsoft Excel Objects", you'll see a list of your worksheets:

Sheet1 (TheNameOnTheTab_1)
Sheet2 (TheNameOnTheTab_2)
....

Then in your code, instead of doing this:

with worksheets("TheNameOnTheTab_1")
.range("a1").clearcontents
end with

you can just use that codename

with Sheet1
.range("a1").clearcontents
end with

(The name within parentheses is the name you can see on the tab when you're in
Excel.)


Rich wrote:

I'm not sure if MS programmers should be blamed for this problem or VBA
developers . . .

In any case, a VBA programmer developed a program in Excel. It, of course,
uses names of Worksheet tabs. Unfortunately, some of the worksheet tab names
have square brackets in them -- "[" and "]". (i.e., "Worksheet[1]")

Also, unfortunately, our company is upgrading most PC's to Office 2003. It
turns out that Excel 2003 doesn't like square brackets in worksheet names.
When you open a worksheet with square brackets in the tab name with 2003,
Excel gives you a warning, then proceeds to open the worksheet (without
apparently modifying the worksheet names).

However, when the worksheet is saved, it converts brackets to parens -- "("
and ")".

This e-mail boils down to two questions:
(1) Is there a way to prevent Excel 2003 from turning brackets to parens in
worksheet tabs, and
(2) if not, is the only fix doing a search and replace in the VBA code?

Thanks for any information . . .

Rich

P.S. From this problem, I learned to consider the use of indexing when
referring to Worksheets within a VBA program.


--

Dave Peterson
  #4   Report Post  
Old November 30th 04, 06:10 PM
Rich
 
Posts: n/a
Default

Thanks guys . . . that makes sense . . .


"Dave Peterson" wrote in message
...
If you can get to the code, you can always:

Edit|Find
and search the "current project" for both [ and ].

And you may want to look at using the codename of the worksheet within

your
code.

Next time you're in the VBE, hit ctrl-r to see the project explorer.
Expand your project.

Under the "Microsoft Excel Objects", you'll see a list of your worksheets:

Sheet1 (TheNameOnTheTab_1)
Sheet2 (TheNameOnTheTab_2)
...

Then in your code, instead of doing this:

with worksheets("TheNameOnTheTab_1")
.range("a1").clearcontents
end with

you can just use that codename

with Sheet1
.range("a1").clearcontents
end with

(The name within parentheses is the name you can see on the tab when

you're in
Excel.)


Rich wrote:

I'm not sure if MS programmers should be blamed for this problem or VBA
developers . . .

In any case, a VBA programmer developed a program in Excel. It, of

course,
uses names of Worksheet tabs. Unfortunately, some of the worksheet tab

names
have square brackets in them -- "[" and "]". (i.e., "Worksheet[1]")

Also, unfortunately, our company is upgrading most PC's to Office 2003.

It
turns out that Excel 2003 doesn't like square brackets in worksheet

names.
When you open a worksheet with square brackets in the tab name with

2003,
Excel gives you a warning, then proceeds to open the worksheet (without
apparently modifying the worksheet names).

However, when the worksheet is saved, it converts brackets to parens --

"("
and ")".

This e-mail boils down to two questions:
(1) Is there a way to prevent Excel 2003 from turning brackets to parens

in
worksheet tabs, and
(2) if not, is the only fix doing a search and replace in the VBA code?

Thanks for any information . . .

Rich

P.S. From this problem, I learned to consider the use of indexing when
referring to Worksheets within a VBA program.


--

Dave Peterson





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT +1. The time now is 04:06 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017