+ Reply to Thread
Results 1 to 4 of 4

Worksheets Exists Code...

  1. #1
    KimberlyC
    Guest

    Worksheets Exists Code...

    Hi,
    I'm using the code below to find out if a worksheet named "Memo" exists in
    the active workbook.
    If the code find a worksheet named "Memo"... it returns True.
    It's working great..but now I need to change the code to find out if there
    are any worksheets in the active workbook that start with the name "Memo"
    There can be many worksheets with the name "Memo" added to this file....and
    appear as... Memo, Memo(2), Memo (3) and so on...
    I'm not sure what needs to be changed in this code to do this..

    Public Function WSExist(Memo As String) As Boolean
    'returns true if worksheet exists in the active workbook
    Dim objWorksheet As Object
    On Error Resume Next
    WSExist = False
    Set objWorksheet = ActiveWorkbook.Sheets("Memo")
    If Err = 0 Then
    WSExist = True
    End If
    End Function

    Any help is greatly apprecaited..

    Thanks in advance!
    Kimberly



  2. #2
    Dave Peterson
    Guest

    Re: Worksheets Exists Code...

    One way:

    Option Explicit

    Public Function WSExist2(Memo As String, _
    Optional wkbk As Workbook = Nothing) As Boolean
    'returns true if worksheet exists in the active workbook
    Dim objWorksheet As Worksheet

    If wkbk Is Nothing Then
    Set wkbk = ActiveWorkbook
    End If

    WSExist2 = False
    For Each objWorksheet In wkbk.Worksheets
    If LCase(Left(objWorksheet.Name, Len(Memo))) = LCase(Memo) Then
    WSExist2 = True
    Exit For
    End If
    Next objWorksheet

    End Function


    Sub testme()
    Dim wkbk As Workbook
    Set wkbk = Workbooks("book2.xls")
    MsgBox WSExist2("Memo", wkbk)
    End Sub





    KimberlyC wrote:
    >
    > Hi,
    > I'm using the code below to find out if a worksheet named "Memo" exists in
    > the active workbook.
    > If the code find a worksheet named "Memo"... it returns True.
    > It's working great..but now I need to change the code to find out if there
    > are any worksheets in the active workbook that start with the name "Memo"
    > There can be many worksheets with the name "Memo" added to this file....and
    > appear as... Memo, Memo(2), Memo (3) and so on...
    > I'm not sure what needs to be changed in this code to do this..
    >
    > Public Function WSExist(Memo As String) As Boolean
    > 'returns true if worksheet exists in the active workbook
    > Dim objWorksheet As Object
    > On Error Resume Next
    > WSExist = False
    > Set objWorksheet = ActiveWorkbook.Sheets("Memo")
    > If Err = 0 Then
    > WSExist = True
    > End If
    > End Function
    >
    > Any help is greatly apprecaited..
    >
    > Thanks in advance!
    > Kimberly


    --

    Dave Peterson

  3. #3
    KL
    Guest

    Re: Worksheets Exists Code...

    Hi Kimberly,

    Try this:

    Public Function WSExist(MyStr As String) As Boolean
    'returns true if worksheet exists in the active workbook
    For Each ws In ActiveWorkbook.Worksheets
    If UCase(ws.Name) Like UCase(MyStr) & "*" Then
    WSExist = True
    Exit For
    End If
    Next ws
    End Function


    Regards,
    KL



    "KimberlyC" <[email protected]> wrote in message
    news:%[email protected]...
    > Hi,
    > I'm using the code below to find out if a worksheet named "Memo" exists in
    > the active workbook.
    > If the code find a worksheet named "Memo"... it returns True.
    > It's working great..but now I need to change the code to find out if there
    > are any worksheets in the active workbook that start with the name "Memo"
    > There can be many worksheets with the name "Memo" added to this
    > file....and
    > appear as... Memo, Memo(2), Memo (3) and so on...
    > I'm not sure what needs to be changed in this code to do this..
    >
    > Public Function WSExist(Memo As String) As Boolean
    > 'returns true if worksheet exists in the active workbook
    > Dim objWorksheet As Object
    > On Error Resume Next
    > WSExist = False
    > Set objWorksheet = ActiveWorkbook.Sheets("Memo")
    > If Err = 0 Then
    > WSExist = True
    > End If
    > End Function
    >
    > Any help is greatly apprecaited..
    >
    > Thanks in advance!
    > Kimberly
    >
    >




  4. #4
    KimberlyC
    Guest

    Re: Worksheets Exists Code...

    Thanks to you both!!!


    "KimberlyC" <[email protected]> wrote in message
    news:#[email protected]...
    > Hi,
    > I'm using the code below to find out if a worksheet named "Memo" exists in
    > the active workbook.
    > If the code find a worksheet named "Memo"... it returns True.
    > It's working great..but now I need to change the code to find out if there
    > are any worksheets in the active workbook that start with the name "Memo"
    > There can be many worksheets with the name "Memo" added to this

    file....and
    > appear as... Memo, Memo(2), Memo (3) and so on...
    > I'm not sure what needs to be changed in this code to do this..
    >
    > Public Function WSExist(Memo As String) As Boolean
    > 'returns true if worksheet exists in the active workbook
    > Dim objWorksheet As Object
    > On Error Resume Next
    > WSExist = False
    > Set objWorksheet = ActiveWorkbook.Sheets("Memo")
    > If Err = 0 Then
    > WSExist = True
    > End If
    > End Function
    >
    > Any help is greatly apprecaited..
    >
    > Thanks in advance!
    > Kimberly
    >
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1