Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Feb 2002
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel VBA Userform (Excel 97)

    Aloha All,

    I'm teaching myself VBA via what I call pallor tricks in various MS Apps. My latest trick is in Excel. I was creating a user form which would update a checkbook register. I was working my way through a number of self inflicted challenges when I hit a brick wall. All of a sudden the user form quit responding to mouse clicks. It would "pop up" as designed with all the controls initialized but after that
    Attached Files Attached Files

  2. #2
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts

    Re: Excel VBA Userform (Excel 97)

    Hi Mike,

    One possible clue: I get the same behavior when I show the userform, which is that it doesn't respond to any user actions. When I then go to look at the code in the VBE, the title bar says "Bill.xls [ running] - [Calendar Form (code)] - and the Calendar Form, rather than the Checkbook form, is selected in the project window.

    Not sure what that means, but it seems like some kind of call or reference to the Calendar Form is working right.

    BTW, the code would not compile unless I commented out several lines - these were all erroring on a missing reference to something called "Channel OLE control module" - which is a file called CHANNELLib.TWD - any idea what that is?

    Gary

  3. #3
    New Lounger
    Join Date
    Feb 2002
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel VBA Userform (Excel 97)

    Gary,

    Thanks for the help... but unfortunately it only confuses me more. I did discover the calendar form worked by the other user form will not. I am really confused by the other reference you cited. Don

  4. #4
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts

    Re: Excel VBA Userform (Excel 97)

    Mike,

    Have tracked down your problem: go to the VB Editor and doubleclick on CheckForm in the Project window, so that the form appears and more importantly, its properties display in the Properties window (if by some chance you don't have the Properties window showing, show it by going to View > Properties Window).

    Look down the list of properties until you get to Enabled - this is set to False. - I'm guessing you were meaning to set some control to enabled = False and set it for the userform instead. Anyway, set this back to True and the userform will permit user input.

    With regard to the mystery reference, hard to figure how that got there if you've developed this whole project from scratch and don't recognize it - I would have guessed that someone else worked on the project as well and set that reference. For test purposes I've just removed that reference, but it you can compile the code on your machine without getting that missing reference error, then just leave it. What seemed to prompt the calls to that missing library were ordinary things like calls to the Format function - no idea what is going on with that!

    By the way, having you seen any of John Walkenbach's Excel Power Programming books? - they've got other fun projects along these lines.

    Gary

  5. #5
    New Lounger
    Join Date
    Feb 2002
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel VBA Userform (Excel 97)

    Gary,

    A Thousand and one THANK YOUs! <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

    I was "playing" with this property for one of the controls. Never donned on me that I set it for the form. I thought the issue was I put the form in an endless loop.

    On the mystery reference, I'm afraid it only gets stranger... I'm the only one playing with this and really don't have a clue!?

    Funny you should mention Jon Walkenbach's book. On recommendation from a co-worker I bought it . It is the reference I'm teaching myself with. Actually it is the aforementioned co-worker that got me interested in VBA. He sent a rather impressive spreadsheet and I just had to know how he did that!

    Again, thanks. Now I can finish playing with my little pallor trick!

    Mike
    <img src=/S/joy.gif border=0 alt=joy width=23 height=23>

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel VBA Userform (Excel 97)

    This ("FORMAT") is similar to something I reported in this post

  7. #7
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts

    Re: Excel VBA Userform (Excel 97)

    Hi Chris,

    Yes that does look like the same thing. Looks like it's turned up in Excel 97 (VB5) and Excel 2000 (VB6) as well as VB6.
    Liked the code Deb provided; also liked the discussion....

    Gary

Posting Permissions

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