Results 1 to 11 of 11
  1. #1
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    VBA 'Select Object' Mode (XL2K)

    I noticed that if I have the Select Object button on the drawing toolbar depressed, then I cannot enter any data into my worksheet. Normally, this is an annoying feature, but I have a worksheet now in which there are times that I only want to allow the user to examine the data and press a command button on the worksheet. It would be nice to be able to turn-on/off this "Select Object" mode in VBA, but I cannot seem to find anything. However, I did find Application.DataEntryMode which is similar. Anyone know how to depress the Select Object button in VBA? TIA --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  2. #2
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: VBA 'Select Object' Mode (XL2K)

    Never mind. Obvious:
    <pre>Option Explicit

    Public Sub selectObjectsOnly()
    With CommandBars("Drawing").Controls("Select Objects")
    If .State = msoButtonUp Then .Execute
    End With
    End Sub

    Public Sub selectNormal()
    With CommandBars("Drawing").Controls("Select Objects")
    If .State = msoButtonDown Then .Execute
    End With
    End Sub
    </pre>

    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  3. #3
    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: VBA 'Select Object' Mode (XL2K)

    Great idea. <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35> I went and added this to my bag of good code. I then added it to two of my current projects and one of them failed at compile time. I did checking and saw that the msoButtonDown requires a reference to the MS Office 10 Object Library. Only one of my projects had a reference to that object.

    Where can I find the value for msoButtonDown so I can hard-code it? I can't guarantee that users have Office 10 since I have to work with xl97, xl2000 and xl2002.

    Thnx, Deb

  4. #4
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: VBA 'Select Object' Mode (XL2K)

    > can't guarantee that users have Office 10
    No need, it's all done with smoke and mirrors. Just set a reference to Office 8, 9, or 10, whichever you have at that time. Then if a user opens it with a different version of Excel, it uses the version that it has. Just tested this to make sure: I was on Win XP/XL2K and the ref was for Office 9. Rebooted into Win NT/XL 97 (I have a dual boot PC <img src=/S/grin.gif border=0 alt=grin width=15 height=15>), opened the same file, code works and reference is to Office 8. So, bottom line, just add a reference to Office x and "nothing can go wrong...." --Sam <img src=/S/wink.gif border=0 alt=wink width=15 height=15>

    PS, don't know if you care about international issues, but I think that code will only work for the English version Excel. It really should use a Toolbar ID and a Button Face ID, instead of the English words. If you figure out these numbers, post back the code or maybe HansV or Jan Karel will supply us with the numbers. One of them probably has a cross-reference table.
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  5. #5
    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: VBA 'Select Object' Mode (XL2K)

    Hmmm, well I can't get your results on my system. One project had references to both MS Office 9 and 10 and the other project had a no reference to any MS Office library (I misspoke before). it was this latter project that failed with the error that it didn't know msoButtonDown. It was only after I added reference to MS Office 10 to that one project that it worked. I'm was running Excel 2000 on Win2k.

    >> References in failing workbook (get 'variable not defined' at msoButtonDown)
    VBA
    MS Excel 9.0
    OLE Automation
    MS Forms 2.0

    To ths same workbook, I then added a reference to MS Office 10 and it compiled just fine, no errors.

    I guess I just need to add a reference to ANY MS Office library even though the failing workbook didn't have one (and doesn't require it for the code I'm doing so far).

    I'm was just concerned about versions that if I require MS 10 in my system and then give the .xls file to someone else w/o this version it'd fail but the internals are smart enough to use whatever MS Office x Object Library is present.

    Thnx for the insight. <img src=/S/invisible.gif border=0 alt=invisible width=15 height=15> <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>
    Deb

  6. #6
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: VBA 'Select Object' Mode (XL2K)

    > One project had references to both MS Office 9 and 10
    Dangerous! Who's on first? Do you have both Office2K & OfficeXP installed on the same machine? I've found that whenever I tried that, I got hopelessly confused. I would reinstall XP and let it clean house; ie, get rid of previous object libraries.

    > I'm was running Excel 2000 on Win2k.
    but, then you should only have Excel 9, Office 9, etc. libraries.

    I'm very <img src=/S/confused.gif border=0 alt=confused width=15 height=20> --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  7. #7
    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: VBA 'Select Object' Mode (XL2K)

    Let me un <img src=/S/confused.gif border=0 alt=confused width=15 height=20> you. Yes I do have Excel 97 and Excel 2000 on the same PC happily co-existing. I am about to also install Excel XP as well since I have to test against all these versions (a major pain the <img src=/S/moon.gif border=0 alt=moon width=15 height=15> in trying to write one set of code that works with all). I currently am mostly using Excel 2000, however.

    So you recommend when I install XP to uninstall Excel 97 and Excel2000 first and then re-install them? Or better yet put these on another PC for testing purpses (but then file sharing isn't as convenient - am I whining yet? <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15> )

    I'm now happily using the code to disable

    Thnx, Deb <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA 'Select Object' Mode (XL2K)

    Since you state you're developing for 97, 2000 and XP, one small advice:

    Do your development in 97. Test in later versions. XP (and 2000) has new objects and methods added to the object model, which may cause runtime errors and need a workaround (or are not even possible!) in the earlier versions.

    For instance:
    1. There is an InstrRev function in 2000 (and XP), that does not exist yet in 97. To make code work in 97 that needs that function, you would need to write your own function that does what InstrRev does for 2000 and XP.
    2. In 2000 and XP, showing a modeless userfrom is simple: use the vbModeless argument to the show method. But 97 has no arguments to the show method and some intricate API calls are needed to show a modeless userform.
    3. and many more examples...
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  9. #9
    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: VBA 'Select Object' Mode (XL2K)

    Yes I agree totally. I never use any of these "new" functions that don't exist in Excel 97 (I even wrote my own Split that I use everywhere). The problems I see are those undocumented ones where the same function or set of code works in one rev of Excel but not another. It's THOSE that make me <img src=/w3timages/censored.gif alt=censored border=0> <img src=/S/bash.gif border=0 alt=bash width=35 height=39>. I had tons of problems using the Chart object as some of the very same funcs worked differently (some things only worked if the chart was active or visible which didn't matter to Excel 2000, etc., etc., etc.)

    Example: (works in xl2000, fails in xl97)
    <pre>Dim X as variant
    If X <= 0 Or X = vbNullString Then
    ' do something
    End If</pre>


    Deb <img src=/S/crazy.gif border=0 alt=crazy width=15 height=15>

  10. #10
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: VBA 'Select Object' Mode (XL2K)

    <hr>
    I do have Excel 97 and Excel 2000 on the same PC happily co-existing. I am about to also install Excel XP as well since I have to test against all these versions
    <hr>
    In my opinion and experience, you are not really testing if your code will run on any of the three because you have all of the object libraries on your system whereas your users only have one. In order to better test, but not be a pain, I would have two disks with the larger and newer of the two divided into two partitions. Then I would install an operating system on each of these three partitions and be able to boot any of the three. Finally I would install just one of the three Office 97, 2K & XP on each of these new systems. If your users have different operating systems, then you may want to have different OS's on the three systems. If you do this, be sure to install the older operating systems first.

    On a different subject-compatability, don't know if any of your code does a GetOpenFilename with a multi-select, but in XL97 & XL2K, the list comes back in the order selected, however, in XL2002, the list comes back in an undecipherable order (at least to me). This is such a bad feature for me that I am downgrading the few Office XP systems that we have. --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  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: VBA 'Select Object' Mode (XL2K)

    (Edited by HansV to update link to Excel MVP site)

    Jan Karel already has; xlMenuFunDict
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

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