Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use of Intersect: Place Value in one cell AND remove from another
Hello again all of you VERY helpful people! :)
I have a macro to add an "X" to certain cells using the Worksheet_Intersect VBA function. The problem is that I would like to see only 1 "X" at a time, but the following macro will allow for both cells (C11 and I11) to have an "X" at the same time. ----- Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Not Intersect(Target, Range("C11,I11")) Is Nothing Then If Target = vbNullString Then Target = "X" Else Target = vbNullString End If End If End Sub ----- Can I add something to this where if C11 already has an "X" and they click on either C11 or I11, it will remove the "X" from C11 and put one in I11? Thank you! Ronny |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use of Intersect: Place Value in one cell AND remove from another
Ronny,
Your rules are not completely clear to me. The following is my interpretation. Give it a try. -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Not Intersect(Target, Range("C11,I11")) Is Nothing Then If Target.Address = "$C$11" Then If Len(Target.Value) Then Target.ClearContents Me.Range("I11").Value = "X" Else Me.Range("I11").ClearContents Target.Value = "X" End If Else Target.Value = "X" Me.Range("C11").ClearContents End If End If End Sub '-------------- "Ronny Hamida" wrote in message Hello again all of you VERY helpful people! :) I have a macro to add an "X" to certain cells using the Worksheet_Intersect VBA function. The problem is that I would like to see only 1 "X" at a time, but the following macro will allow for both cells (C11 and I11) to have an "X" at the same time. ----- Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Not Intersect(Target, Range("C11,I11")) Is Nothing Then If Target = vbNullString Then Target = "X" Else Target = vbNullString End If End If End Sub ----- Can I add something to this where if C11 already has an "X" and they click on either C11 or I11, it will remove the "X" from C11 and put one in I11? Thank you! Ronny |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use of Intersect: Place Value in one cell AND remove from anot
Jim -
This answered my question perfectly! Thank you! "Jim Cone" wrote: Ronny, Your rules are not completely clear to me. The following is my interpretation. Give it a try. -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Not Intersect(Target, Range("C11,I11")) Is Nothing Then If Target.Address = "$C$11" Then If Len(Target.Value) Then Target.ClearContents Me.Range("I11").Value = "X" Else Me.Range("I11").ClearContents Target.Value = "X" End If Else Target.Value = "X" Me.Range("C11").ClearContents End If End If End Sub '-------------- "Ronny Hamida" wrote in message Hello again all of you VERY helpful people! :) I have a macro to add an "X" to certain cells using the Worksheet_Intersect VBA function. The problem is that I would like to see only 1 "X" at a time, but the following macro will allow for both cells (C11 and I11) to have an "X" at the same time. ----- Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Not Intersect(Target, Range("C11,I11")) Is Nothing Then If Target = vbNullString Then Target = "X" Else Target = vbNullString End If End If End Sub ----- Can I add something to this where if C11 already has an "X" and they click on either C11 or I11, it will remove the "X" from C11 and put one in I11? Thank you! Ronny |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need to remove text in specific place | Excel Worksheet Functions | |||
Remove data from certain rows and place in new row or spreadsheet | Excel Worksheet Functions | |||
Intersect Operator vs Explicit Cell References and the Dependency | Excel Programming | |||
how to get the intersect cell data out using vba or function | Excel Worksheet Functions | |||
Is there a "Non-Intersect" VBA method to remove a sub-range from a range? | Excel Programming |