View Full Version : easy vb help
Emoncada
07-21-2008, 09:20 AM
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
07-21-2008, 09:48 AM
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
07-21-2008, 10:12 AM
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
07-21-2008, 11:07 AM
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
07-21-2008, 01:50 PM
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
07-22-2008, 05:19 AM
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
07-22-2008, 09:37 AM
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.