run two Worksheet_change in the same instance - merge code?

[flashgordie]

Hello All, Any help would be appreciated. With the assistance of this Forum ( and Fluff) he was able to provide me with a solution to my problem by means of the following code. The issue now is that I have come to understand that I can not run two Worksheet_change in the same instance. Is there any work around so that I can run Fluff's code as well as the second one below that changes the worksheet name based on a cell value.

Thank you for your consideration and assistance.!

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim Fnd As Range

   If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Range("C62:C76")) Is Nothing Then
      Set Fnd = Sheets("DataSource").Range("TableName").Find(Target.Value, , , xlWhole, , , False, , False)
      If Fnd Is Nothing Then Exit Sub
      Application.EnableEvents = False
      Target.Value = Fnd.Offset(, 1).Value
      Application.EnableEvents = True
   End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    'Specify the target cell whose entry shall be the sheet tab name.
    If Target.Address <> "$H$4" Then Exit Sub
        'If the target cell is empty (contents cleared) then do not change the sheet name
    If IsEmpty(Target) Then Exit Sub

    'If the length of the target cell's entry is greater than 31 characters, disallow the entry.
    If Len(Target.Value) > 31 Then
        MsgBox "Worksheet tab names cannot be greater than 31 characters in length." & vbCrLf & _
        "You entered " & Target.Value & ", which has " & Len(Target.Value) & " characters.", , "Keep it under 31 characters"
        Application.EnableEvents = False
        Target.ClearContents
        Application.EnableEvents = True
        Exit Sub
    End If

    'Sheet tab names cannot contain the characters /, \, [, ], *, ?, or :.
    'Verify that none of these characters are present in the cell's entry.
    Dim IllegalCharacter(1 To 7) As String, i As Integer
    IllegalCharacter(1) = "/"
    IllegalCharacter(2) = "\"
    IllegalCharacter(3) = "["
    IllegalCharacter(4) = "]"
    IllegalCharacter(5) = "*"
    IllegalCharacter(6) = "?"
    IllegalCharacter(7) = ":"
    For i = 1 To 7
        If InStr(Target.Value, (IllegalCharacter(i))) > 0 Then
            MsgBox "You used a character that violates sheet naming rules." & vbCrLf & vbCrLf & _
            "Please re-enter a sheet name without the ''" & IllegalCharacter(i) & "'' character.", 48, "Not a possible sheet name !!"
            Application.EnableEvents = False
            Target.ClearContents
            Application.EnableEvents = True
            Exit Sub
        End If
    Next i

    'Verify that the proposed sheet name does not already exist in the workbook.
    Dim strSheetName As String, wks As Worksheet, bln As Boolean
    strSheetName = Trim(Target.Value)
    On Error Resume Next
    Set wks = ActiveWorkbook.Worksheets(strSheetName)
    On Error Resume Next
    If Not wks Is Nothing Then
        bln = True
    Else
        bln = False
        Err.Clear
    End If

    'If the worksheet name does not already exist, name the active sheet as the target cell value.
    'Otherwise, advise the user that duplicate sheet names are not allowed.
    If bln = False Then
        ActiveSheet.Name = strSheetName
    Else
        MsgBox "There is already a sheet named " & strSheetName & "." & vbCrLf & _
        "Please enter a unique name for this sheet."
        Application.EnableEvents = False
        Target.ClearContents
        Application.EnableEvents = True
    End If

End Sub

 

[Fluff]

Do you need all the different message boxes, or would you be happy to have just one that says the sheet name is not valid?

 

[flashgordie]

Hello Again. annnnd, Thanks Again!!! In this case the sheet name is going to be pulled from H4 and because I am using a my own dropdown list in h4, I can eliminate the issues with 31characters, and special characters. So those functions could be deleted. I would like it to check for a duplicate sheet with the same name and if found add a (2 or 3 or ?) as a suffix to the end of it (same as excel does now). But to answer your question. I believe a generic error message is fine. Thanks so much!!!!!

 

[Fluff]

Ok, if you've already dealt with invalid sheet names, how about

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim Fnd As Range

   If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Range("C62:C76")) Is Nothing Then
      Set Fnd = Sheets("DataSource").Range("TableName").Find(Target.Value, , , xlWhole, , , False, , False)
      If Fnd Is Nothing Then Exit Sub
      Application.EnableEvents = False
      Target.Value = Fnd.Offset(, 1).Value
      Application.EnableEvents = True
   ElseIf Target.Address(0, 0) = "H4" Then
      If Target.Value = "" Then
         Exit Sub
      ElseIf Evaluate("isref('" & Target.Value & "'!A1)") Then
         MsgBox "Sheet " & Target.Value & " already exists"
      Else
         Me.Name = Trim(Target.Value)
      End If
   End If
End Sub

 

[flashgordie]

Well Fluff, you're pretty solid to me. Works like a charm! Thanks. I will try not to bother anyone for while! Really appreciate the help.

 

[Fluff]

Glad to help & thanks for the feedback.