Results 1 to 13 of 13
  1. #1
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Changing focus (Excel 97)

    I have a toolbar that inserts audit symbols. A sample insertion instruction (these are attached to toolbar icons) is:
    ActiveSheet.Pictures.Insert("C:Program FilesMicrosoft OfficeOfficeXLSTARTSymbolsbullseye.bmp").Select
    That leaves the bmp file as the "selected" object on the page-it has those boxes at the corners, in the language of barbarians like myself.
    I have been trying to figure out how to write an instruction to "de-select" that image so I don't have to click off it. You'd think this would be simple, after reading the Microsoft Programming book 3 times. You'd think I'd know; I think I'd know. But I don't and I am annoyed about that.
    I've been looking in the Object browser without success. Those "pop-up" suggestion tip boxes are never there when you need them. So I would appreciate some guidance. I wonder if I'll ever get that "spark" of understanding that pulls everything together!

  2. #2
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: Changing focus (Excel 97)

    Does Selection.Collapse do what you need?

    StuartR

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Changing focus (Excel 97)

    Just leave out the .Select at the end - that's what leaves the picture selected.

    (Stuart: Selection.Collapse is Word VBA, not Excel VBA)

  4. #4
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: Changing focus (Excel 97)

    Hans,

    Thanks for the response, sorry for the misunderstanding. Poor reading, must get new glasses. Maybe just stop filling them with beer.

    StuartR

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Changing focus (Excel 97)

    Stuart,

    On the contrary, fill them up again - see <post#=257213>post 257213</post#>

    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16> Hans

  6. #6
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing focus (Excel 97)

    Thanks. Works fine now. As a followup, what, if anything places the image relative to the background? That is, is the image located relative to the cell behind it? Relative to the upper left corner of the whole sheet? I'm not sure. So, instead of having .select, would one assign a property to have the image to the left, center or right hand side of a cell? A tweak idea I have. Thanks again. <img src=/S/salute.gif border=0 alt=salute width=15 height=20>

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Changing focus (Excel 97)

    The top left corner of the picture is the top left corner of the active cell. If you want to move the picture to another location, set its Top and Left properties, either absolutely (in points, relative to the top left corner of cell A1), or relative to some cell. For example:

    Dim pic As Picture
    Set pic = ActiveSheet.Pictures.Insert("C:Test.bmp")
    With pic.ShapeRange
    .Top = Range("P12").Top
    .Left = Range("P12").Left
    End With

  8. #8
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Changing focus (Excel 97)

    Convenient thread! I have been trying to do some of this myself lately, and Shapes are not well covered in the books I have. The code I have so far may help you with some of the properties you are wondering about. The relevant part of the code is:

    Dim shP As Shape
    Set shP = Worksheets("Name").Pictures.Paste
    With shP.ShapeRange
    .Name = "Name"
    .Left = 360
    .Top = 360
    .TextFrame.Characters.Text = "Name"
    .ZOrder = msoBringToFront
    End With

    but I'm looking for improvements.
    -John ... I float in liquid gardens
    UTC -7ąDS

  9. #9
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Changing focus (Excel 97)

    Hans, what is the difference between a Picture and a Shape?
    -John ... I float in liquid gardens
    UTC -7ąDS

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Changing focus (Excel 97)

    Hi John,

    Frankly, I don't know. When I record the inserting of a picture, ActiveSheet.Pictures.Add is used. This seems to have the same effect as ActiveSheet.Shapes.AddPicture.

    The Picture object and Pictures collection are not exposed in the Object Browser (in Excel 2002), and if I type Picture or Pictures and press F1, I get a help page stating only that this is a hidden language element.

    But thanks for your replies - the AddPicture method allows you to specify the position and size of the inserted picture, which is useful for Torquemada, who started this thread.

  11. #11
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Changing focus (Excel 97)

    From poking around in Excel 2000 Help:

    Objects that have been hidden in the Microsoft Excel 97 Visual Basic object model are listed in the following table. These objects are supported only for backward compatibility; for new code, you should use the replacement functionality provided in Microsoft Excel 97. To view hidden objects in the Object Browser, right-click in the Object Browser window and click Show Hidden Members on the shortcut menu. For more information about the changes to the Microsoft Excel 97 object model, see one of the following topics:
    <table border=1><td align=center>Hidden objects</td><td align=center>Replacement</td><td>Arc, Arcs, Drawing, DrawingObjects, Drawings, Label, Labels, Line, Lines, Oval, Ovals, Picture, Pictures, Rectangle, Rectangles</td><td>New Shapes drawing layer</td></table> (more follows ...)
    -John ... I float in liquid gardens
    UTC -7ąDS

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Changing focus (Excel 97)

    John,

    Thanks. I keep forgetting that it is possible to show hidden members. Apparently the macro recorder in Excel 2002 still generates code that was already obsolete in Excel 97 <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    The "approved" method is to use the Shape object and Shapes collection.

  13. #13
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing focus (Excel 97)

    "Shape" is something my doctor tells me I am out of.

    "Picture" is alleged to be worth 1000 words.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •