VBA, Auto create linked pictures of cells to a floorplan

  • What I am doing.

    I have Excel spreadsheets that have two sheets. One is a table of information pertaining to light fixtures (I am using light fixtures as an example), where one of the columns is the fixture number (i.e. 123, 124, 125, ect. ect. ect.). The other sheet has an image of a building floorplan with those fixture-number cells from the first sheet, copied over as linked images. Essentially it's a floorplan of symbols, where the symbols are just linked images of the fixture-number cells from the first sheet, instead of me having to manually click and drag everything around the floorplan all over again, just because some numbers changed. If mods to the facility occur, then the numbers inside those symbols change automatically as I make quick changes in the first sheet, and maybe I have to add new linked images or remove linked images manually as changes happen. But it's fast. Not a lot of clicking since I don't have to reorganize the symbols. Then I just copy the Excel table and spreadsheet as linked content into a Word file to pretty it up as a document. Pretty dang simple.

    The complexity.

    When creating a new spreadsheet for new large buildings, I wanted to autogenerate those linked images of cells so I wouldn't have to go back and forth creating one by one. First for cell A123, then A124, then A125. I created a Macro that went and created them for me all at once, and saved a long the way (because I was running into Excel Range Errors and Excel would freeze and have to force quit otherwise). All I had to do was initially move all these symbols around the first time after they were automatically created. I'll copy that code below.

    What I want to do // What I need help with // The next step.

    I want to modify that code so that for each new linked image that automatically get's generated, I can just click on the image of the floorplan in the second sheet, and it will paste that cell as a linked imaged, damn close to where I intended. Then the script goes to create the next linked cell and I just click in a new spot and it pastes the next one, and I just keep going to the end. Any ideas? I am aware that I can click on different cells in a spreadsheet to paste that linked image in separate places on the sheet, but I need something a little different than that. Maybe there's a way to put the floorplan image BEHIND the cells, so that I am actually clicking on the cells. That way, I can realign the cell dimensions into a small grid and effectively be clicking on cells to paste into as I look at the floorplan. Then I could just bring the floorplan up a level, in front of the cells at the end. The whole send-to-back and send-to-front feature. Any ideas?

    I have pasted the code below.

    The list of fixtures is on the sheet, LIGHT FIXTURES SHEET

    The image of the floorplan is on the sheet, FLOORPLAN SHEET
    In this example, I am copying cells L133 to L136. These cells contain my fixture numbers in this case.
    F33 is the cell that all of the linked images get generated into. They appear on top of each other. This Code does them in reverse so that the fixture number at the top most of those stacked images is the lowest number. So let's say the numbers 4, 5, 6, and 7 where my fixture numbers, stored in cells L133 to L136, in order. This code will make sure that when it is complete, the image for number 4 is on top for me to drag to the location I need it at. I just don't want to have to drag them anymore. Autosaves every 5 times, and then again at the end.

    Sub CreateLinkedPictures()


    ' CreateLinkedPictures Macro



    Dim i As Long

    Dim t As Integer

    Dim rng As Range

    Set rng = Range("L133:L136")

    t = 1

    For i = rng.Cells.Count To 1 Step -1

    Sheets("LIGHT FIXTURES SHEET").Select



    Sheets("FLOORPLAN SHEET").Select



    If t Mod 5 = 0 Then ActiveWorkbook.Save

    t = t + 1

    Next i


    End Sub

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!