Macro using Name box to select picture

[Legacy 68581]

Hello,

I am working on creating a map on which I will paste pictures in specific places.
The color of the pictures I paste will change according to the values I enter in my data sheet (to show to my coworkers which areas are doing better than others).

I have created a copy of the picture in each of the colors I will need, and named each picture (picturename1, picturename2 etc.).

I am trying to create a macro that will go to a cell that contains the picture name (here picturename1), copy that cell, go to the name box, paste the picture name, which will then select the picture, go to a cell I choose and paste it there.

Sub copypicture()
'
' copypicture Macro
' Macro recorded 29/09/2006 by matten
'

'
    Range("F4").Select
    Selection.Copy
    ActiveSheet.Shapes("picturename1").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("K2").Select
    ActiveSheet.Paste
End Sub

This works, but when I change the data in my data sheet and the information in cell F4 is automatically updated to 'picturename2', the macro keeps on pasting picturename1.

Could somebody please tell me how to replace:

   ActiveSheet.Shapes("picturename1").Select

by a code that would say 'go to name box and paste clipboard contents, enter'?

This seems so simple, yet after hours of browsing I still haven't found the answer... please help!

Thanks,

matten

 

[Legacy 98055]

ActiveSheet.Shapes(Range("F4").Text).Select
Selection.Copy
Range("K2").Select
ActiveSheet.Paste

Is this what you are after? With no replies, I think some people, including myself, are having a difficult time laying hold of exactly what the trouble is. If the above code snippet does not solve your problem, please reply with some more information.

 

[Legacy 68581]

using the name box in a macro

Hi Tom,

Many thanks for your reply. I was afraid my explanation wasn't very clear...
In short, I would like to know how to paste the clipboard contents to the name box, so that a picture will automatically be selected.
The macro would then go:

1 – go to F4 (which contains a picture name) and copy
2 – go to the name box, paste the clipboard contents and enter --> select the picture
3 – copy the picture
4 – go to K2 and paste the picture

I don’t know how to write code, the way I usually go about macros is to record my actions, and then make minor changes in the code. Here, when I record this, it replaces step 2 by selecting the picture, which doesn’t work out for me because the name in F4 will change, so that a different picture should be selected each time.

Please let me know if this is still not very clear.

Thank you,

matten

 

[Legacy 98055]

Ok. Your second post states the same thing as your first post. There is no need to paste anything to the name box to select a picture. If range F4 contains a valid picture name, then the code example I provided you with will work. Here is a more verbose example of the above.

<table width="100%" border="1" bgcolor="White" style="filterrogid:DXImageTransform.Microsoft.Gradient(endColorstr='#C0CFE2', startColorstr='#FFFFFF', gradientType='0');"><tr><TD><font size="2" face=Courier New>  <font color="#0000A0">Sub</font> copypicture()
       <font color="#0000A0">Dim</font> MyPicturesName <font color="#0000A0">As</font> <font color="#0000A0">String</font>

      <font color="#008000"> 'place the text in range F4 into the variable, "MyPicturesName"</font>
       MyPicturesName = Range("F4").Text
      <font color="#008000"> 'select the picture with the name stored in "MyPicturesName"</font>
       ActiveSheet.Shapes(MyPicturesName).Select

       Selection.Copy
       Range("K2").Select
       ActiveSheet.Paste

  <font color="#0000A0">End</font> <font color="#0000A0">Sub</font>
</FONT></td></tr></table><button onclick='document.all("102200622197671").value=document.all("102200622197671").value.replace(/<br \/>\s\s/g,"");document.all("102200622197671").value=document.all("102200622197671").value.replace(/<br \/>/g,"");window.clipboardData.setData("Text",document.all("102200622197671").value);'>Copy to Clipboard</BUTTON><textarea style="position:absolute;visibility:hidden" name="102200622197671" wrap="virtual">
Sub copypicture()
Dim MyPicturesName As String

'place the text in range F4 into the variable, "MyPicturesName"
MyPicturesName = Range("F4").Text
'select the picture with the name stored in "MyPicturesName"
ActiveSheet.Shapes(MyPicturesName).Select

Selection.Copy
Range("K2").Select
ActiveSheet.Paste

End Sub</textarea>

 

[Legacy 68581]

thanks

Hi Tom,
This does wonders!
Many, many thanks for your help.
nm