Displaying an Excel Drawing Based on a Pull Down

[zrwolf]

Hello All,

I have been given an interesting task to explore. The construction company I work uses Excel drawing tools to create spec drawings. As it turns out, most of the drawings that we create are similar to ones that have been done in the past.

I am trying to come up with a way that a library of excel drawings could be utilized. My ideal situation would have a pulldown that would paste a drawing based on the selection.

Experimenting, I have gotten as far as having a drawing paste on the sheet with the pulldown, but I can't seem to control where the pasting occurs.

Any great ideas?

 

[Damon Ostrander]

Hi zrwolf,

I assume that each drawing ends up being a grouped shape object. As a result all you need to add to your code to control where the object is pasted is to set its location right after it is pasted by setting its Left and Top properties. For example:

With Activesheet.Shapes("Group 3")
.Left = 100
.Top = 200
End With

positions the top left corner of Group 3 at screen coordinates (100,200).

Damon

 

[zrwolf]

Thanks Damon. That was a big step in the right direction for me.

My followup question is how do I reference the copied object at a later point (after it is no longer selected)? When you copy an object Excel gives the new pasted object a new name. How would I reference this new object later, if say I wanted to delete it or move it again?

 

[Damon Ostrander]

Hi again zrwolf,

There are several ways to refer to the later after it is no longer selected. If you only need to reference it later and the workbook is still open, you can either reference it by name or save an object variable reference to it. For example, to save its name:

Dim Gname As String

Sub PasteDrawing()

' code here that pastes drawing onto active worksheet

' drawing (shape) should now be selected

' this code positions the drawing on the sheet and saves the name
With Selection
.Left = 100
.Top = 200
Gname = .Name
End With

End Sub

Note that the Dim occurs outside the Sub so that Gname is global and therefore still exists even after the PasteDrawing procedure exits. It can be referenced in another Sub at a later time.

Similarly, to save it as an object:

Dim Gshape As Shape

Sub PasteDrawing()

' code here that pastes drawing onto active worksheet

' drawing (shape) should now be selected

Set Gshape = Selection

' this code positions the drawing on the sheet and saves the name

Gshape.Left = 100
Gshape.Top = 200

End Sub


If you want to access the drawing at a much later time (i.e., after closing and re-opening the workbook) you can do this by either putting the name of the drawing in a non-volatile location, such as a worksheet cell, or by giving the drawing a name (in your code by changing the Name property) adhering to some convention, such as "Drawing1043", where the latest drawing will be the one with the highest number (1043) and your code could look for the drawing with the hightest number when it wants to access the latest drawing.

I hope this helps.

Damon

 

[zrwolf]

I've been able to invest some time on this in between a few other more pressing projects. With your advice along with much experimenting it is really looking good.

However...

I'm still having trouble getting my code to delete an old object. I've gone ahead with assigning the name of the grouped object to a cell (AX28). However my code balks and says it cannot find the object even though I can verify that an object witht the same name as AX28 exists on the page. Here is my code:

Function DeleteCurrentObject()
Dim wsCurrent As Worksheet
Set wsCurrent = ActiveWorkbook.ActiveSheet
Dim Elevation As String
Elevation = wsCurrent.Cells(28, 50).Value
wsCurrent.Shapes(" & Elevation & ").Select
Selection.Delete
End Function

I've tried a few variations but nothing seems to work. Any suggestions? Thanks as always!

 

[Damon Ostrander]

Hi zrwolf,

Yes, I can see the problem--or problems. One issue is that when you use a numeric value in a cell (I assume the content of Cells(28, 50) is numeric) as the name of an object, you are unlikely to get a match. This is because even though the value in the cell might display as "12", the underlying value could easily be 12.000000000001 or even 11.99999999999999, and unless you make provision to convert the value to a string with no decimals or with the appropriate number of decimals, you will not get a match. For example

Elevation = FORMAT(wsCurrent.Cells(28, 50).Value,"00")

will round the value to the nearest integer and display with two digits. On the other hand if you want Elevation set exactly to the text DISPLAYED in the cell, you can use

Elevation = wsCurrent.Cells(28, 50).Text

but then you are vulerable to missing the match if the user changes the number of digits to which the number is displayed in the cell. If you are actually using a non-numerical text string in AX28, disregard all of the above.

Secondly, if your Shape is named exactly as the text in the cell, then you should be able to do just:

wsCurrent.Shapes(Elevation).Select

since Elevation is a String variable.

Finally, and this is just for efficiency sake, it is not necessary to select the shape in order to delete it. You could simply do:

wsCurrent.Shapes(Elevation).Delete

Also, incidentally, Range("AX28") and [AX28] are interchangable with Cells(28, 50) if you prefer using the A-1 style notation.

I hope this helps.

Damon

 

[zrwolf]

Works perfectly!

The problem was, as you suggested, that I was calling:

wsCurrent.Shapes(" & Elevation & ").Select

When what I needed was:

wsCurrent.Shapes(Elevation).Select

Sometimes the simplest things drive me insane!! Thanks again!

 

[Damon Ostrander]

De nada.



Damon