Clicking on Cells to execute code

[mpadgett]

I've successfully written some code to execute a certain task if the user clicks on a cell. This is done by have a conditional statement in the Work Sheet code.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Address = "$B$13" And Target.Value = "" Then
"code continues here but omitted to save space"

This code actually needs to needs to kick-off if the user clicks on any of the cells below:

$B$13 $C$13 $D$13 $E$13 $F$13
$B$14 $C$14 $D$14 $E$14 $F$14
$B$15 $C$15 $D$15 $E$15 $F$15

I'm trying to avoid a long nested IF statement that evaluate for each cell. I thought about the Select Case statement but I'm not sure if that would save me much coding.

Any thoughts or suggestions would be appreciated.

MP

 

[CBasicAsslember]

Use Select Case. You can put all the cells in the same Case statement seperated by a comma.

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim sTemp As String

sTemp = Target.Address

Select Case sTemp

Case "$B$13", "$B$13", "$C$13", "$D$13", "$E$13"
   ' Insert code here
Case Else

End Select
End Sub

 

[Zathras]

It can be simpler than that:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Not Intersect(Target, Range("B13:F15")) Is Nothing Then
    MsgBox ("You clicked " & Target.Address)
  End If
End Sub

 

[SkipVought]

You might also want to consider what happens if the user selects more than one cell in the selection.

if target.count>1 then
'...what?

Skip,

_[red][/red]

 

[Zathras]

If selecting more than one cell implies to perform whatever it is you want to do on each of them, then this illustrates how that is possible:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim c As Range
  If Not Intersect(Target, Range("B13:F15")) Is Nothing Then
    For Each c In Intersect(Target, Range("B13:F15"))
      MsgBox ("You clicked " & c.Address)
    Next c
  End If
End Sub