Results 1 to 12 of 12
  1. #1
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    196
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Test for sheet name (2003)

    I am needing to create a macro that checks the current workbook sheet names and if some sheet "Test" does not exist, then create it. The sheet name is a variable and dose change. How might I go about this?

    Thanks,
    Jackal

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

    Re: Test for sheet name (2003)

    Like this:

    <pre>Public Sub CkSh(strShName As String)
    Dim oWS As Worksheet
    On Error Resume Next
    Set oWS = Worksheets(strShName)
    On Error GoTo 0
    If Not oWS Is Nothing Then Exit Sub
    Set oWS = Worksheets.Add
    oWS.Name = strShName
    End Sub



    Public Sub Test()
    CkSh ("Sheet4")
    End Sub
    </pre>

    Legare Coleman

  3. #3
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    196
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Test for sheet name (2003)

    How might I write the following <font color=red>RED</font color=red> line in VBA:
    [tag]If Sheets ("CO #1").<font color=red>does not exist</font color=red> Then
    bla
    bla
    bla
    ElseIf Sheets ("CO #2").<font color=red>does not exist</font color=red> Then
    bla
    bla
    bla
    End If [tag]
    And so on at least 15 times. Or is there a more efficient way to do this?
    Thanks,
    Jackal

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Test for sheet name (2003)

    Add this function to a module:

    <pre>Function SheetExists(sSheetName As String) As Boolean
    On Error Resume Next
    SheetExists = Len(Sheets(sSheetName).Name) <> 0
    End Function</pre>


    Then use something like this
    dim x as integer
    for x = 1 to 15
    If not sheetexists ("CO #" & x) Then
    bla
    bla
    bla
    end if
    next

    Steve

  5. #5
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    196
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Test for sheet name (2003)

    This works, but I would like a way to only add one sheet per each time button is pushed. So when the Change order process executes, it looks to see if any of the CO #? sheets exist, if not then create CO #1, if CO #1 does exist the careat the next CO#? depending on the highest CO sheet existing. How difficult is this?

    THANKS,
    Jackal

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

    Re: Test for sheet name (2003)

    Does this do what you want?

    <pre>Public Sub NewSheet()
    Dim oWS As Worksheet
    Dim I As Long
    For I = 1 To Worksheets.Count
    On Error Resume Next
    Set oWS = Nothing
    Set oWS = Worksheets("CO #" & I)
    On Error GoTo 0
    If oWS Is Nothing Then
    Set oWS = Worksheets.Add(After:=Worksheets(Worksheets.Count) )
    oWS.Name = "CO #" & I
    Exit Sub
    End If
    Next I
    Set oWS = Worksheets.Add(After:=Worksheets(Worksheets.Count) )
    oWS.Name = "CO #" & I
    End Sub
    </pre>

    Legare Coleman

  7. #7
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    196
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Test for sheet name (2003)

    Thanks to Legare & Steve for your help. I have one more little thing I would like to add to what I have. As you can see in the code below I have changed your <pre>Worksheets.Add</pre>

    so that I would copy the active sheeet.

    <pre>Public Sub NewSheet()
    Dim oWS As Worksheet
    Dim I As Long
    For I = 1 To Worksheets.Count
    On Error Resume Next
    Set oWS = Nothing
    Set oWS = Worksheets("CO #" & I)
    On Error GoTo 0
    If oWS Is Nothing Then
    ActiveSheet.Copy before:=Sheets(1)
    Set oWS = ActiveSheet

    oWS.Name = "CO #" & I
    <font color=blue>Range("A16").Select
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
    "=NOT(A1=INDIRECT(<font color=red>""PavBid!""</font color=red> & CELL(""Address"",A1)))</font color=blue>
    Exit Sub
    End If
    Next I
    ActiveSheet.Copy before:=Sheets(1)
    Set oWS = ActiveSheet

    oWS.Name = "CO #" & I
    End Sub</pre>

    The <font color=red>red</font color=red> part is what is giving me troubles. As you can see the conditional formatting works for the first CO #1, but when the next one is created, I am wanting the formula to reglect the previous page. ie...CO #2 wil be condiditonally formatted to reflect a different value for the same cell in CO #1 and so on as sheets are added. How might I go about getting this accomplished? I tried to add a Dim pSht as worksheet and an If I = 1 then set psht = Sheets("PavBid"), Else set pSht = Sheets("CO #" & I-1), however this did not work, it only put the pSht in the formula, thus no formatting occured.

    Thanks,
    Jackal

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Test for sheet name (2003)

    You are missing the end parentheses:

    "=NOT(A1=INDIRECT(""PavBid!"" & CELL(""Address"",A1)))<font color=red>"</font color=red>

    Steve

  9. #9
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    196
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Test for sheet name (2003)

    Oops, that was merely a type-o, in my actual workbook the parentheses are there. The the issue that still remains is in the formula:

    "=NOT(A1=INDIRECT(<font color=red>""PavBid!""</font color=red> & CELL(""Address"",A1)))"

    Can I make <font color=red>""PavBid!""</font color=red> a variable of some sort?

    Jackal

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

    Re: Test for sheet name (2003)

    Does this do it?

    <pre> With oWS.Range("A16").
    .FormatConditions.Delete
    If I > 1 Then
    .FormatConditions.Add Type:=xlExpression, Formula1:= _
    "=NOT(A1=INDIRECT(""CO #"" & I-1 & ""!A1""))"
    End If
    </pre>

    Legare Coleman

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Test for sheet name (2003)

    Dim sVariable As String
    sVariable = "PavBid" 'or whatever

    Range("A16").Select
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add _
    Type:=xlExpression, Formula1:= _
    "=NOT(A1=INDIRECT(" & """" & sVariable & "!" & """" & " & CELL(""Address"",A1)))"

    After looking more closely, is the problem the lack of formatting if the condition is true (something like):
    Selection.FormatConditions(1).Interior.ColorIndex = 36

    Steve

  12. #12
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    196
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Test for sheet name (2003)

    Once again Thanks to both of you, Steve & Legare for the help. Here is what my final code looks like:

    <pre>Private Sub NewSheet2()
    Dim oWS As Worksheet
    Dim I As Long
    For I = 1 To Worksheets.Count
    On Error Resume Next
    Set oWS = Nothing
    Set oWS = Worksheets("ChgOrd" & I)
    On Error GoTo 0
    If oWS Is Nothing Then
    ActiveSheet.Copy before:=Sheets(1)
    Set oWS = ActiveSheet
    oWS.Name = "ChgOrd" & I
    Range("A16").Select
    Dim sVariable As String
    If I > 1 Then
    sVariable = "ChgOrd" & I - 1
    Else
    sVariable = "PavBid"
    End If
    With Selection
    .FormatConditions.Delete
    If I > 1 Then
    .FormatConditions.Add Type:=xlExpression, Formula1:= _
    "=NOT(A1=INDIRECT(" & """" & sVariable & "!" & """" & " & _
    CELL(""Address"",A1)))"
    Else
    .FormatConditions.Add Type:=xlExpression, Formula1:= _
    "=NOT(A1=INDIRECT(""PavBid!"" & Cell(""Address"",A1)))"
    End If
    End With
    formatCond
    Exit Sub
    End If
    Next I
    ActiveSheet.Copy before:=Sheets(1)
    Set oWS = ActiveSheet
    oWS.Name = "ChgOrd" & I
    End Sub</pre>

    And it works GREAT! ! !
    <img src=/S/hailpraise.gif border=0 alt=hailpraise width=27 height=22>
    Thanks,
    Jackal

Posting Permissions

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