Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Nov 2004
    Sacramento, California, USA
    Thanked 0 Times in 0 Posts

    Confirm Sheet Name (VBA for Excel 2000)

    I am getting files with a sheet called "Data" that I need to confirm exists. However, sometimes, someone will name it "data", with a lower case 'D'. I would like to confirm it exists. At first, I added an 'ElseIf' statement to allow for either and then got another variant of the word ("DATA"). I read another post with a 'matchexactly' I seem to get errors every time. The code looks like this:

    MatchTextExactly = False
    If ActiveSheet.Name = "Data" Then
    ElseIf ActiveSheet.Name = "data" Then
    GoTo Reloop '(Goes to a point to move to the next file)
    End If
    Someone suggested "UCASE" to make the sheet name uppercase, then test for upper case letters - can't find anything on that.

    How can I test a sheet name without worrying about whether the letters are capitalized or not?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 18 Times in 18 Posts

    Re: Confirm Sheet Name (VBA for Excel 2000)

    MatchTextExactly is a property of the Application.FileSearch object that can be used to search for files on disk matching specified criteria; it doesn't apply to comparing text in general.

    One possibility is to use UCase like this:

    If UCase(ActiveSheet.Name) = "DATA" Then

    Ucase will convert the sheet name to all upper case, so it won't matter whether the user named the sheet "Data" or "dAtA" or "data" etc.

    Another possibility is to insert the following line at the top of the module

    Option Compare Text

    This will make all text comparisons in the module case insensitive, i.e. "data" and "DATA" and "daTA" will be considered to be equal. So you can use

    If ActiveSheet.Name = "data" Then

    and still catch all variants.

  3. #3
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Silicon Valley, USA
    Thanked 93 Times in 89 Posts

    Re: Confirm Sheet Name (VBA for Excel 2000)

    A third option is to use StrComp, which offers the strange notion of determining which string has a greater value. Setting that aside:

    <code>If StrComp(, "Data", vbTextCompare) = 0 Then ' they are the same, case insensitive</code>

Posting Permissions

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