Emoncada
11-20-2008, 09:47 AM
I have the following vb script that works great but would like to add something to it that needs to be placed within the With formula I believe.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Intersect(Range("B5,B7,B9,B11,B13,D5,D7,D9,D11,D13,F7"), Target) Is Nothing Then
Exit Sub
End If
With Target
If .Value = "X" Then
.Value = ""
Else
.Value = "X"
Select Case .Column
Case 2
.Offset(, 2).ClearContents
.Offset(, 4).ClearContents
Case 4
.Offset(, 2).ClearContents
.Offset(, -2).ClearContents
Case 6
.Offset(, -2).ClearContents
.Offset(, -4).ClearContents
End Select
End If
End With
Cancel = True
End Sub
I want to add this
If Range("D11").Value = "X" Then Range("B20").Value = "N/A"
If Range("D11").Value = "" Then Range("B20:H20").Cells.ClearContents
This part I had after the End With but i was doing it everytime someone double clicked a Target cell. So if someone input data in the "B20" cell then went back to a target cell and double clicked it if "D11" = "" it clears that range. Is there a fix for that?
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Intersect(Range("B5,B7,B9,B11,B13,D5,D7,D9,D11,D13,F7"), Target) Is Nothing Then
Exit Sub
End If
With Target
If .Value = "X" Then
.Value = ""
Else
.Value = "X"
Select Case .Column
Case 2
.Offset(, 2).ClearContents
.Offset(, 4).ClearContents
Case 4
.Offset(, 2).ClearContents
.Offset(, -2).ClearContents
Case 6
.Offset(, -2).ClearContents
.Offset(, -4).ClearContents
End Select
End If
End With
Cancel = True
End Sub
I want to add this
If Range("D11").Value = "X" Then Range("B20").Value = "N/A"
If Range("D11").Value = "" Then Range("B20:H20").Cells.ClearContents
This part I had after the End With but i was doing it everytime someone double clicked a Target cell. So if someone input data in the "B20" cell then went back to a target cell and double clicked it if "D11" = "" it clears that range. Is there a fix for that?