MS Excel 2003 - Simple unselect question in Excel VBA when dealing with shapes

Question

So I have an excel workbook that has a global mapping scheme in it. So I have a shape for each and every country in excel. Depending on the region select, relative to the data/query, it will shade regions/countries in various ways.

So I know how to manipulate each shape in terms of colors, gradient shading, etc....

What I don't know how to do is "unselect" the shape at the end of the sub routine. My code looks like this (real simple)

sheet1.shapes("CountryName").select selection.shaperange.fill.solid selection.shaperange.fill.visible = true selection.shaperange.fill.forecolor.rgb=rgb(110,110,110) selection.shaperange.fill.onecolorgradiend msogradienthorizontal, 2, 0.45

ok so from one shape/country/region to another the "unselect" is not that big of a deal because focus jumps, but at the end????

i have guessed/tried a bunch of stuff but alas no luck

thanks!

Answer 1

Can you not simply record the original cell which was selected?

Dim oCell as Range
set oCell = activecell

'' Do stuff here

oCell.activate

Update: This bit of code records the current selection, then re-selects it after selecting range("A4"). Without knowing what kinds of shapes you have in your workbook, I can't verify that this will work, but it has so far with what I've tested.

Set mySel = Application.Selection
[A4].Select
mySel.Select

Answer 2

you have a typo in your source last line, ...gradiend --> ...gradient

selection.shaperange.fill.onecolorgradienT msogradienthorizontal, 2, 0.45

a very simple way of "de-selecting" the shape object would be to add this line of code

sheet1.[A1].select

this moves the focus to cell A1 in your sheet and hence away fro your object. Very rude, and I don't recommend it. I also don't recommend to "save the current selection" as proposed above as we don't know if the cursor is in a cell or at another (range) object.

The bettwer way is to avoid "selection" completely throughout your script. Asign your shape to an object and manipulate that object (note: I simulated in sheet3 with the first available object in my test), i.e.

Sub test()
Dim MyShape As Shape
    Set MyShape = Sheet3.Shapes(1) ' or whatever shape according to the user input
    With MyShape.Fill
        .Solid
        .Visible = True
        .ForeColor.RGB = RGB(110, 110, 110)
        .OneColorGradient msoGradientHorizontal, 2, 0.45
    End With
End Sub

Even better, if you are processing a list that gives you the name of a shape, do the following

Sub Test()
    '
    ' get the shape's name into ShapeName
    ' ...

    ColorShape Sheet3.Shapes(ShapeName)

    ' ...

End Sub

Sub ColorShape(MyShape As Shape)
    With MyShape.Fill
        .Solid
        .Visible = True
        .ForeColor.RGB = RGB(110, 110, 110)
        .OneColorGradient msoGradientHorizontal, 2, 0.45
    End With
End Sub

Hope this helps Good luck MikeD