Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Michigan, USA
    Posts
    408
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Check Other Tab Names

    I have a macro that allows the user to create a new tab and asks for the name of the tab. What I don't know how to do is to check to see if the name they are using is already a tab name. If they use a previously used name the macro fails.

    Here is the code so far:
    Dim Message, Title, Default, MyValue
    Dim iCurValue As Integer
    iCurValue = 0
    Message = "Enter A Tab Name"
    Title = "Scenario Name?"
    MyValue = Application.InputBox(Prompt:=Message, Title:=Title, Type:=2)
    If MyValue = "" Then MyValue = iCurValue + 1
    <font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

  2. #2
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    120
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Check Other Tab Names

    Just use a for-next loop:

    Dim SH
    Dim SName As String
    For Each SName In ActiveWorkbook.Worksheets
    If SName.Name = myvalue Then
    MsgBox "There already is a sheet named " & myvalue
    Exit Sub
    End If
    Next

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Michigan, USA
    Posts
    408
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Check Other Tab Names

    I'm a little confused as to what SH is and how I tell it that Sname is the tab names. When I paste your code at the end of my code I get an error message "For Each control variable must be a Variant or Object"
    <font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Check Other Tab Names

    If MyValue (from your code) contains the name entered by the user, then the following code will look for a duplicate name:

    <pre>Dim oSheet As Worksheet
    For Each oSheet In Worksheets
    If oSheet.Name = MyValue Then
    MsgBox "Name already uses"
    End If
    Next oSheet
    </pre>

    Legare Coleman

  5. #5
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    120
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Check Other Tab Names

    Sorry, my error:

    Dim SH
    Dim SName As String
    For Each SH In ActiveWorkbook.Worksheets
    If SH.Name = MYVALUE Then
    MsgBox "There already is a sheet named " & MYVALUE
    Exit Sub
    End If
    Next

  6. #6
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Michigan, USA
    Posts
    408
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Check Other Tab Names

    Thanks. It works like a charm!! [img]/w3timages/icons/cool.gif[/img]
    <font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

Posting Permissions

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