Results 1 to 43 of 43

Compile error: Ambigous name detected: Worksheet_Change

Threaded View

  1. #1
    Forum Contributor
    Join Date
    02-06-2013
    Location
    Maryland
    MS-Off Ver
    Excel 2010
    Posts
    1,012

    Compile error: Ambigous name detected: Worksheet_Change

    I have codes that need to be tweaked to achieve the desired outcome. The two (2) codes have Private Sub Worksheet_Change(ByVal Target As Range), which is resulting in error. Would like to combine the 2 codes into one and still have the desired result.
    “Credit to Linda at Contextures for the first code; credit to Debraj Roy for the second code.” Sample Excel workbook attached for convenience.


    Option Explicit
    Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo exitHandler
    
    Dim rngDV As Range
    If Target.Count > 1 Then GoTo exitHandler
    
    On Error Resume Next
    Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
    On Error GoTo exitHandler
    If rngDV Is Nothing Then GoTo exitHandler
    If Intersect(Target, rngDV) Is Nothing Then
       'do nothing
    Else
      Application.EnableEvents = False
       If Target.Column = 11 Then
         If Target.Value = "" Then GoTo exitHandler
         If Target.Offset(0, 1).Value = "" Then
           Target.Offset(0, 1).Value = Target.Value
         Else
           Target.Offset(0, 1).Value = _
             Target.Offset(0, 1).Value _
             & ", " & Target.Value
         End If
       End If
    End If
    
    ExitHandler:
      Application.EnableEvents = True
      
    End Sub
    
    
    Private Sub Worksheet_Change(ByVal Target As Range)
        If Not Intersect(Range("j:k"), Target) Is Nothing And Target.Row >= 6 Then
            If ((Range("j" & Target.Row) <> "") * (Range("K" & Target.Row) <> "")) Then
                Range("l" & Target.Row).Formula = _
            "=I" & Target.Row & "&"" ""&J" & Target.Row & " &"" ""&K" & Target.Row & "&"" - ""&E" & Target.Row
            Else
                Range("l" & Target.Row) = ""
            End If
        End If
    End Sub
    Attached Files Attached Files
    Last edited by bjnockle; 07-20-2014 at 10:51 PM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] IF formula with certain conditions to achieve a desired value
    By Excel Dumbo in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-11-2014, 10:59 PM
  2. Replies: 1
    Last Post: 12-10-2013, 12:28 AM
  3. Replies: 0
    Last Post: 10-09-2013, 07:59 PM
  4. Comparing 2 different Excel Worksheets & obtaining desired outcome
    By rvkadu1 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 07-11-2013, 01:18 AM
  5. Combine two vba Codes to achieve a result
    By newqueen in forum Excel Programming / VBA / Macros
    Replies: 43
    Last Post: 06-12-2013, 06:15 AM

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