Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF Statements to Select Case???
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF Statements to Select Case???
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 "Walter" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF Statements to Select Case???
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 -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Walter" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
select case in vba | Excel Discussion (Misc queries) | |||
select case | Excel Discussion (Misc queries) | |||
Select Case | Excel Discussion (Misc queries) | |||
Case without Select Case error problem | Excel Discussion (Misc queries) | |||
Case Statements in Excel 2003 | Excel Discussion (Misc queries) |