Userforms - Path/File access error

[Pete_Bristol]

Dear All,

I have already done a fair bit of research on this problem but unfortunately whilst I have found lots of similar questions, there were no answers given! Hopefully someone can help here which will help others as well?

I have reduced a problem that I was getting in a big, complex workbook to its simpliest form - a single sheet, with a single userform, a single commandbutton and only two lines of code "userform.show" in the workbook_open() event and userform.hide in the commandbutton. I have created this in a completetly new workbook.

The problem I am getting is that the first time the spreadsheet opens, all is fine. The userform display and functions properly. If I then close the workbook and reopen (without quiting Excel) the worksheet crashes out with a "Path/File access error" and when I press debug it highlights the "userform.show" statement and says that the resource cannot be found. However i can see it in the project explorer. Coming out causes excel to throw an "Excel has encountered problems..." windows and restarts. If however, I quit Excel and call up the spreadsheet, it functions normally again.

Its as though Excel is remembering "something" which is only cleared when excel re-starts?

The IT boys here are stumped - so far they have:-
(a) re-installed Excel 2003
(b) completely zapped my profile - both local and on server
(c) checked for hardware errors
(d) checked that I have all the necessary userform references ticked i.e FM20.DLL

All have prooved unsucessful.

Please can somebody offer guidance as to what might be going wrong? WHere does Excel store its userforms? What/where does it store its environment variables between sessions?

Somebody also thought it might be an "access rights issue" - but I am set up as an administrator on my machine, and am saving it to my local hard disk - I have tried other locations as well - same result.

A colleague can open/close this simple spreadsheet without problem so its definately something that has become corrupt on my PC.

I suppose I could completely re-format my PC and start again but this really would be as a last result.

Any pointers would be greatfully appreciated.

Thanks

Pete

 

[jindon]

Really not sure though...

have you tried

Unload unserformname instead?

 

[Pete_Bristol]

jindon,

Thanks for reply - just tried that - same problem!

Pete

 

[ilyaskazi]

Even I face similar problem with my one of my big project. I am struggling to find the solution for the same. I have debugged every line of codes with no error.

My VBA Project is password protected. It runs fine in Editing mode but when I distribute others to test, it fails to run and crashed.

However fews days ago I personally came with stupid solution with the below codes. I am confused how it works ???

This code needs to be written before calling userform to display:

With Application                            '   << Handle Excel Crash Error !!!
    If .VBE.MainWindow.Visible = False Then     '
        .ScreenUpdating = False                 '
        .VBE.MainWindow.Visible = True          '
        .VBE.MainWindow.Visible = False         '
        .ScreenUpdating = True                  '
    End If                                      '
End With

Assuming, my codes run fine in editing mode I have write the above codes to work with my project accordingly to open VBE once and start.

But this is not the actual solution. I too wait for any great ideas for this problem to solve...