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

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 co开发者_运维知识库lors, 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!

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

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

I ran into a similar problem where I needed to hide a shape after it was click. My solution was to use SendKeys to escape the selection

SendKeys "{Esc}"