Selection of only one object  
Author Message
AndersBank





PostPosted: Visual Basic for Applications (VBA), Selection of only one object Top

I have a macro that lets the user first select a number of charts and then when the user presses a button the charts sizes and color are eing changed. The problem is that it does not works if I only have one chart selected. When I have two or more it works fine. My code (relevant parts) is:

Public Sub arrayLoop()
Dim obj As Object
Dim currentChart As Object

For Each obj In Selection
If TypeName(obj) = "ChartObject" Then
Set currentChart = obj
Call linjeDiagramKnapp(currentChart)
End If
Next
End Sub

In this sub I check the selection to see if the selected objects really are charts. I assume that there is something wrong here on the first line of the For-loop, but I do not know how it should be or if the problem might be elsewhere. Any help appreciated! Thanks alot!



Microsoft ISV Community Center Forums2  
 
 
duck thing





PostPosted: Visual Basic for Applications (VBA), Selection of only one object Top

The problem is that if only one chart is selected, Selection is an object rather than a collection of objects. So your "For Each" statement can't be interpreted. I'm working on a workaround for this right now, should have something up and running shortly.



 
 
duck thing





PostPosted: Visual Basic for Applications (VBA), Selection of only one object Top

Here you go. Basically this code just traps for an "Object does not support this method" error message. If it finds one, it sets cc to be the ChartArea the user has selected. You can use cc.Parent if you want to access the Chart object itself. Hope this helps!

Option Explicit
Option Base 0

Public Sub main()
Dim ss As Object, pp As Variant, cc As ChartArea
On Error GoTo err_selection

For Each ss In Selection
MsgBox ss.Name
Next ss
Exit Sub

err_selection:
If Err.Number = 438 Then
Set pp = Selection
If TypeName(pp) <> "ChartArea" Then Exit Sub ' ignore non-chart selection
Set cc = pp
End If
MsgBox cc.Parent.Name
End Sub