Insert Picture Name Assignment in Macro

[Frankenroc]

Greetings,

I'm basically using a plug in that creates a menu structure, and then
going into Excel and VB, creates a menu dynamically. The menu consists
of pictures, text boxes, and lines (looking like a hierarchy tree, for
example). Now, for simplicity sake, let's just say pictures, since
that's where the problem arised.

Basically, I use the command

Code:
--------------------

ActiveSheet.Pictures.Insert( [picture location here] ).Select
--------------------

to insert my picture. From there, it renames the picture to what I
want so when the macro is assigned, it can figure out which picture the
user clicked on so it can take the appropriate action.

When the picture is clicked, the VBA figures out what it needs to do,
and usually it destroys the menu it created and makes a new one. The
problem arose after extensive testing when, all of a sudden, the macro
could no longer select the file. It would insert the picture, but
couldn't select it using the same code that had worked before.

Figured out that it has something to do with the names automatically
assigned by Excel when a picture is inserted. The latest picture it
created was named "Picture 65536," and I'm sure you realize the
significance of that number. (2^16) Of course, I'd like to rename
this picture, but I need to get past this automatically assigned name
that Excel gives to it. Is there a way to reset this number or set it
to a certain amount?

I'm almost certain the number is stored somewhere on the sheet itself.
If I take this code to another sheet the "counter" is reset, and I've
done that as a temporary measure so I can continue working with and
testing the menu system. Also, even after a complete reboot of the
machine, I notice the auto-assigned number in the object name is at the
same level it was before. So I'm thinking I just need a way to access
that counter embedded in the sheet... any ideas?

 

[Dave Peterson]

How about just naming the picture when you insert it?

Option Explicit
Sub testme()

Dim myPict As Picture
Dim myRng As Range

With ActiveSheet
Set myRng = .Range("b3:c4")
Set myPict = .Pictures.Insert("c:\my documents\my pictures\badday.jpg")

End With
With myPict
.Top = myRng.Top
.Left = myRng.Left
.Width = myRng.Width
.Height = myRng.Height
.Name = "Pict_" & .TopLeftCell.Address(0, 0)
.OnAction = "'" & ThisWorkbook.Name & "'!testme03"
End With

End Sub
Sub testme03()
dim myPict as picture
MsgBox "you clicked: " & Application.Caller
set mypict = activesheet.pictures(application.caller)
msgbox mypict.name
End Sub

Greetings,

I'm basically using a plug in that creates a menu structure, and then
going into Excel and VB, creates a menu dynamically. The menu consists
of pictures, text boxes, and lines (looking like a hierarchy tree, for
example). Now, for simplicity sake, let's just say pictures, since
that's where the problem arised.

Basically, I use the command

Code:
--------------------

ActiveSheet.Pictures.Insert( [picture location here] ).Select
--------------------

to insert my picture. From there, it renames the picture to what I
want so when the macro is assigned, it can figure out which picture the
user clicked on so it can take the appropriate action.

When the picture is clicked, the VBA figures out what it needs to do,
and usually it destroys the menu it created and makes a new one. The
problem arose after extensive testing when, all of a sudden, the macro
could no longer select the file. It would insert the picture, but
couldn't select it using the same code that had worked before.

Figured out that it has something to do with the names automatically
assigned by Excel when a picture is inserted. The latest picture it
created was named "Picture 65536," and I'm sure you realize the
significance of that number. (2^16) Of course, I'd like to rename
this picture, but I need to get past this automatically assigned name
that Excel gives to it. Is there a way to reset this number or set it
to a certain amount?

I'm almost certain the number is stored somewhere on the sheet itself.
If I take this code to another sheet the "counter" is reset, and I've
done that as a temporary measure so I can continue working with and
testing the menu system. Also, even after a complete reboot of the
machine, I notice the auto-assigned number in the object name is at the
same level it was before. So I'm thinking I just need a way to access
that counter embedded in the sheet... any ideas?

 

[Frankenroc]

Never tried that method before, but it works! (I even tested by
"overloading" the counter up to 65536.) Thanks!

 

[Frankenroc]

Okay, this is kinda similar to the first...

How would I do the same with a label? I tried integrating the syntax
used for the picture into a line, but it didn't work as well, and I can
definately see this problem happening with that as well, since it used
the same structure as the picture. Here's what I tried for the
line...


Code:
--------------------
Dim myLabel As Label

With ActiveSheet
Set myLabel = .Shapes.AddLabel(msoTextOrientationHorizontal, NumericX, NumericY, 0#, 0#)
End With

With myLabel
.name = "Text " & Counter
End With

 

[Dave Peterson]

Try recording a macro when you add a label from the Forms toolbar to the
worksheet. You'll get something like:

ActiveSheet.Labels.Add(295.5, 82.5, 236.25, 21).Select

So I just changed it to:
Set myLabel = .Labels.Add(0, 0, 0, 0)

And I'd use that other code to move it and name it.

 

[Frankenroc]

Actually I had already tried that. ActiveSheet.Shapes.AddLabel is the
the code the macro recorded. But I did try With ActiveSheet:
Labels.Add as your code implied, and that worked, so thanks again!

 

[Dave Peterson]

All I did was record a new macro to get that code. I'm not sure why you and I
got different results.

But glad you got it working.