Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Nov 2004
    Location
    Sacramento, California, USA
    Posts
    21
    Thanks
    0
    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' suggestion...now I seem to get errors every time. The code looks like this:

    MatchTextExactly = False
    If ActiveSheet.Name = "Data" Then
    ActiveSheet.Unprotect
    ElseIf ActiveSheet.Name = "data" Then
    ActiveSheet.Unprotect
    Else
    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. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 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.

  4. #3
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    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(ActiveSheet.name, "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
  •