next object selection
 

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

activesheet.ChartObjects(1).Chart.Axes(xlValue,xlS econdary).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.


--
Boris

next object selection
 

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

--
Regards,
Tom Ogilvy



"BorisS" wrote:

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

activesheet.ChartObjects(1).Chart.Axes(xlValue,xlS econdary).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.


--
Boris

next object selection
 

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.
--
Boris


"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy



"BorisS" wrote:

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

activesheet.ChartObjects(1).Chart.Axes(xlValue,xlS econdary).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.


--
Boris

next object selection
 

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" wrote:

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

--
Regards,
Tom Ogilvy



"BorisS" wrote:

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

activesheet.ChartObjects(1).Chart.Axes(xlValue,xlS econdary).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.


--
Boris

next object selection
 

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" wrote:

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" wrote:

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

--
Regards,
Tom Ogilvy



"BorisS" wrote:

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

activesheet.ChartObjects(1).Chart.Axes(xlValue,xlS econdary).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.


--
Boris