Results 1 to 7 of 7
  1. #1
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    UserForm Problem (XP)

    On a worksheet, there is a macro button that initializes and displays a UserForm (Setup) - No Problem.

    When I bring up the VBA editor to work on that userform, it presents a problem...
    In the VBA Editor left explorer pane, I can trickle down to the Form, right click it and view the code, again, wothout issue. But if I click on View Object, it results in an error message.

    I cannot get the actual form in the editor window to work with it. I've rebooted, restarted Exel, walked around the block....
    Any ideas?
    Attached Images Attached Images
    - Ricky

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: UserForm Problem (XP)

    Ricky,

    Have you any third party controls on the form, that may have become un-installed ?

    Have you tried to export the form and re-import it ?

    I would suspect that it is a sign of corruption creeping into the project, so you might be advised to rebuild the form. Copy the code if you can to a textfile and paste it back into a fresh form.

    Andrew C

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

    Re: UserForm Problem (XP)

    If you are using Excel 2000, this might be caused by a known bug regarding a loaded userform and the SaveCopyAs method. Any userform that is NOT unloaded (still in memory) whilst invoking the SaveCopyAs method will exhibit the behaviour you described.

    I'm sorry to say an older version of my Autosafe also may have caused this (The latest from the Excel MVP Page below works around the bug).

    All you can do either rebuild the form or find an older copy of your workbook, export that userform, remove the offending one from the current book and import the one you've exported.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: UserForm Problem (XP)

    <P ID="edit" class=small>(Edited by Tricky on 05-Feb-03 22:32. Added other attempts to salvage the form.)</P>Thanks Andrew and Jan Karel,

    It's just odd to me that from the spreadsheet macro button, the form works fine! Just can't get to the form through the VB-Editor to tinker with it.

    This is a new project and from within the editor, I had used the mouse to drag the form from another project. While still in the editor, I made a few changes to the form, saved my work and went back to the sheet to try it out. All was good so I left it alone for a few weeks. Now, I can't get back to the form to continue working with it....

    I do still have the original (without the latest modifications. Perhaps dragging it was not the best way? I could just build the form from scratch but it would save lots of time if I could begin with a form from another project that already has 90% of what I like. Is there a correct way to move the form from one project to another?

    By the way, I didn't save the file while the form was open. In fact, the entire file menu and toolbars are disabled until the form is closed.

    I tried to drag the form to another sheet AND I tried to export the form. Both trys resulted in the same error message shown in my previous post.
    - Ricky

  5. #5
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: UserForm Problem (XP)

    Hi Ricky,

    I have had several problems as you describe, multipage controls which have numerous textboxes (30-40) per page, some complicated gyrations on textbox events, even a form that behaved badly when I removed one label. On each occasion, I thought that the error was somehow mine - baffled me for quite a long time and I had numerous "restore from backups" with certain books.

    Like you, I learned to always make a backup before I started tinkering. I remember getting largely out of sorts one evening after spending a couple of hours making changes to a book and not making a backup every so often - after which time my book was toast (I think I had to get a new printer the next day due to my wife vacuuming some of the parts off of the floor).

    Although I am not familiar with XP, what seemed to work for me most of the time in 2000 was to export and delete the form, close the workbook, open the workbook, import the form again.

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

    Re: UserForm Problem (XP)

    Hi Tricky,

    <hr>By the way, I didn't save the file while the form was open<hr>

    That is not what I said. A form has two properties (states) that are important here:

    - Loaded
    - visible

    A form must first be loaded into memory before it can be shown. However, when you use the Show method, Excel automatically loads the form first (thereby executing all initialisation code). Then the form is shown. So if you like, you can load the form without showing it at first, then do some processing and lastly show the form. A form is also loaded when it is referred to in code, e.g. when you fill a listbox.

    If the form is closed by the user, the closing code often contains something like
    Me.Hide

    This in fact only hides the form, but leaves it in memory!

    It is that status I was referring to that will cripple the form when you do a SaveCopyAs from VBA. I've even heard that saving multiple times might cause this to happen.

    To avoid it use
    Unload Me
    when the form needs te be closed.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

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

    Re: UserForm Problem (XP)

    I do that too, by using Rob Bovey's code cleaner from the Excel MVP page below my sig. But even that one fails on XL2000 if a form is still loaded. My Method:

    - save and close the workbook
    - open with macros disabled
    - clean the code
    - reopen with enable macros if needed.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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