Compile error: ambiguous name detected vba excel Jan-06-17

Question

when i add another VBA into the same Sheet its produce a Complile error: ambigous name detected vba 

please help on how can i accomplish this!

Answer 1

hi Bernie Thank again now it did work as you said i am going to use

as follow!

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim i As Long

    If Intersect(Target, Range("B:D,N:P")) Is Nothing Then Exit Sub

    Application.EnableEvents = False

    For i = Target.Cells(1).Row To Target.Cells(Target.Cells.Count).Row

        If Cells(i, "B").Value <> "" And Cells(i, "C").Value <> "" And Cells(i, "D").Value <> "" And Cells(i, "F").Value = "" Then

            Cells(i, "F").NumberFormat = "m/d/yyyy h:mm AM/PM"

            Cells(i, "F").Value = Now

        End If

        If Cells(i, "N").Value <> "" And Cells(i, "O").Value <> "" And Cells(i, "P").Value <> "" And Cells(i, "R").Value = "" Then

            Cells(i, "R").NumberFormat = "m/d/yyyy h:mm AM/PM"

            Cells(i, "R").Value = Now

        End If

    Next i

    Application.EnableEvents = True

    Range("F:F,R:R").EntireColumn.AutoFit

the is that remain is as follow!

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Column = 4 Then       i can not write any number in this cell  or even click

    Beep

    Cells(Target.Row + 1, 2).Activate

End If

    If Target.Column = 17 Then    i can not write any number in this cell or even click

    Beep

    Cells(Target.Row + 1, 15).Activate

End If

End Sub

if i change target 4 and 17 it work but then my Laser measurement tool wont work!

Answer 2

hi John, you are right! thank you.

Answer 3

thank you Gord i did looked but i was a bit of lost on the Excel files you have on the onedrive, now if i look it might make more sense, Bernie is doing a good job as well trying to help, thank you you all, this is a great room, ive been dealing with computers for 20 years my personal stuff and this is a first time seeking for help, it could not be any better.

Answer 4

Better to describe what you need for the code to work than to say it doesn't work - it doesn't work because any time you select those cells, your code selects the cells 2 or 15 columns to the right. You might want to check the state of those cells and allow them to be selected if they are empty: 

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Column = 4 Then

    Beep

    If Target.Value <> "" Then Cells(Target.Row + 1, 2).Activate

End If

    If Target.Column = 17 Then

    Beep

    If Target.Value <> "" Then Cells(Target.Row + 1, 15).Activate

End If

End Sub

Answer 5

Thank you, well appreciated, may i ask how to i allow them to be selected if they are empty?.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Column = 4 Then

    Beep

    If Target.Value <> "" Then Cells(Target.Row + 1, 2).Activate

End If

    If Target.Column = 17 Then

    Beep

    If Target.Value <> "" Then Cells(Target.Row + 1, 15).Activate

End If

End Sub