worksheets(1).Name gives an error
MZ Tools makes life easier for the Excel VBA coder
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: worksheets(1).Name gives an error

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    hi,
    does anyone know why the statment

    Worksheets(1).Name

    give an error even when that sheet exists.
    It works fine on most of my workbooks, but
    in some workbooks, I can run

    MsgBox Worksheets.Count

    and know there are worksheets in the workbook, but I get an error when I try to access any of their names.

    Thanx

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Guderup, Denmark
    Posts
    288
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi
    you probably have a space in the name.
    this function (alone) can't handle that.

    regards Tommy

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Works OK for me, what error are you getting? Remember that Worksheets(1) refers to the first worksheet in your workbook, regardless of when it was added. To refer to a sheet explicitly, try using Sheet1 (the actual name will be in the VBA explorer) or Worksheets("Worksheet_Name"), but remember that you will get an error if you try and rename a sheet to the name of a sheet that already exists.

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-19 10:38, Tommy Bak wrote:
    Hi
    you probably have a space in the name.
    this function (alone) can't handle that.

    regards Tommy
    Sorry Tommy, it doesn't matter whether it has a space in there or not.

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    Keaau, Hawaii
    Posts
    238
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I've noticed this behaviour as well. So I use Sheets for WorkSheets. It works both ways in the attached code. You could also be missing a object reference statement like With or Set. VBA statements may be written in a multitude of ways and still work, but miss one little letter and NADA!!

    Also:
    What did your Error statement tell you.
    What was the Error Number and What was the text?

    Option Explicit

    Sub WorksheetNm()
    Dim sheetnm As String
    sheetnm = Worksheets(1).Name
    MsgBox Sheets.Count
    MsgBox Worksheets.Count
    MsgBox (sheetnm)
    End Sub


    Yours in EXCELent Frustration
    KniteMare


  6. #6
    New Member
    Join Date
    Apr 2002
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    thanks for your responses, but the
    Worksheets(1).Name
    method call does work for workbooks that have sheets named with spaces in them, it just doesn't seem to work for a specific file even though it does have two sheets in it. The following is the actual code:

    For Each ws In Worksheets
    If Trim(ws.Name) = "mySheet" Then
    found = 1
    End If
    Next ws

    the error says:

    "Application defined or object defined error"

  7. #7
    Board Regular
    Join Date
    Feb 2002
    Location
    Guderup, Denmark
    Posts
    288
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Mudface > sorry, you are off caurse right. Space doesn't matter in this case.
    It's just that I recently had a problem regarding names, but know I come to think of that, it was i the indirect function.



    regards Tommy

  8. #8
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    On 2002-04-19 10:50, macroNubee wrote:
    thanks for your responses, but the
    Worksheets(1).Name
    method call does work for workbooks that have sheets named with spaces in them, it just doesn't seem to work for a specific file even though it does have two sheets in it. The following is the actual code:

    For Each ws In Worksheets
    If Trim(ws.Name) = "mySheet" Then
    found = 1
    End If
    Next ws

    the error says:

    "Application defined or object defined error"
    How did you dimension the variables? The following worked for me.

    ------------------
    Sub test()
    Dim ws As Worksheet, found As Byte

    For Each ws In Worksheets
    If Trim(ws.Name) = "mySheet" Then
    found = 1
    End If
    Next ws
    MsgBox found
    End Sub
    -------------------

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com