Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts

    Overlay shapes issues in Excel2007

    Hi

    In Excel 2003, I can add a button (Form Control) to a sheet.
    I can then add a text box to the sheet.
    I can then format this text box to have a solid white fill with no lines.
    I can then drag this text box to partially 'overlay' my button created earlier.
    I can then right-click to select the white text box, and then select the menu option Order to place this white text box either in front or behind my button.

    I can do exactly the same thing in Excel2010
    I can do exactly the same thing in Excel2013
    ...so why can't I do this in Excel2007????????

    In Excel2007, the form button is always in front.
    I cannot hide or cover the form button with my white textbox.
    Is it just me?????

    zeddy
    (same thing with Group Box - it's always in front in Excel2007)

  2. #2
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Two bets:

    1. No-one knows
    2. There's no solution

    If I'm wrong on either then I'll buy you a beer when I'm next visiting my sister in Newcastle.

    M

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts
    Hi Martin

    Thanks for upping the steaks on my query.
    I'm sure someone will have an answer.

    If anyone out there can confirm my issue with the Excel2007 version, I would be very grateful to hear.

    zeddy

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 649 Times in 592 Posts
    Are those steaks cooked medium or well done?

  5. #5
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Rare

  6. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 649 Times in 592 Posts
    Zeddy,

    What happens if you run these macros that I recorded?

    Code:
    Public Sub BringToFront()
        ActiveSheet.Shapes.Range(Array("TextBox 1")).Select
        Selection.ShapeRange.ZOrder msoBringToFront
    End Sub
    
    Public Sub SendToBack()
        ActiveSheet.Shapes.Range(Array("TextBox 1")).Select
        Selection.ShapeRange.ZOrder msoSendToBack
    End Sub

  7. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts
    Hi Maud

    Many thanks for the suggestion. I did try that already.
    But these macros have no effect with respect to the textbox shape and the Form button in Excel2007.
    The macros work with respect to other shapes, i.e you can put the textbox in front of another textbox etc.
    But the Form button remains always in front. In Excel2007 that is.

    So, you must have recorded that macro in another version of Excel??? The macro recorder in Excel2007 will not generate these statements. Well, not in my Excel2007 version.

    I have also tried this on my Win8.1 machine, and Excel2007 behaves the same.

    So I'm still looking for a method of 'hiding' Form buttons by placing a shape in front of them in Excel2007.
    And it would be nice if someone can confirm this behaviour in their copy of Excel2007.

    zeddy

  8. #8
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 649 Times in 592 Posts
    Yep Zeddy, you are right. I recorded these on 2010. Using code, wondering if it would make any difference sending the form button to the back instead of manipulating the text box?

    I am guessing that you do not want to hide the form button just by changing its visible property, such as:

    ActiveSheet.Shapes.Range(Array("Button 1")).Visible = msoFalse

    If you want to hide it behind another object, do all shapes present the same problem with a form button?


    And it would be nice if someone can confirm this behaviour in their copy of Excel2007
    But you are not alone:
    http://excel.questionfor.info/q_micr...el_495226.html
    Last edited by Maudibe; 2014-02-08 at 09:56.

  9. The Following User Says Thank You to Maudibe For This Useful Post:

    zeddy (2014-02-09)

  10. #9
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts
    Hi Maud

    Can't send any Form button behind a shape in Excel2007.

    Yes, I was trying to avoid having to change the visible property of the Form Button.
    The reason is, there are a few of them in the real application, and they would each have to be 'turned Off'.
    A quicker way would be to place a 'shutter' pane over all the buttons I wanted hidden.
    Then it's only one shape to hide and unhide as required.

    But in the end, with the Excel2007 version, it's got to be each Form button separately. Too bad.

    But many thanks for the link. Interesting.

    On my Win8.1 machine, I double-checked that my Excel2007 version was up to date.
    Selecting the Resources option from Excel Options, the first item in the list is get updates, with a button alongside [Check for Updates].
    ..but when you click this, you get a message back from Microsoft:
    "Thanks for your interest in getting updates from us.
    Sorry, this website isn't designed to work with your operating system"

    ..that says a lot eh!

    zeddy

  11. #10
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    I am wondering if there's a Collection for Form Buttons, which would make your task easier ?

    I seem to remember there being one called Buttons but can't find any references to it. There's Controls in VB, but not - I think - in VBA.

    However I did turn up this, from teachexcel.com, which suggests a way out of your impasse:

    Code:
    Private Sub Visibility()
    
       Dim CommandButton() As Variant
       CommandButton = Array("CommandButton1", "CommandButton2", "CommandButton3", "CommandButton4")
        For i = 1 To 4
          CommandButton(i).Visible = True
        Next i
           
    End Sub
    Or . . . something based on this, which looks simpler (I like simple !):

    Code:
    For Each objButton In Wks.OLEObjects
              objButton.Visible = False [or True]
    Next objButton
    Last edited by MartinM; 2014-02-09 at 14:32.

  12. #11
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 649 Times in 592 Posts
    Zeddy,

    I had one trial session left of Excel 2007 on an old laptop. I was able duplicate your issue by rearranging the z-order of form buttons with respect to other form buttons but not in relationship to any other shapes. Even using the selection pane, form buttons were separated from other shapes and could only be rearranged within their group.

    Seems as though this was either by design or a Microsoft flaw that has not been addressed.

    Sorry I could not be of any help here.
    Maud

  13. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I suspect it was a "flaw", but it was addressed as XL2010 no longer seems to have the issue...

    I do not have (nore ever used) XL2007 (our company went from XL2002 to XL2010), so I can not test. But, if you arrange them "as desired" using XL2010 does the arrangement "revert" when opened in XL2007 or does the order stay as it was set in XL2010?

    Steve

  14. #13
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts
    Hi Steve

    I don't think it is a 'flaw', since the XL2010 behaviour is same as XL2003 behaviour (as mentioned in my first post).
    And if, as you suggest, you put a 'white text box' in front of a Form button using Excel2010, when you then open the file in Excel2007 the Form button is shown in front (whether you like it or not).
    Seems like Microsoft thought of doing something different in Excel2007, then changed their mind and went back to the old way in Excel2010 (and later).

    Martin: your first code suggestion is good for turning a specific group of buttons on or off, your second code selection turns them all on or off. In my case, some buttons need to remain visible always. But I like the way you're thinking!

    zeddy

  15. #14
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts
    Hi Steve

    me again. What I meant to say, if Microsoft 'accidentally' mucked it up in Excel2007, I thought they would have fixed it via one of the three Service Pack updates that were done for Excel2007. So that is why I thought they deliberately left it in. Then changed their minds again for Excel2010, going back to the way it worked in Excel2003.

    zeddy

  16. #15
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    So you take the "Form button behind white box" in XL2010 and open it in XL2007 and get "Form button in front of white box". If no changes are made and NOT saved in XL2007, and reopened in XL2010, which is in front then? If saved in XL2007 and then opened in XL2010?


    Not sure of the "reality", but what I imagine:
    It sounds like Microsoft redid the "design space" of the various objects in XL2007 with each type being on different "sheet" and while the objects could move back to front on those sheets, the sheets themselves were non-intersecting. The non-intersecting of the various sheets seems to be the "flaw" and I doubt it could have been fixed with a service pack.

    In XL2003 (and previous) and XL2010 they either have all the objects on the same sheets or the "design-space" of those shape-sheets intersect allowing any type to go in front or behind.

    Really going out on a speculative limb, the XL2003 and previous may have had one-design space for all shapes, and with XL2007 they redesigned (for whatever the reason) to have the muliple design sheets (one for each shape type). These did not interect. Then in XL2010 the "flaw" was fixed and they may still be on separate sheets but the scope of these sheets allow them to overlap with other sheets...

    The fact that the when levels are "set" in XL2010 they are changed in XL2007 suggests to me a design difference between XL2007 and XL2010 in how they are handled and I call it a "flaw" when the "results" with the newer XL2010 match the results from preXL2007 versions instead of using the change developed in XL2007. Now whether it was a unexpected flaw in the execution of the change or the change itself was a purposeful design change that was percieved as a "flaw" later, to me it is still a flaw.

    I don't think that all "flaws" can be fixed with a service pack. Sometimes it must come with a larger change (like from XL2007 to XL2010), but I don't know, I am just trying to work up a "model" in my mind that explains the facts.

    Steve

Page 1 of 2 12 LastLast

Posting Permissions

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