Run Time Error When Calling Userform

  • Hi folks,


    I am getting a run time error # 9 when I run a macro that calls a Userform or when I try to run code in a Userform module. The code performs beautifully on my computer, but it did not work on a coworker's computer. It ended up working on 3 out of the 5 computers I have tried it on.


    I have tried changing security settings to low, and a bunch of other stuff, but I cannot get the code to run on the computers that get the run time error on them when I try running the code on them.


    I get the run time error when I try to load or show any userform in the workbook and I get it if I try to run code that is in the userform module. However, if I paste the code into a regular module and run it, the code runs fine.


    Does anyone know what could be causing this? I don't think my code is causing the problem since it runs on some machines, I am guessing there is a setting that is preventing Excel from calling Userforms. Any ideas?


    Thanks,


    Joe


    I have a button, Private Sub CommandButton1_Click, on a sheet that shows a userform. This is the bit of code that gets tagged with the run time error. The userform has a refedit control on it allows the user to select a cell and then hit ok to run the code or cancel to, well, cancel it.




    Code for the button that gets tagged with the run time error:

    Code
    Private Sub CommandButton1_Click()
        frmLoadTrade.Show 'calls userform
    End Sub


    Code in the Userform Module:

  • Re: Run Timer Error When Calling Userform


    Baffled why you'd get a subscript out of range error on some computers and not others. User rights?


    Try using a Form button (View - Toolbars - Forms) with a macro in a "public" module instead of a Command button to invoke the UserForm and see if that makes any difference.

  • Re: Run Timer Error When Calling Userform


    Can you tell me whether the code was done in Excel 2003 and then run in Excel 2000, because I have had the same problem and this has been the cause. You can add the code in 2000 and it runs OK in 2003, but there appears to be a problem sometimes the other way around.


    Bill

  • Re: Run Timer Error When Calling Userform


    Thanks for the suggestions. The code was written in Office 2000 and used in 2000.


    I figured out what the problem was. I was referring to an external workbook as Workbooks("TradeDB") when I should have been referring to it as Workbooks("TradeDB.xls"). I dont know why the first way worked on some machines but not others, but now it works on all machines.


    Joe

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!