Picture # assignment
 

My book has many pictures.
Picture #s kept going higher and higher every time I made a change to the
book and the #s were skipping.
Is there a way to get a list of the Picture #s in the book.
I want to rename all the Pictures to a low # start and more consecutive.
Thanks

Picture # assignment
 

You can run this line of code in the Immediate window of the Visual Basic
editor and it will list the names of all shapes on the specified sheet...

For Each S In Sheet1.Shapes : ? S.Name : Next

--
Rick (MVP - Excel)


"danpt" wrote in message
...

My book has many pictures.
Picture #s kept going higher and higher every time I made a change to the
book and the #s were skipping.
Is there a way to get a list of the Picture #s in the book.
I want to rename all the Pictures to a low # start and more consecutive.
Thanks

Picture # assignment
 

Hi, Rick
Glad to hear you again.

Sub PictureList()
For Each S In Sheet1.Shapes: Print S.Name: Next
End Sub

I got a
Compile error: Mathod not valid without suitable object
I'm new to this. Please refine it.


"Rick Rothstein" wrote:

You can run this line of code in the Immediate window of the Visual Basic
editor and it will list the names of all shapes on the specified sheet...

For Each S In Sheet1.Shapes : ? S.Name : Next

--
Rick (MVP - Excel)


"danpt" wrote in message
...

My book has many pictures.
Picture #s kept going higher and higher every time I made a change to the
book and the #s were skipping.
Is there a way to get a list of the Picture #s in the book.
I want to rename all the Pictures to a low # start and more consecutive.
Thanks

Picture # assignment
 

On a sheet select all pictures then run this macro.

Public Sub ReNamePics()
Dim Pic As Shape, K As Long
For Each Pic In Selection.ShapeRange
K = K + 1
Pic.Name = "MyPic" & K
Next Pic
End Sub

Or run this is you have a list of names to use.

Sub Rename_Pics22()
'select all pictures then rename with a list starting at A2
Dim Pic As Shape
Dim rng As Range
Dim i As Integer
On Error GoTo endit
Set rng = Range("A2")
For Each Pic In Selection.ShapeRange
Pic.Name = rng.Offset(i, 0).Value
i = i + 1
Next Pic
Exit Sub
endit:
MsgBox "there is a picture by that name, re-type a name"
End Sub


Gord Dibben MS Excel MVP

On Thu, 2 Apr 2009 21:52:04 -0700, danpt
wrote:


My book has many pictures.
Picture #s kept going higher and higher every time I made a change to the
book and the #s were skipping.
Is there a way to get a list of the Picture #s in the book.
I want to rename all the Pictures to a low # start and more consecutive.
Thanks

Picture # assignment
 

You didn't do what I said to do... run the single line of code I posted in
the Immediate window. I think you had a problem because you tried to make it
a macro... the Print statement has no meaning in a macro. If you want to
keep it as a macro, change the keyword Print to Debug.Print

--
Rick (MVP - Excel)


"danpt" wrote in message
...
Hi, Rick
Glad to hear you again.

Sub PictureList()
For Each S In Sheet1.Shapes: Print S.Name: Next
End Sub

I got a
Compile error: Mathod not valid without suitable object
I'm new to this. Please refine it.


"Rick Rothstein" wrote:

You can run this line of code in the Immediate window of the Visual Basic
editor and it will list the names of all shapes on the specified sheet...

For Each S In Sheet1.Shapes : ? S.Name : Next

--
Rick (MVP - Excel)


"danpt" wrote in message
...

My book has many pictures.
Picture #s kept going higher and higher every time I made a change to
the
book and the #s were skipping.
Is there a way to get a list of the Picture #s in the book.
I want to rename all the Pictures to a low # start and more
consecutive.
Thanks

Picture # assignment
 

Hi, Rick
Not because I didn't want to, but simply I'm igorant on how to.
Will you walk me through - !! Help !!
It is of great importance for me to get this.

"Rick Rothstein" wrote:

You didn't do what I said to do... run the single line of code I posted in
the Immediate window. I think you had a problem because you tried to make it
a macro... the Print statement has no meaning in a macro. If you want to
keep it as a macro, change the keyword Print to Debug.Print

--
Rick (MVP - Excel)


"danpt" wrote in message
...
Hi, Rick
Glad to hear you again.

Sub PictureList()
For Each S In Sheet1.Shapes: Print S.Name: Next
End Sub

I got a
Compile error: Mathod not valid without suitable object
I'm new to this. Please refine it.


"Rick Rothstein" wrote:

You can run this line of code in the Immediate window of the Visual Basic
editor and it will list the names of all shapes on the specified sheet...

For Each S In Sheet1.Shapes : ? S.Name : Next

--
Rick (MVP - Excel)


"danpt" wrote in message
...

My book has many pictures.
Picture #s kept going higher and higher every time I made a change to
the
book and the #s were skipping.
Is there a way to get a list of the Picture #s in the book.
I want to rename all the Pictures to a low # start and more
consecutive.
Thanks

Picture # assignment
 

Many thanks, Gord
I found out that I couldn't use
Pic.Name = "Picture " & K
Is it correct?

"Gord Dibben" wrote:

On a sheet select all pictures then run this macro.

Public Sub ReNamePics()
Dim Pic As Shape, K As Long
For Each Pic In Selection.ShapeRange
K = K + 1
Pic.Name = "MyPic" & K
Next Pic
End Sub

Or run this is you have a list of names to use.

Sub Rename_Pics22()
'select all pictures then rename with a list starting at A2
Dim Pic As Shape
Dim rng As Range
Dim i As Integer
On Error GoTo endit
Set rng = Range("A2")
For Each Pic In Selection.ShapeRange
Pic.Name = rng.Offset(i, 0).Value
i = i + 1
Next Pic
Exit Sub
endit:
MsgBox "there is a picture by that name, re-type a name"
End Sub


Gord Dibben MS Excel MVP

On Thu, 2 Apr 2009 21:52:04 -0700, danpt
wrote:


My book has many pictures.
Picture #s kept going higher and higher every time I made a change to the
book and the #s were skipping.
Is there a way to get a list of the Picture #s in the book.
I want to rename all the Pictures to a low # start and more consecutive.
Thanks

Picture # assignment
 

You are correct.

Doesn't like the space in "Picture "

Not sure why but will ponder on that a while.

Try this revision...................rename by double-naming then rename
again.

Public Sub ReNamePics()
Dim Pic As Shape, K As Long
For Each Pic In Selection.ShapeRange
Pic.Name = Pic.Name & Pic.Name
Next Pic
For Each Pic In Selection.ShapeRange
K = K + 1
Pic.Name = "Picture " & K
Next Pic
End Sub


Gord

On Fri, 3 Apr 2009 14:51:01 -0700, danpt
wrote:

Many thanks, Gord
I found out that I couldn't use
Pic.Name = "Picture " & K
Is it correct?

"Gord Dibben" wrote:

On a sheet select all pictures then run this macro.

Public Sub ReNamePics()
Dim Pic As Shape, K As Long
For Each Pic In Selection.ShapeRange
K = K + 1
Pic.Name = "MyPic" & K
Next Pic
End Sub

Or run this is you have a list of names to use.

Sub Rename_Pics22()
'select all pictures then rename with a list starting at A2
Dim Pic As Shape
Dim rng As Range
Dim i As Integer
On Error GoTo endit
Set rng = Range("A2")
For Each Pic In Selection.ShapeRange
Pic.Name = rng.Offset(i, 0).Value
i = i + 1
Next Pic
Exit Sub
endit:
MsgBox "there is a picture by that name, re-type a name"
End Sub


Gord Dibben MS Excel MVP

On Thu, 2 Apr 2009 21:52:04 -0700, danpt
wrote:


My book has many pictures.
Picture #s kept going higher and higher every time I made a change to the
book and the #s were skipping.
Is there a way to get a list of the Picture #s in the book.
I want to rename all the Pictures to a low # start and more consecutive.
Thanks

Picture # assignment
 

No, still no good


"Gord Dibben" wrote:

You are correct.

Doesn't like the space in "Picture "

Not sure why but will ponder on that a while.

Try this revision...................rename by double-naming then rename
again.

Public Sub ReNamePics()
Dim Pic As Shape, K As Long
For Each Pic In Selection.ShapeRange
Pic.Name = Pic.Name & Pic.Name
Next Pic
For Each Pic In Selection.ShapeRange
K = K + 1
Pic.Name = "Picture " & K
Next Pic
End Sub


Gord

On Fri, 3 Apr 2009 14:51:01 -0700, danpt
wrote:

Many thanks, Gord
I found out that I couldn't use
Pic.Name = "Picture " & K
Is it correct?

"Gord Dibben" wrote:

On a sheet select all pictures then run this macro.

Public Sub ReNamePics()
Dim Pic As Shape, K As Long
For Each Pic In Selection.ShapeRange
K = K + 1
Pic.Name = "MyPic" & K
Next Pic
End Sub

Or run this is you have a list of names to use.

Sub Rename_Pics22()
'select all pictures then rename with a list starting at A2
Dim Pic As Shape
Dim rng As Range
Dim i As Integer
On Error GoTo endit
Set rng = Range("A2")
For Each Pic In Selection.ShapeRange
Pic.Name = rng.Offset(i, 0).Value
i = i + 1
Next Pic
Exit Sub
endit:
MsgBox "there is a picture by that name, re-type a name"
End Sub


Gord Dibben MS Excel MVP

On Thu, 2 Apr 2009 21:52:04 -0700, danpt
wrote:


My book has many pictures.
Picture #s kept going higher and higher every time I made a change to the
book and the #s were skipping.
Is there a way to get a list of the Picture #s in the book.
I want to rename all the Pictures to a low # start and more consecutive.
Thanks

Picture # assignment
 

Haven't yet found a method to use a space with "Picture "

No problem with a space in any string except that.

I guess you'll just have to rename to something else or leave out the space.


Gord


On Sat, 4 Apr 2009 10:34:01 -0700, danpt
wrote:

No, still no good


"Gord Dibben" wrote:

You are correct.

Doesn't like the space in "Picture "

Not sure why but will ponder on that a while.

Try this revision...................rename by double-naming then rename
again.

Public Sub ReNamePics()
Dim Pic As Shape, K As Long
For Each Pic In Selection.ShapeRange
Pic.Name = Pic.Name & Pic.Name
Next Pic
For Each Pic In Selection.ShapeRange
K = K + 1
Pic.Name = "Picture " & K
Next Pic
End Sub


Gord

On Fri, 3 Apr 2009 14:51:01 -0700, danpt
wrote:

Many thanks, Gord
I found out that I couldn't use
Pic.Name = "Picture " & K
Is it correct?

"Gord Dibben" wrote:

On a sheet select all pictures then run this macro.

Public Sub ReNamePics()
Dim Pic As Shape, K As Long
For Each Pic In Selection.ShapeRange
K = K + 1
Pic.Name = "MyPic" & K
Next Pic
End Sub

Or run this is you have a list of names to use.

Sub Rename_Pics22()
'select all pictures then rename with a list starting at A2
Dim Pic As Shape
Dim rng As Range
Dim i As Integer
On Error GoTo endit
Set rng = Range("A2")
For Each Pic In Selection.ShapeRange
Pic.Name = rng.Offset(i, 0).Value
i = i + 1
Next Pic
Exit Sub
endit:
MsgBox "there is a picture by that name, re-type a name"
End Sub


Gord Dibben MS Excel MVP

On Thu, 2 Apr 2009 21:52:04 -0700, danpt
wrote:


My book has many pictures.
Picture #s kept going higher and higher every time I made a change to the
book and the #s were skipping.
Is there a way to get a list of the Picture #s in the book.
I want to rename all the Pictures to a low # start and more consecutive.
Thanks