Results 1 to 5 of 5
  1. #1
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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>.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

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

    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.
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  5. #5
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •