Results 1 to 3 of 3
  1. #1
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Buttons on sheets not keep names (xl97 (SR-2))

    I have an app that needs to work the same in xl97 and xl2000. I have found many cases where VBA is not the same in these versions but so far I've been able to find workarounds. I have just now found yet another inconsistency which I need advise on how to handle.

    All my worksheets have ActiveX controls, mostly buttons. When these sheets are copied (either manually or through VBA) the names on the buttons are not retained. For example, button = "btnGoMain" gets renamed to its default name CommandButton1. I have worked around this in xl2000 by not renaming the buttons at all (use their original names). PROBLEM: This does not work in xl97 as it seems to randomly rename the buttons. Example: On one sheet I have CommandButton1 through CommandButton4. When the sheet is copied, what was CommandButton3 is now CommandButton4. The reason this is a big deal is that the code that sits behind the buttons now gets launched on the wrong event (user pushes CommandButton3 and the code for CommandButton4 executes).

    Can I rename a button at run-time? How about reading the button's caption and use that to figure out what button to fire? I'd have to dynamically write the VBA code that goes with the button since I can't rely on the button having the same name.

    This is harder to describe in writing I know. Any ideas for fixes?

    Frustrating with undocument VBA changes - Deb <img src=/S/yikes.gif border=0 alt=yikes width=15 height=15>

  2. #2
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Buttons on sheets not keep names (xl97 (SR-2))

    WOW, I guess I'm stumped everyone on the list as I've had no replies at all. This is an honor <img src=/S/bow.gif border=0 alt=bow width=15 height=15>. Do I win a prize? *ha*

    My Excel project (about 3.2MB) is 90% VBA and I know I'm stretching the capabilities of the language having to make it work the same in xl97 and xl2000 plus tons of forms and worksheets and modules and classes. I think I use almost all the object model. The undocument differences in VBA between these two revs has added at least 3 months to my project timeline. I talked to MS support and they hadn't heard of this problem. I haven't checked the KB though (I always have problems finding stuff there...)

    I guess I'll lock myself in a cave somewhere and wrack the ol' brain to come up with some wickedly twisted code to get myself out of this mess.

    Deb <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>

  3. #3
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Buttons on sheets not keep names (xl97 (SR-2))

    Quite interesting. But I'm not sure this is of help.

    What I found was that the name property of controls as set by the right click, properties did not match the name which VBA gave me in the OLEObjects collection- whereas the name DID match in XL2000.

    eg, Button 1 named "ABC", Button 2 named "XYZ" via the properties. However, This code:
    <pre>Dim ctl As OLEObject
    For Each ctl In ActiveSheet.OLEObjects
    Debug.Print ctl.Name
    Next</pre>

    showed the names as CommandButton1 & CommandButton2. I could change the name property in this code.

    BUT, when I copied the sheet, the names in the properties window were the same in the new sheet- even though, if I had changed the OLEObject name, that named reverted to "CommandButton1".

    In Excel2000, the names matched. I could copy OK without the name changing.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

Posting Permissions

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