Please can somebody help with my problem? I know it looks really boring to read this lot through but I
would really appreciate your advice.
I am having significant problems with one of my spreadsheets (causes Excel to
repeatedly crash) since I tried to automate it using userforms.
Unfortunately I can't post the actual spreadsheet
due to file size limitations plus the fact that it contains queries to external datasources so I will try and describe it as
best I can in pseudo-code. Hopefully someone can see the errors of my ways?
Failing that, I am trying to
reconstruct my problem with a cut-down version of the spreadsheet so possibly I might be able to post something in the
Here is my scenario:-
1. workbook, containing 3 sheets opens with "sheet1" selected. Sheets 2 & 3 are hidden.
2. simple userform containing 1 combobox and 1 command button display as part of workbook_open() event. Combobox takes as its
rowsource a range from sheet1
3.on pressing command button, userform is hidden (not unloaded), macro generates a dynamic SQL statement (using contents
chosen from combobox), unhides "sheet2", selects "sheet2" which contains a querytable, updates the command text using the
dynamic SQL, refreshes the querty with background refresh = false (i.e. query is completed before macro continues), re-hides
4. macro continues (still originating from the command button on userform), unhides "sheet3", selects "sheet3" which contains
a pivottable, using the results of "sheet2" as its datasource.
5. The worksheet_activate() event of sheet3 includes code to refresh the pivot table so as soon as the sheet is selected the
PT is refreshed "before the users eyes" with the new data.
6. On pressing the save button, the workbook_beforeSave() event pops up another userform containing a single textbox &
command button. The user types password in textbox and command button checks this passoerd. If OK, saves file else informs
user that file cannot be saved and exits with cancel=true
7. Moving away from "sheet3" to "sheet1" (the only sheet visible) causes "sheet3" to be hidden in worksheet_deactivate()
8. Worksheet_activate() event of "sheet1" re-displays userform and the process starts again.
Well, on the face of
it, this appears to work well UNTIL the user either trys to close the spreadsheet or presses save (with the correct password)
1. If excel is still open and you try to re-open the speadsheet is crashes with Excel has generated errors
2. If I close Excel and then re-open, it will begin to load the file and then display a "file/path access error" msgbox, with
debug sitting on the userform.show statement.
3. Comment out all refrences to userform.show and re-save the file. Then reload it, and manually run macro - all is fine.
Uncomment userform.show, save & run = no joy!
4. Closing the spreadsheet with "sheet1" selected seems to help significantly (why?) but still causes it to crash
4. I'm using Excel 2003 but have tried it on Excel 2002 with similar (but not so erratic results)
5. Have created new workbook, added new comboboxes, "cleaned" my VBA project etc without success so I dont think its a
I am convinced its something to do with the order I am trying to do things and Excel is getting
itself tied up in knots?
I have tried every permutation of enabling/disabling events, moving code from userforms
to worksheet events, again without success.
I've checked and double-checked that my events aren't getting caught
in a recursive loop which is normally the problem when I get this kind of error, but perhaps the events of the Querytable are
conflicting with the PivotTable?
Why is Excel loosing the userform details between sessions even though i can
"see" the forms in the project explorer?
Conceptually, am I doing something wrong?
If your reading
this, then thank you for staying with it and I look forward to any advice you can give to restore my sanity!