select next

[Guest]

I need for a macro to run and then have the next object selected. What is
the one line that will get me that "select next object in the sheet" code? I
don't want it to go through all of them, just to do the equivalent of
clicking on the next graph I have in number order.

Thx.

 

[Guest]

The exact syntax will depend on the objects you are trying to llop through
but essentially you need a For Each..Next loop through the object collection
so

For each objectVariable in SheetRef.objectCollection
Process objectVariable
Next

so to iterate through the textboxes on a sheet, you would reference the
SHAPES collection:

For Each shp In ActiveSheet.Shapes
MsgBox shp.Name
Next

 

[Guest]

so here is what I have (graphs), and it is not working:

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


again, however, my ideal scenario is not to have the macro go automatically
through each chart. Rather, I have some sheets where I need it just to go
through all, make changes I indicate, and then be done with the sheet.

but other sheets, I actually would prefer to go one by one, however not
having to hold the mouse and click on each successive graph. I want to
assign a shortcut key to run (I'll select the first graph), and end with a
line that basically does the same as simply clicking on the next graph (but
not running the macro over (which I understand for-each will force it to do).
Then once it's selected the next graph, I'll again hit the shortcut if I'm
sure I want to run it on that graph.

Thx for any further help.

 

[Guest]

Best bet would be to use the shapes collection and use the index to move
around within that - shapes contain charts so could be of use to you

You can test the shape type using error handling and the "autoshapetype" or
"formcontroltype" properties
--
Rgds, Geoff

"A crash reduces
Your expensive computer
To a simple stone"

so here is what I have (graphs), and it is not working:

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


again, however, my ideal scenario is not to have the macro go automatically
through each chart. Rather, I have some sheets where I need it just to go
through all, make changes I indicate, and then be done with the sheet.

but other sheets, I actually would prefer to go one by one, however not
having to hold the mouse and click on each successive graph. I want to
assign a shortcut key to run (I'll select the first graph), and end with a
line that basically does the same as simply clicking on the next graph (but
not running the macro over (which I understand for-each will force it to do).
Then once it's selected the next graph, I'll again hit the shortcut if I'm
sure I want to run it on that graph.

Thx for any further help.
--
Boris

The exact syntax will depend on the objects you are trying to llop through
but essentially you need a For Each..Next loop through the object collection
so

For each objectVariable in SheetRef.objectCollection
Process objectVariable
Next

so to iterate through the textboxes on a sheet, you would reference the
SHAPES collection:

For Each shp In ActiveSheet.Shapes
MsgBox shp.Name
Next

 

[Guest]

If you look at your code at the end, the selection is not a chartobject. I
gave you code to handle this in a previous thread.

here is a simpler version of that code:

Sub AA_SelectNext()
Dim obj As Object
If Not ActiveChart Is Nothing Then
Set obj = ActiveChart.Parent
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

Unfortunately, there is not one line of code that will do this that I am
aware of.
ActiveChart.Next does not work for what you want.

--
regards,
Tom Ogilvy

so here is what I have (graphs), and it is not working:

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


again, however, my ideal scenario is not to have the macro go automatically
through each chart. Rather, I have some sheets where I need it just to go
through all, make changes I indicate, and then be done with the sheet.

but other sheets, I actually would prefer to go one by one, however not
having to hold the mouse and click on each successive graph. I want to
assign a shortcut key to run (I'll select the first graph), and end with a
line that basically does the same as simply clicking on the next graph (but
not running the macro over (which I understand for-each will force it to do).
Then once it's selected the next graph, I'll again hit the shortcut if I'm
sure I want to run it on that graph.

Thx for any further help.
--
Boris

The exact syntax will depend on the objects you are trying to llop through
but essentially you need a For Each..Next loop through the object collection
so

For each objectVariable in SheetRef.objectCollection
Process objectVariable
Next

so to iterate through the textboxes on a sheet, you would reference the
SHAPES collection:

For Each shp In ActiveSheet.Shapes
MsgBox shp.Name
Next

 

[Guest]

maybe unrelated, but is there an equivalent of the "nothing" test for whether
or not there is a secondary axis? In other words, I have a combination of
graphs on some sheets, some with 2 and some 1 axis. I want to make sure all
axes are arial 10 bold, let's say. To do that, I would run what I had (minus
the number formatting). But I need, within the macro, to have the question
asked, "is there a second axis, and if so, then do XYZ". How would I get the
macro to ask that question, so I can then insert the secondary axis code
after that question. If there is no second axis, I would want it to go about
its business and skip the secondary axis code, so as to avoid a crash.
--
Boris

If you look at your code at the end, the selection is not a chartobject. I
gave you code to handle this in a previous thread.

here is a simpler version of that code:

Sub AA_SelectNext()
Dim obj As Object
If Not ActiveChart Is Nothing Then
Set obj = ActiveChart.Parent
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

Unfortunately, there is not one line of code that will do this that I am
aware of.
ActiveChart.Next does not work for what you want.

--
regards,
Tom Ogilvy

so here is what I have (graphs), and it is not working:

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


again, however, my ideal scenario is not to have the macro go automatically
through each chart. Rather, I have some sheets where I need it just to go
through all, make changes I indicate, and then be done with the sheet.

but other sheets, I actually would prefer to go one by one, however not
having to hold the mouse and click on each successive graph. I want to
assign a shortcut key to run (I'll select the first graph), and end with a
line that basically does the same as simply clicking on the next graph (but
not running the macro over (which I understand for-each will force it to do).
Then once it's selected the next graph, I'll again hit the shortcut if I'm
sure I want to run it on that graph.

Thx for any further help.
--
Boris

The exact syntax will depend on the objects you are trying to llop through
but essentially you need a For Each..Next loop through the object collection
so

For each objectVariable in SheetRef.objectCollection
Process objectVariable
Next

so to iterate through the textboxes on a sheet, you would reference the
SHAPES collection:

For Each shp In ActiveSheet.Shapes
MsgBox shp.Name
Next
--
Rgds, Geoff




:

I need for a macro to run and then have the next object selected. What is
the one line that will get me that "select next object in the sheet" code? I
don't want it to go through all of them, just to do the equivalent of
clicking on the next graph I have in number order.

Thx.

 

[Guest]

from the immediate window as a demo

? activechart.HasAxis(xlValue, xlPrimary)
True
? activechart.HasAxis(xlValue,xlSecondary)
False


if activechart.hasAxis(xlValue, xlSecondary) then
' set attributes for this axis
--
Regards,
Tom Ogilvy

maybe unrelated, but is there an equivalent of the "nothing" test for whether
or not there is a secondary axis? In other words, I have a combination of
graphs on some sheets, some with 2 and some 1 axis. I want to make sure all
axes are arial 10 bold, let's say. To do that, I would run what I had (minus
the number formatting). But I need, within the macro, to have the question
asked, "is there a second axis, and if so, then do XYZ". How would I get the
macro to ask that question, so I can then insert the secondary axis code
after that question. If there is no second axis, I would want it to go about
its business and skip the secondary axis code, so as to avoid a crash.
--
Boris

If you look at your code at the end, the selection is not a chartobject. I
gave you code to handle this in a previous thread.

here is a simpler version of that code:

Sub AA_SelectNext()
Dim obj As Object
If Not ActiveChart Is Nothing Then
Set obj = ActiveChart.Parent
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

Unfortunately, there is not one line of code that will do this that I am
aware of.
ActiveChart.Next does not work for what you want.

--
regards,
Tom Ogilvy

so here is what I have (graphs), and it is not working:

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


again, however, my ideal scenario is not to have the macro go automatically
through each chart. Rather, I have some sheets where I need it just to go
through all, make changes I indicate, and then be done with the sheet.

but other sheets, I actually would prefer to go one by one, however not
having to hold the mouse and click on each successive graph. I want to
assign a shortcut key to run (I'll select the first graph), and end with a
line that basically does the same as simply clicking on the next graph (but
not running the macro over (which I understand for-each will force it to do).
Then once it's selected the next graph, I'll again hit the shortcut if I'm
sure I want to run it on that graph.

Thx for any further help.
--
Boris


:

The exact syntax will depend on the objects you are trying to llop through
but essentially you need a For Each..Next loop through the object collection
so

For each objectVariable in SheetRef.objectCollection
Process objectVariable
Next

so to iterate through the textboxes on a sheet, you would reference the
SHAPES collection:

For Each shp In ActiveSheet.Shapes
MsgBox shp.Name
Next
--
Rgds, Geoff




:

I need for a macro to run and then have the next object selected. What is
the one line that will get me that "select next object in the sheet" code? I
don't want it to go through all of them, just to do the equivalent of
clicking on the next graph I have in number order.

Thx.