Macro using Name box to select picture

L

Legacy 68581

Guest
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.

Code:
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:
Code:
   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
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
L

Legacy 98055

Guest
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. :)
 
L

Legacy 68581

Guest
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
 
L

Legacy 98055

Guest
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="filter:progid: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>
 
L

Legacy 68581

Guest
thanks

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

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,127
Messages
5,857,525
Members
431,883
Latest member
Hien

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
Top