Solved

Picture & VBA fun Fun FUn FUN :)

Posted on 2011-03-23
5
321 Views
Last Modified: 2012-06-27
Hello all,

I have a picture on a worksheet of a lego head labelled "lego_head".  I would like to select a range of cells and for all the cells within the chosen selection I would like to paste a copy of the lego head mentioned above.  I.e. If I select cells "B2:C4" I would expect to see 6 lego heads (one in each of the 6 cells).

Any help would be greatly appreciated.
0
Comment
Question by:Canders_12
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 39

Accepted Solution

by:
nutsch earned 400 total points
ID: 35201718
Select your lego head, then run this code.

Sub copyPictureToRange()
Dim rg As Range, img As Shape, cl As Range, img2 As Shape

Set rg = Range("B2:C4")
Set img = ActiveSheet.Shapes(Selection.Name)

For Each cl In rg.Cells
    
    img.Copy
    ActiveSheet.Paste
    Set img2 = ActiveSheet.Shapes(Selection.Name)
    img2.Top = cl.Top
    img2.Left = cl.Left
    img2.Height = cl.Height
    If img2.Width > cl.Width Then img2.Width = cl.Width
    
Next cl

End Sub

Open in new window


Thomas
0
 

Author Comment

by:Canders_12
ID: 35201965
nutsch:

Many thanks for the swift reply.  The only question I have is how easy is it to amend the code so teh range may be variable.  "B2:C4" was an example.  The user may choose "B2:D5".

I hope this makes sense? Many thanks.
0
 
LVL 43

Assisted Solution

by:Saqib Husain, Syed
Saqib Husain, Syed earned 100 total points
ID: 35202099
This is only to support Thomas's solution.

Thomas chose to fix the target range and let the user select the picture. You can reverse this by changing line 5 (picture name instead of selection.name) and line 4 (selection.address instead of the given range in quotes)

Saqib
0
 

Author Closing Comment

by:Canders_12
ID: 35202145
Many thanks to you both, greatly appreciated.
0
 
LVL 39

Expert Comment

by:nutsch
ID: 35202380
Glad to help. Thanks for jumping in, Saqib.

Thomas
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Filter an Excel list by multiple criteria 6 40
Copy and paste Excel Shapes using vba 6 23
Script/Formula to move data to another cell 6 22
Excel Formula 6 25
A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question