Keep Losing VBAProject

[Please Help]

I created a user form this morning, and after I created and when I opened the
user form (by opening the Excel file containing the user form), it crashed.
After it crashed and the Excel file restored and repaired, I got a message
that I lost my entire VBAProject. When I checked it, I did lose it.

So I created again for second time. This time the form opened, and I was
able to navigate to other forms. When I clicked on the Close button on the
main form, it crashed again. I have never had this experience.

Does anyone know why it keeps crashing?

I don't know this would be an issue. I created those user forms to lookup
the information from the sheet in that Excel file.

Thanks.

 

[Matthew Pfluger]

What kinds of controls or events did you have on your userform? I've found
that I've had huge problems with RefEdits and events. However, I've not
encountered something so specific as a crash when clicking the 'X' button.

Is there anything unique about what you're trying to do that might have
caused this?

HTH,
Matthew Pfluger

 

[Please Help]

Hi Matthew,

Thanks for your response.

Basically, I have a main form with command buttons to the lookup forms and a
"Close" command button to close the main form. The form's X buttons are
disable on both main form and lookup forms (to force the users to return to
the main form and use the "Close" button) with the code below:

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = 0 Then Cancel = True
End Sub

On my lookup forms, I have a combox box and a few text boxes. The
ControlSource and RowSource of the combox box are referenced to the sheet as
"B2" and "B2:B2000", respectively.

On any lookup form, when I select a value from the combo box, the text boxes
will show the values associated with the combox box.

Based on my current settings, can you think of anything that might cause the
file to crash and lose the VBA Project?

Thanks.

 

[Jon Peltier]

I don't know what causes this, other than some ill-defined "corruption".
Here are a couple things that may help:

Export, Delete, and Re-Import all of the VB components (modules and
userforms) in the project. Rob Bovey's Code Cleaner add-in does this for you
(http://appspro.com).

Close Excel, empty the Temp directory. To find the temp directory easily, go
to the Windows Start menu, Run, then type

%temp%

in the box and press Enter. One time I also had to delete a few *.EXD files
from another directory under C:\Documents and Settings\username\Application
Data.

- Jon

 

[Please Help]

Jon,

Thanks for your response. I tried all of your options you have mentioned,
and I still got the same errors.

From this whole experience, I have learned that the errors occurs when I
have a cell reference in the ControlSource of combo box. If I don't have a
cell reference, I do not get those errors.

Thanks.

 

[Jon Peltier]

Aha. I haven't seen errors from ControlSource links, but I avoid them,
because they are rather inflexible. I always pass the information into and
out of the user form using properties:

http://peltiertech.com/Excel/PropertyProcedures.html

- Jon