next object selection

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

had Tom help me previously with following command as part of larger code

activesheet.ChartObjects(1).Chart.Axes(xlValue,xlSecondary).TickLabels.Font.Name
Book Antiqua

This, as I understand it, depends on knowing which object you want to
select. But if I am using '07 (in case it matters) and want the macro to 1)
determine how many objects are on the page and the 2) go through each object
one by one, and complete a set of steps, how would I do that?

Additionally, if I were to want to not have it go through automatically and
do each, what is the final command I can put on my code to say, effectively,
"pick the next object on the sheet."

Thx in advance.
 
Dim cobj as ChartObject
for each cobj in Activesheet.ChartObjects


Next cobj

Keep track of the index number of where you are


just to illustrate from the immediate window:

? activesheet.chartobjects.count
4
? activesheet.chartobjects(1).name
Chart 1
? activesheet.chartobjects(3).name
Chart 3
activesheet.chartobjects(2).Select
? selection.name
Chart 2
 
Tom, thanks so much for coming through again. For the second part of the ? I
listed, do you know how I would command the active selection to simply pick
the next object? Only issue is that if I do for-each-next, it'll have to run
through all of them (which is fine on some of my sheets). But in some cases,
I need to visually scan the object before running the macro, so was planning
on avoid the for each, and just running macro, with final line saying "select
the next object in the sheet", so I could look at it, before hitting the
shortcut I'll assign to the macro.

Thanks for the further piece.
 
Tom, tried the code I had (combination of your two pieces) and am not seeing
the macro move through the objects. I am stepping through, and upon
finishing the code, it does not go through to the next object. Just sits
where it is, with the first graph selected. Here is what I have as code:

Dim cobj As ChartObject

For Each cobj In ActiveSheet.ChartObjects

ActiveChart.Axes(xlValue, xlPrimary).Select
Selection.TickLabels.NumberFormat = "#,##0.0_);[Red](#,##0.0)"
Selection.TickLabels.AutoScaleFont = True
With Selection.TickLabels.Font
..Name = "Arial"
..FontStyle = "Regular"
..Size = 10
..Strikethrough = False
..Superscript = False
..Subscript = False
..OutlineFont = False
..Shadow = False
..Underline = xlUnderlineStyleNone
..ColorIndex = xlAutomatic
..Background = xlAutomatic
End With
ActiveChart.Axes(xlValue, xlSecondary).Select
Selection.TickLabels.NumberFormat = "#,##0_);[Red](#,##0)"
Selection.TickLabels.AutoScaleFont = True
With Selection.TickLabels.Font
..Name = "Arial"
..FontStyle = "Regular"
..Size = 10
..Strikethrough = False
..Superscript = False
..Subscript = False
..OutlineFont = False
..Shadow = False
..Underline = xlUnderlineStyleNone
..ColorIndex = xlAutomatic
..Background = xlAutomatic
End With

Next cobj

End Sub


It works on just one fine. But it doesn't step through. Can you decipher
where I am screwing up?
 
Dim cobj As ChartObject

For Each cobj In ActiveSheet.ChartObjects
cobj.select '<== added line
ActiveChart.Axes(xlValue, xlPrimary).Select
Selection.TickLabels.NumberFormat = "#,##0.0_);[Red](#,##0.0)"
Selection.TickLabels.AutoScaleFont = True
With Selection.TickLabels.Font
..Name = "Arial"
..FontStyle = "Regular"
..Size = 10
..Strikethrough = False
..Superscript = False
..Subscript = False
..OutlineFont = False
..Shadow = False
..Underline = xlUnderlineStyleNone
..ColorIndex = xlAutomatic
..Background = xlAutomatic
End With
ActiveChart.Axes(xlValue, xlSecondary).Select
Selection.TickLabels.NumberFormat = "#,##0_);[Red](#,##0)"
Selection.TickLabels.AutoScaleFont = True
With Selection.TickLabels.Font
..Name = "Arial"
..FontStyle = "Regular"
..Size = 10
..Strikethrough = False
..Superscript = False
..Subscript = False
..OutlineFont = False
..Shadow = False
..Underline = xlUnderlineStyleNone
..ColorIndex = xlAutomatic
..Background = xlAutomatic
End With

Next cobj

End Sub


for your previous question about selecting the next chartobject.
This code selects the next chartobject:

Sub AA_SelectNext()
Dim obj As Object
s = TypeName(Selection)
Set obj = Selection
Do Until LCase(s) = "chartobject"
Set obj = obj.Parent
s = TypeName(obj)
Debug.Print s
If LCase(s) = "worksheet" Then
MsgBox "Chart Object not selected"
Exit Sub
End If
Loop
If LCase(TypeName(obj)) = "chartobject" Then
i = obj.Index
obj.TopLeftCell.Select
If i < ActiveSheet.ChartObjects.Count Then
ActiveSheet.ChartObjects(i + 1).Select
Else
ActiveSheet.ChartObjects(1).Select
End If
Else
MsgBox "chartObject is not selected"
End If

End Sub

--
Regards,
Tom Ogilvy




BorisS said:
Tom, tried the code I had (combination of your two pieces) and am not seeing
the macro move through the objects. I am stepping through, and upon
finishing the code, it does not go through to the next object. Just sits
where it is, with the first graph selected. Here is what I have as code:

Dim cobj As ChartObject

For Each cobj In ActiveSheet.ChartObjects

ActiveChart.Axes(xlValue, xlPrimary).Select
Selection.TickLabels.NumberFormat = "#,##0.0_);[Red](#,##0.0)"
Selection.TickLabels.AutoScaleFont = True
With Selection.TickLabels.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
ActiveChart.Axes(xlValue, xlSecondary).Select
Selection.TickLabels.NumberFormat = "#,##0_);[Red](#,##0)"
Selection.TickLabels.AutoScaleFont = True
With Selection.TickLabels.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With

Next cobj

End Sub


It works on just one fine. But it doesn't step through. Can you decipher
where I am screwing up?
--
Boris


Tom Ogilvy said:
Dim cobj as ChartObject
for each cobj in Activesheet.ChartObjects


Next cobj

Keep track of the index number of where you are


just to illustrate from the immediate window:

? activesheet.chartobjects.count
4
? activesheet.chartobjects(1).name
Chart 1
? activesheet.chartobjects(3).name
Chart 3
activesheet.chartobjects(2).Select
? selection.name
Chart 2
Click to expand...
Click to expand...
 
Back
Top