VBA Compile Error: Ambiguous name detected

[Neurotic]

Hello

Im new to vba and i am getting Compile Error: Ambiguous name detected

any help would be Appreciated

Cheers

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDest As Range
Set rngDest = Worksheets("Archive").Range("rngDest")
If Not Intersect(Target, Worksheets("Active").Range("rngTrigger")) Is Nothing Then
     If IsDate(Target) Then
        Application.EnableEvents = False
        Target.EntireRow.Select
        Selection.Cut
        rngDest.Insert Shift:=xlDown
        Selection.Delete
        Application.EnableEvents = True
    End If
End If
End Sub
 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDest2 As Range
Set rngDest2 = Worksheets("HoldingBay").Range("rngDest2")
If Not Intersect(Target, Worksheets("Active").Range("rngTrigger2")) Is Nothing Then
     If IsDate(Target) Then
        Application.EnableEvents = False
        Target.EntireRow.Select
        Selection.Cut
        rngDest2.Insert Shift:=xlDown
        Selection.Delete
        Application.EnableEvents = True
    End If
End If
End Sub

 

[rjwebgraphix]

Two "Private Sub Worksheet_Change(ByVal Target As Range)" events within the same worksheet

You can only have one and with that one handle everything you need when the worksheet changes.

Like so...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDest As Range
Set rngDest = Worksheets("Archive").Range("rngDest")
If Not Intersect(Target, Worksheets("Active").Range("rngTrigger")) Is Nothing Then
     If IsDate(Target) Then
        Application.EnableEvents = False
        Target.EntireRow.Select
        Selection.Cut
        rngDest.Insert Shift:=xlDown
        Selection.Delete
        Application.EnableEvents = True
    End If
End If

Dim rngDest2 As Range
Set rngDest2 = Worksheets("HoldingBay").Range("rngDest2")
If Not Intersect(Target, Worksheets("Active").Range("rngTrigger2")) Is Nothing Then
     If IsDate(Target) Then
        Application.EnableEvents = False
        Target.EntireRow.Select
        Selection.Cut
        rngDest2.Insert Shift:=xlDown
        Selection.Delete
        Application.EnableEvents = True
    End If
End If
End Sub

 

[Neurotic]

Two "Private Sub Worksheet_Change(ByVal Target As Range)" events within the same worksheet

You can only have one and with that one handle everything you need when the worksheet changes.

Like so...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDest As Range
Set rngDest = Worksheets("Archive").Range("rngDest")
If Not Intersect(Target, Worksheets("Active").Range("rngTrigger")) Is Nothing Then
     If IsDate(Target) Then
        Application.EnableEvents = False
        Target.EntireRow.Select
        Selection.Cut
        rngDest.Insert Shift:=xlDown
        Selection.Delete
        Application.EnableEvents = True
    End If
End If

Dim rngDest2 As Range
Set rngDest2 = Worksheets("HoldingBay").Range("rngDest2")
If Not Intersect(Target, Worksheets("Active").Range("rngTrigger2")) Is Nothing Then
     If IsDate(Target) Then
        Application.EnableEvents = False
        Target.EntireRow.Select
        Selection.Cut
        rngDest2.Insert Shift:=xlDown
        Selection.Delete
        Application.EnableEvents = True
    End If
End If
End Sub

Perfect, So simple!

thanks for the fast reply

Cheers

 

[aquelon]

Hi! Excel isn't my usual environment (I'm architect and usually work in simple stuff in Excel) but now I'm trying to create a spreadsheet to feed information to a script, some of the parameter are colours, the input are numbers for RGB in separate coloumns, so I want to have a control column where the filling colour of the cells is determined by the values in the corresponding columns.

I found a piece of code here in a post that helped me a lot, but it is limited to one column and one set of values, I would like to define many columns and many sets of values, but for now I haven't managed to tweak the code to do this. I'll post here the original code and next my version of the tweaked code that shows the error mentioned above.

Here the original:

Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range, cell As Range Dim i As Long Set rng = Intersect(Target, Range("A:C")) If Not rng Is Nothing Then On Error Resume Next For Each cell In Target.Columns(1).Cells If Application.CountA(Range("A" & cell.Row & ":C" & cell.Row)) < 3 Or _ Application.Count(Range("A" & cell.Row & ":C" & cell.Row)) < 3 Then GoTo next_row Cells(cell.Row, "D").Interior.Color = _ RGB(Cells(cell.Row, "A").Value, Cells(cell.Row, "B").Value, Cells(cell.Row, "C").Value) next_row: Next cell End If End Sub

 

[aquelon]

Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range, cell As Range Dim i As Long Set rng = Intersect(Target, Range("A:C")) If Not rng Is Nothing Then    On Error Resume Next    For Each cell In Target.Columns(1).Cells        If Application.CountA(Range("A" & cell.Row & ":C" & cell.Row)) < 3 Or _            Application.Count(Range("A" & cell.Row & ":C" & cell.Row)) < 3 Then GoTo next_row        Cells(cell.Row, "D").Interior.Color = _            RGB(Cells(cell.Row, "A").Value, Cells(cell.Row, "B").Value, Cells(cell.Row, "C").Value) next_row:    Next cell End If End Sub

 

[aquelon]

And here is "my version" of this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range, cell As Range
Dim i As Long

Set rng = Intersect(Target, Range("K:M"))
If Not rng Is Nothing Then
    On Error Resume Next
    For Each cell In Target.Columns(1).Cells
        If Application.CountA(Range("K" & cell.Row & ":M" & cell.Row)) < 3 Or _
            Application.Count(Range("K" & cell.Row & ":M" & cell.Row)) < 3 Then GoTo next_row
        Cells(cell.Row, "J").Interior.Color = _
            RGB(Cells(cell.Row, "K").Value, Cells(cell.Row, "L").Value, Cells(cell.Row, "M").Value)
next_row:
    Next cell
End If


Dim rng2 As Range, cell As Range
Dim i As Long

Set rng2 = Intersect(Target, Range("P:R"))
If Not rng Is Nothing Then
    On Error Resume Next
    For Each cell In Target.Columns(1).Cells
        If Application.CountA(Range("P" & cell.Row & ":R" & cell.Row)) < 3 Or _
            Application.Count(Range("P" & cell.Row & ":R" & cell.Row)) < 3 Then GoTo next_row
        Cells(cell.Row, "O").Interior.Color = _
            RGB(Cells(cell.Row, "P").Value, Cells(cell.Row, "Q").Value, Cells(cell.Row, "R").Value)
next_row:
    Next cell
End If

End Sub

 

[aquelon]

Well, sorry for the spam, but finally it was possible to fix the code. A colleague of mine did it. Here is the final result.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range, cell As Range


' Defining Shading Color

Set rng = Intersect(Target, Range("K:M"))
If Not rng Is Nothing Then
    On Error Resume Next
    For Each cell In Target.Columns(1).Cells
        If Application.CountA(Range("K" & cell.Row & ":M" & cell.Row)) < 3 Or _
            Application.count(Range("K" & cell.Row & ":M" & cell.Row)) < 3 Then GoTo next_row_1
        Cells(cell.Row, "J").Interior.Color = _
            RGB(Cells(cell.Row, "K").Value, Cells(cell.Row, "L").Value, Cells(cell.Row, "M").Value)
next_row_1:
    Next cell
End If


' Defining Surface Foreground Pattern Color

Set rng = Intersect(Target, Range("P:R"))
If Not rng Is Nothing Then
    On Error Resume Next
    For Each cell In Target.Columns(1).Cells
        If Application.CountA(Range("P" & cell.Row & ":R" & cell.Row)) < 3 Or _
            Application.count(Range("P" & cell.Row & ":R" & cell.Row)) < 3 Then GoTo next_row_2
        Cells(cell.Row, "O").Interior.Color = _
            RGB(Cells(cell.Row, "P").Value, Cells(cell.Row, "Q").Value, Cells(cell.Row, "R").Value)
next_row_2:
    Next cell
End If


' Defining Cut Foreground Pattern Color

Set rng = Intersect(Target, Range("U:W"))
If Not rng Is Nothing Then
    On Error Resume Next
    For Each cell In Target.Columns(1).Cells
        If Application.CountA(Range("U" & cell.Row & ":W" & cell.Row)) < 3 Or _
            Application.count(Range("U" & cell.Row & ":W" & cell.Row)) < 3 Then GoTo next_row_2
        Cells(cell.Row, "T").Interior.Color = _
            RGB(Cells(cell.Row, "U").Value, Cells(cell.Row, "V").Value, Cells(cell.Row, "W").Value)
next_row_3:
    Next cell
End If

End Sub