macro problem: change values when selected

[Aries]

I wrote a macro to change the value of a cell in the range B3:B17 from "y" to "" and from "" to "y" whenever it is selected. The macro works but when I select more than one cell, it fails.

Private Sub Worksheet_SelectionChange(ByVal_
Target As Excel.Range)
If Target.Column = 2 And Target.Row >= 3_
And Target.Row <= 17 Then
ThisRow = Target.Row
If Target.Value = "" Then
Target.Value = "y"
End
End If
If Target.Value = "y" Then
Target.Value = ""
End
End If
End If
End Sub

How can I improve the codes?

 

[Damon Ostrander]

Hi Aries,

You didn't mention what you WANT the macro to do when you select multiple cells. For example do you want it to

- do nothing?

- run your algorithm on all the selected cells?

- run your algorithm on just the first cell in the range?

- run your algorithm on the active cell in the range?

Let us know and an answer will be quickly forthcoming.

Damon

 

[Aries]

On 2002-10-05 14:40, Damon Ostrander wrote:
Hi Aries,

You didn't mention what you WANT the macro to do when you select multiple cells. For example do you want it to

Damon

I want to run the algorithm on the active cells in the range. Thank you

 

[Legacy 98055]

Hi Aries.
Paste this in:<pre>
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim c As Range
For Each c In Selection
If Not Intersect(c, Range("B3:B17")) Is Nothing Then
Select Case c
Case "y": c.Value = ""
Case "": c.Value = "y"
Case Else
MsgBox "Invalid entry in Cell " & c.Address
c.Select
Exit Sub
End Select
End If
Next
End Sub</pre>
Tom
This message was edited by TsTom on 2002-10-05 15:03

 

[Aries]

thank you very much. It works perfectly.

On 2002-10-05 15:01, TsTom wrote:
Hi Aries.
Paste this in:<pre>
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim c As Range
For Each c In Selection
If Not Intersect(c, Range("B3:B17")) Is Nothing Then
Select Case c
Case "y": c.Value = ""
Case "": c.Value = "y"
Case Else
MsgBox "Invalid entry in Cell " & c.Address
c.Select
Exit Sub
End Select
End If
Next
End Sub</pre>
Tom
This message was edited by TsTom on 2002-10-05 15:03