Reset picture count?

H

HotLanta

Board Regular
Joined
Nov 3, 2005
Messages
176
I have several pictures in a spreadsheet that I want to paste into another worksheet. Then I want to run a macro to place those pictures into the exact postions I want them in everytime I do this.

The pictures are named "Picture 1, Picture 2, and Picture 3". I discovered that when I paste the pictures into the second document, the pictures are renamed "Picture 4, Picture 5, and Picture 6". If I paste them a second time they are then named "Picture 7, Picture 8, and Picture 9". I will never paste them twice, I just noticed that excel looks at how many pictures are present and increments the name by one.

My macro that I want to create will never work if it is searching for "Picture 1" and it isn't there.

My question is - Is there any way to reset the picture count or have the pictures start at the name "Picture 1" each time I open the file?

Thanks in advance!
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Sort by date Sort by votes
The following macro will rename all "Pictures" on a worksheet starting at "Picture 1". It will ignore all other shapes (rectangles etc).
The code can be easily modified to go through all worksheets in a workbook incrementing the Picture count, by using another loop from 1 to activeworkbook.sheets.count

Code: 
Sub PictureRename()
  Dim iLoop As Integer
  Dim iCtr As Integer

  iCtr = 1
  For iLoop = 1 To ActiveSheet.Shapes.Count
    If Left(ActiveSheet.Shapes.Item(iLoop).Name, 7) = "Picture" Then
      ActiveSheet.Shapes.Item(iLoop).Name = "Picture " + Str(iCtr)
      iCtr = iCtr + 1
    End If
  Next iLoop
End Sub

Hope this helps.
 
Upvote 0
As far as I can tell from limited testing, this code errors if a picture with the same name (e.g. 'Picture 7') already exists when the code tries to rename a picture called e.g. 'Picture X' to Picture 7. One can work around this by using

Code: 
 On Error Resume Next

before the line that attempts the renaming. But is there a better way of looping (or not?) through all pictures in a sheet and renaming them to 'Picture 1' to 'Picture n'?
 
Upvote 0
Hi,

perhaps loop twice

first loop
rename all pictures which are named "picture x" to
"thisnameisunlikely " & i
second loop
Picture 1
Picture 2
...

I wouldn't see how else you can avoid "jumping over"*

*example of picture names in the order the code would "call" them
abc
xyz
Picture 2
bbb
with a single loop the result would be
Picture 1
xyz
Picture 2
Picture 4

is this what you asked ?

kind regards,
Erik
 
Upvote 0
Hi Erik

Thank you for the reply. Yes I had thought of looping twice because I tend to 'loop before I think'.

I didn't really phrase my question very well :oops: I was really wondering if there was some code that would grab the whole collection of pictures and do a mass rename. I guess not...

Thanks anyway :)
 
Upvote 0
you're WELCOME

I can not imagine how renaming could be done "en masse" without loop
I could imagine some code which would only run once

when error occurs "name already exists", the code would go to the next name (in my example "Picture 3") and put the other picturename in an array
the code would need to check if the current picture is already in the array, else move on

result
start with
abc
xyz
Picture 2
bbb

abc renamed to "Picture 1"
xyz renamed to "Picture 3"
"Picture 2" put in array
Picture 2 is skipped because found in the array
bbb renamed to "Picture 4"

another method
loop through names, put in array
replace arrayitems, using one of the above methods
replace picturenames using array

depending of number of pictures and number of names "to replace or not", one or the other could be faster
 
Upvote 0
Thanks again, Erik.

Those are some good ideas for me to experiment with. And thanks for confirming that there is no 'en masse' method.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
Picture Insert Macro Question
Replies
1
Views
106
NdNoviceHlp
NdNoviceHlp
novabond
  • Question
VBA to add/change loaded ´Picture´ of an Image within a UserForm
Replies
9
Views
1K
bobsan42
bobsan42
M
  • Question
VBA search for specific file name in folders and their subfolders
Replies
7
Views
976
Candyman8019
C
M
  • Question
Error 1004 - Can't Copy as Picture, but only on certain computer
2
Replies
10
Views
384
rlv01
R
cyrilbrd
  • Question
VBA display pictures in a table
Replies
2
Views
344
cyrilbrd
cyrilbrd

Forum statistics

Threads
1,215,766
Messages
6,126,758
Members
449,336
Latest member
p17tootie

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top