easy vb help

[Emoncada]

I need for this formula to work i am guessing it should be pretty simple.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Target.Address = "$B$11" Or Target.Address = "$D$11" Then
If Target.Value = "a" Then
Application.EnableEvents = False
With Range(Target, Range("C11"))
shortTermMemory3 = .Value
Range("b11:d11").Value = vbNullString
.Value = shortTermMemory3
End With
Application.EnableEvents = True
End If
End If

If Range("D11").Value = "a" Then Range("B20").Value = "N/A" Else Range("B20").Value = ""

All i want is if "D11" is empty for Cell "B20" to be empty. If "D11" = "a" then "B20" should have "N/A".

How can I make this work.

[Simon Lloyd]

If its the last line you are talkin about then there's nothing wrong with your logic, just rearranged a little
If Range("D11").Value = "a" Then
Range("B20").Value = "N/A"
Else
Range("B20").Value = ""
End If

[RonMcK]

Emoncada,

You've defined the values of B20 relative to D11 where D11 = "" (null) or "a". If D11 is some other value, say "b", is the value of B20 always "" (null)?

Thanks,

[Emoncada]

ok the cells b5,d5,b7,d7,b9,d9,b11,d11,b13,d13 are check boxes that when double clicked puts an "a" or removes it from the cell. It's currently giving me an error when I double click to remove the "a" from D11. Is there another line I need to add so it know's if "D11" = "" then "B20" = ""

[mdmackillop]

I don't understand why you would double click a check box, but you could put the value change into the double click event, rather than the worksheet_change

[Emoncada]

this is my entire code

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Intersect(Range("B5,B7,B9,B11,B13,D5,D7,D9,D11,D13"), Target) Is Nothing Then
Exit Sub

End If

If Target.Value = "a" Then

Target.Value = ""

Else

Target.Value = "a"

End If

Cancel = True

End Sub

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim shortTermMemory As Variant
Dim shortTermMemory1 As Variant
Dim shortTermMemory2 As Variant
Dim shortTermMemory3 As Variant
Dim shortTermMemory4 As Variant

If Target.Address = "$B$5" Or Target.Address = "$D$5" Then
If Target.Value = "a" Then
Application.EnableEvents = False
With Range(Target, Range("C5"))
shortTermMemory = .Value
Range("b5:d5").Value = vbNullString
.Value = shortTermMemory
End With
Application.EnableEvents = True
End If
End If
If Target.Address = "$B$7" Or Target.Address = "$D$7" Then
If Target.Value = "a" Then
Application.EnableEvents = False
With Range(Target, Range("C7"))
shortTermMemory1 = .Value
Range("b7:d7").Value = vbNullString
.Value = shortTermMemory1
End With
Application.EnableEvents = True
End If
End If
If Target.Address = "$B$9" Or Target.Address = "$D$9" Then
If Target.Value = "a" Then
Application.EnableEvents = False
With Range(Target, Range("C9"))
shortTermMemory2 = .Value
Range("b9:d9").Value = vbNullString
.Value = shortTermMemory2
End With
Application.EnableEvents = True
End If
End If
If Target.Address = "$B$11" Or Target.Address = "$D$11" Then
If Target.Value = "a" Then
Application.EnableEvents = False
With Range(Target, Range("C11"))
shortTermMemory3 = .Value
Range("b11:d11").Value = vbNullString
.Value = shortTermMemory3
End With
Application.EnableEvents = True
End If
End If
If Target.Address = "$B$13" Or Target.Address = "$D$13" Then
If Target.Value = "a" Then
Application.EnableEvents = False
With Range(Target, Range("C13"))
shortTermMemory4 = .Value
Range("b13:d13").Value = vbNullString
.Value = shortTermMemory4
End With
Application.EnableEvents = True
End If
End If


If Range("D11").Value = "a" Then
Range("B20").Value = "N/A"
Else
Range("B20").Value = ""
End If

End Sub


Hope that helps.

The only reason it's an "a" value is because in the font Marlett "a" is a check mark.

[mdmackillop]

Still don't follow what you are trying to achieve, but here is a much simplified code which shoulds do the same thing.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Intersect(Range("B5,B7,B9,B11,B13,D5,D7,D9,D11,D13"), Target) Is Nothing Then Exit Sub

Application.EnableEvents = False

If Target.Value = "a" Then
Target.Value = ""
Else
Target.Value = "a"
End If
Cancel = True
Application.EnableEvents = True
End Sub

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim Rw As Long
If Intersect(Range("B5,B7,B9,B11,B13,D5,D7,D9,D11,D13"), Target) Is Nothing Then Exit Sub
Application.EnableEvents = False
Rw = Target.Row
Cells(Rw, 2).ClearContents
Cells(Rw, 4).ClearContents

If Range("D11").Value = "a" Then
Range("B20").Value = "N/A"
Else
Range("B20").Value = ""
End If
Application.EnableEvents = True
End Sub