PDA

View Full Version : Group 2 cells like in a userform.



Emoncada
06-18-2008, 09:14 AM
I have cells B3 & D3 . One being a "YES" the other a "NO".

Is there a way I can have it say if B3 = "a" then D3 = "" & if "D3" = "a" then B3 = ""

I have this code.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Intersect(Range("B3,B5,B7,B9,B11,D3,D5,D7,D9,D11"), 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

I tried this


Private Sub Worksheet_Change(ByVal Target As Range)
If Range("B3") = "a" Then Range("D3") = ""
If Range("D3") = "a" Then Range("B3") = ""
End Sub


But not working the way I want it to work when B3 = "a" and i double click D3 it stays the same.

Bob Phillips
06-18-2008, 09:32 AM
Private Sub Worksheet_Change(ByVal Target As Range)
Dim OtherCell As String

With Target

If .Address = "$B$3" Then

OtherCell = "D3"
ElseIf .Address = "$D$3" Then

OtherCell = "B3"

Else

Exit Sub
End If

Application.EnableEvents = False

If .Value = "a" Then

Me.Range(OtherCell).Value = ""
ElseIf .Value = "" Then

Me.Range(OtherCell).Value = "a"
End If
End With

Application.EnableEvents = True

End Sub

RonMcK
06-18-2008, 09:42 AM
<deleted by author>

Emoncada
06-18-2008, 12:54 PM
That's good but can I have it if I double click the one that equals "a" it goes "".

Bob Phillips
06-18-2008, 03:49 PM
Goes where? It goes by setting the other.

Ago
06-18-2008, 05:24 PM
i think he means like this




Private Sub Worksheet_Change(ByVal Target As Range)
Dim OtherCell As String

With Target

If .Address = "$B$3" Then

OtherCell = "D3"
ElseIf .Address = "$D$3" Then

OtherCell = "B3"

Else

Exit Sub
End If

Application.EnableEvents = False

If .Value = "a" Then

Me.Range(OtherCell).Value = "a"
Me.Range(Target.Address).Value = ""
ElseIf .Value = "" Then

Me.Range(OtherCell).Value = ""
Me.Range(Target.Address).Value = "a"
End If
End With

Application.EnableEvents = True

End Sub

mikerickson
06-18-2008, 07:53 PM
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim shortTermMemory As Variant
If Target.Address = "$B$3" Or Target.Address = "$D$3" Then
If Target.Value = "a" Then
Application.EnableEvents = False
With Range(Target, Range("C3"))
shortTermMemory = .Value
Range("b3:d3").Value = vbNullString
.Value = shortTermMemory
End With
Application.EnableEvents = True
End If
End If
End Sub

Emoncada
06-23-2008, 10:22 AM
Perfect Mikerickson that's exactly what I wanted thanks.