Announcement

Collapse
No announcement yet.

UserForm - Run Time Error 75

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • UserForm - Run Time Error 75



    Dear All,

    Please can somebody throw some light on a problem i am having? I've been trying to solve it now for the last day and its driving me bonkers!!

    Basically i have added a simple userform to a project, which contains nothing more than a ComboBox. The comboBox source is a range in the Excel spreadsheet.

    Whilst in the VBE, everything runs fine. However, when i save the spreadsheet, Excel invariably crashes with the usual message "Excel encountered a problem etc, etc" or if it doesn't, as soon as i re-load the spreadsheet, I get the following string of error messages:-

    Path/File access error
    Run Time error 75
    Cannot find specified object,
    catastrophic failure,
    Object was unloaded

    Pressing debug shows the cursor sitting on the line "userform1.show"

    It sounds like for whatever reason, the userform stuff is not being saved but i have checked and checked that there is enough disk space and i have write access rights etc and all is well. Besides, the problem seems intermittent.

    This is the first time i have added a combobox to a userform so i'm guessing (hoping) that its something to do with this? Do i need to do anything "special" to initialise this i.e allocate memory when the user form loads?

    I am appreciate any help or guidance given.

    Many thanks



    Pete


    PS I have "cleaned" my project so i don't think its anything lurking deep in old code etc and tried it on another computer with the same result!

  • #2
    Re: UserForm Woes - Run Time Error 75

    I would imagine that the fault lies in the Initialisation or Activate routines of the UserForm. Have you any code there?
    Hope that Helps

    Roy

    New users should read the Forum Rules before posting

    For free Excel tools & articles visit my web site

    If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

    RoyUK's Web Site

    royUK's Database Form

    Where to paste code from the Forum

    About me.

    Comment


    • #3
      Re: UserForm - Run Time Error 75

      Hi,

      How did you 'clean' the project?

      Cheers
      Andy

      Comment


      • #4
        Re: UserForm - Run Time Error 75

        Roy, Andy

        To answer your questions:-

        1. There is no code(perhaps this is the problem?). The userform simply has a combobox on it.

        2. Using a freebie tool i found on the internet (code cleaner 1.4)

        Hope that helps?

        Regards,


        Pete

        Comment


        • #5
          Re: UserForm - Run Time Error 75

          Hi,

          Have you renamed the project, code module or control?

          Can you post you non working example?

          Cheers
          Andy

          Comment


          • #6
            Re: UserForm - Run Time Error 75

            Andy,

            I was afraid somebody would ask that!

            Unfortunately i can't really do that as its a fairly complex workbook with ODBC links to various datasources etc along with all sorts of "event" capuring code. Prior to adding the userform everything seemed to work OK that's what makes me think the userform is at fault. I've checked and checked the event procedures to see whether they are going round in circles but again they look fine?

            I was hoping that the run time error was a common problem, maybe due to an obvious oversight on my part, that would be easily resolved.

            I am grateful for any help or guidance you can give but wouldn't want you to waste too much of your time.

            sincere thanks


            pete

            Comment


            • #7
              Re: UserForm - Run Time Error 75

              Ok, start by isolating the problem.

              In a new workbook add a userform and combobox exactly as you have done in the broken one. Does this fail?

              Maybe you can export just the userform and code module that don't work and put those in a new workbook and see if it works.

              Cheers
              Andy

              Comment


              • #8
                Re: UserForm - Run Time Error 75

                Hi Pete,

                Check that none of your files have the read only attribute set, including all the OCX for the combo box.

                Regards

                Rich
                Regards

                Rich

                Comment


                • #9
                  Re: UserForm - Run Time Error 75

                  The plot thickens!

                  I've done my best to extract the userform to a new book and it appears to work OK.

                  In my original spreadsheet, i've commented out any reference to userform1 and it also appears to work.

                  When combined, it falls over!

                  I think i'll just have to put it down to "one of those things"

                  Thanks for all your help, much appreciated.

                  Pete

                  Comment


                  • #10


                    Re: UserForm - Run Time Error 75

                    Hi Andy,

                    Thank you for you kind offer but i think(fingers crossed!) that i may have sorted it out.

                    My gut feeling was that if it wasn't anything directly to do with the userform, it was probably something to do with my "event" macros - maybe firing recursively?

                    Anyhow, by lots and lots of trial and error, commenting out each procedure, it appears to be in the workbook_save() and sheet_activate() events. I had declared the later event at both the workbook (for all sheets) and worksheet ("Index" tab) level with different code in each-does this matter?

                    Furthermore, my workbook_save() event was selecting the "Index" tab to before saving the workbook. Even though i think i had selectively turned on/off events with application.enableevents = true/false etc, i think it was getting itself in a never-ending loop.

                    Even though i do not fully understand why, i have spend two days "be-sotted" with the problem and have learned quite a lot in the process, so not completely wasted time.

                    Thanks again for you help. If you have any comments re:above i would be delighted to hear them.

                    Pete

                    Comment

                    Working...
                    X