Returning sheet names (2000 sp-3/ 98SE)

[AlanMiller]

I'm using code that returns the name of a worksheet (strSheetName) from different "sources", depending on the particular procedure. The two methods being used are:
o ThisWorkbook.Names(strRangeName).RefersToR1C1
o ActiveSheet.Name

Typically, the results look like:
=Contacts!R2C6:R6C7
Contacts

But if there is a space character in the worksheet name, the results returned appear like:
=<font color=red>'</font color=red>Quotes Submitted<font color=red>'</font color=red>!R2C1:R5C23
Quotes Submitted

It's not possible to apply a method that can consistently extract or reuse the returned sheetnames, without me using two functions to run the returned values through - one to add single quote marks around strSheetName if they're needed in the particular context but they're not already there; the other to remove them from strSheetName if they're present, and if their removal is appropriate to some other context in which strSheetName is being used.

This does work, but it's a bit clunky and rather tedious to have to keep applying these functions throughout the code, in various contexts. Has anyone encountered this and come up with a way to consistently obtain a return value of a sheet name, regardless of whether it contains blanks or not?

Alan

[pieterse]

Well, you could of course take another route alltogether, by using the sheet *object* rather than the sheet's name:

<pre>Sub test()
Dim oSh As Worksheet
Set oSh = ThisWorkbook.Names("test").RefersToRange.Parent
MsgBox oSh.Name
End Sub
</pre>


Now in the rest of the code, use oSh directly, instead of getting at the sheet by using the sheet's name <g>.

[JohnBF]

Alan, Jan Karel's solution is more elegant, but FWIW, when using the =INDIRECT() function you can always wrap a sheetname in single quotes, even when they aren't required, and Excel accepts them: see if that also works in code.

[AlanMiller]

That's probably going to be a good solution here, Jan. I've never had cause to use the .Parent property, so didn't even think about it. Thanks

Alan

[AlanMiller]

Thanks John. That's something else I hadn't considered; however I did have problems with sheet names in (single) quotes, when Excel then needed to wrap them (again) in double quotes, in other contexts. e.g. "'Sheet Name'". I don't recall the exact situation though I'm afraid.

Alan