Compile error message

[mmdmalta]

hello, I used a VCA formula in my spread sheet. I want to do the exact same thing, but in a different area of the spread sheet. This is the formula i used and it worked in one area, but not the other.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("b12:f12")) Is Nothing Then
Cancel = True
Target.Interior.Color = vbYellow
End If
End Sub

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("b12:f12")) Is Nothing Then
Cancel = True
Target.Interior.Color = Cancel = False
Target.Interior.Color = vbWhite
End If
End Sub

The second area with the range B12:f12, pops up a window that i have a compile errpr" Ambiguous name detected: Worksheet) BeforeDoubleClick. Can you tell me what i need to do with this second area inside the VBA code?

 

[Fluff]

Something like

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("b12:f12")) Is Nothing Then
   Cancel = True
   Target.Interior.Color = vbYellow
ElseIf Not Intersect(Target, Range("H12:K12")) Is Nothing Then
   Cancel = True
   Target.Interior.Color = vbYellow
End If
End Sub

 

[mmdmalta]

Something like

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("b12:f12")) Is Nothing Then
   Cancel = True
   Target.Interior.Color = vbYellow
ElseIf Not Intersect(Target, Range("H12:K12")) Is Nothing Then
   Cancel = True
   Target.Interior.Color = vbYellow
End If
End Sub

 

[mmdmalta]

thank you, but that didn't work. The cell are different the first one references different cells, but even when i changed the cells it tells me it's ambiguous.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("A41:E43")) Is Nothing Then
Cancel = True
Target.Interior.Color = vbYellow
End If
End Sub

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("A41:E43")) Is Nothing Then
Cancel = True
Target.Interior.Color = Cancel = False
Target.Interior.Color = vbWhite
End If
End Sub

 

[Fluff]

Do you have any other code in that sheet module?
You can only have one instance of an event in any sheet.

 

[mmdmalta]

Yes, i do. But I assume there has to be another way to be able to do it elsewhere in the document. Is there a way to do that? Here is my code for the one section. All i want to do is add it to Cells B12 through F12.

Private Sub CheckBox1_Click()

End Sub

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("A41:E43")) Is Nothing Then
Cancel = True
Target.Interior.Color = vbYellow
End If
End Sub

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("A41:E43")) Is Nothing Then
Cancel = True
Target.Interior.Color = Cancel = False
Target.Interior.Color = vbWhite
End If
End Sub

 

[Fluff]

Then replace

Range("H12:K12"))

in the example I supplied with

Range("A41:E43")

& you can do the RightClick event in the same way

 

[mmdmalta]

ok, that half worked! It's doing it in the other cells, but now, when i right click the color yelllow doesn't go back to the original cell color in both sections...there is still something i'm missing.

 

[Fluff]

Have you changed the right click event? If so can you post the modified code.

 

[mmdmalta]

yes, here it is:

Private Sub CheckBox1_Click()

End Sub

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("b12:f12")) Is Nothing Then
   Cancel = True
   Target.Interior.Color = vbYellow
ElseIf Not Intersect(Target, Range("a43:E45")) Is Nothing Then
   Cancel = True
   Target.Interior.Color = vbYellow
End If
End Sub
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("b12:f12")) Is Nothing Then
   Cancel = True
   Target.Interior.Color = vbYellow
ElseIf Not Intersect(Target, Range("a43:E45")) Is Nothing Then
   Cancel = True
   Target.Interior.Color = vbYellow
End If
End Sub