IF Statements to Select Case???

[Walter]

How would I go about changing the following code to a select case statement?
I got the first snippet of code off of Jon Peltier's website for linking from
a spreadsheet to a chart. Right now I have these eight links which go to 8
different chart tabs. However, I have about 29 more links on my "dashboard"
to go to a bunch more charts that is why I am considering a Select Case
scenario. Thanks for your help!

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("D6")) Is Nothing Then
On Error Resume Next
Charts(Target.Value).Activate
If Err.Number <> 0 Then
MsgBox "No such chart exists.", vbCritical, "Chart Not Found"
End If
On Error GoTo 0
End If
If Not Intersect(Target, Range("F6")) Is Nothing Then
On Error Resume Next
Charts(Target.Value).Activate
If Err.Number <> 0 Then
MsgBox "No such chart exists.", vbCritical, "Chart Not Found"
End If
On Error GoTo 0
End If
If Not Intersect(Target, Range("H6")) Is Nothing Then
On Error Resume Next
Charts(Target.Value).Activate
If Err.Number <> 0 Then
MsgBox "No such chart exists.", vbCritical, "Chart Not Found"
End If
On Error GoTo 0
End If
If Not Intersect(Target, Range("J6")) Is Nothing Then
On Error Resume Next
Charts(Target.Value).Activate
If Err.Number <> 0 Then
MsgBox "No such chart exists.", vbCritical, "Chart Not Found"
End If
On Error GoTo 0
End If
If Not Intersect(Target, Range("L6")) Is Nothing Then
On Error Resume Next
Charts(Target.Value).Activate
If Err.Number <> 0 Then
MsgBox "No such chart exists.", vbCritical, "Chart Not Found"
End If
On Error GoTo 0
End If
If Not Intersect(Target, Range("N6")) Is Nothing Then
On Error Resume Next
Charts(Target.Value).Activate
If Err.Number <> 0 Then
MsgBox "No such chart exists.", vbCritical, "Chart Not Found"
End If
On Error GoTo 0
End If
If Not Intersect(Target, Range("P6")) Is Nothing Then
On Error Resume Next
Charts(Target.Value).Activate
If Err.Number <> 0 Then
MsgBox "No such chart exists.", vbCritical, "Chart Not Found"
End If
On Error GoTo 0
End If
If Not Intersect(Target, Range("R6")) Is Nothing Then
On Error Resume Next
Charts(Target.Value).Activate
If Err.Number <> 0 Then
MsgBox "No such chart exists.", vbCritical, "Chart Not Found"
End If
On Error GoTo 0
End If
End Sub

 

[Mike H]

Walter,

The syntax is like this

Select Case Target.Address
Case Is = "$D$6"
'Your Code
Case Is = "$F$6"
'
Case Is = "$H$6"

Case Is = "$L$6"

Case Is = "$N$6"

Case Is = "$P$6"

Case Is = "$R$6"

Case Else


End Select


Mike

 

[Luke M]

Perhaps something like this would be work in your case (pased on pattern I
see). If it doesn't work, note that you can call out more than one Range in
the Intersect function (just list all possible ranges there).


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Place all possible intersects in one statement
x = Target.Column
If Int(x / 2) <> x / 2 Then Exit Sub 'check if even
If x < 4 Or x > 30 Then Exit Sub 'set column boundaries
On Error Resume Next
Charts(Target.Value).Activate
If Err.Number <> 0 Then
MsgBox "No such chart exists.", vbCritical, "Chart Not Found"
End If
On Error GoTo 0

End Sub