Odd UserForm Behavior

[Guest]

I've created a userform to gather question responses and save them to a
spreadsheet. I made a "Save" button which saves the spreadsheet and then
asks if the user wants to quit. The user form is unloaded and the
application quits.

For some reason, when the user returns to the form, to continue entering in
data, Excel locks up after 1 or 2 items entered.

here is the workbook open code:
Sub Workbook_Open()
removerandc
QWiz.MultiPage1.Value = 0
QWiz.settips
QWiz.UpdateProgress
QWiz.Show
Unload QWiz
restorerandc
Application.Quit
End Sub

and here is the save button code:
Private Sub Savebtn_Click()
UpdateProgress
ActiveWorkbook.Save
exitsurvey = MsgBox("UAS Evaluation progress saved. Exit Survey?", vbYesNo,
"Exit Survey?")
If exitsurvey = 6 Then
Application.DisplayAlerts = False
Unload QWiz
End If
End Sub
--

I am truly stumped. Any ideas would be great.

Thanks,
Gene

 

[Guest]

Add-on. Here is what is causing the problem: when I re-open the form, any
attempts to write data to the spreadsheet are locking up Excel. So, when I
close the form the first time, something seems to be "write-protecting" the
spreadsheet...any thoughts on how to detect that or how to ensure I close
this form properly?

Thanks,

 

[Guest]

Latest Chapter:
This application works fine the first time you open it.
This application also works fine as long as the VBE is open and at least one
of my code modules is open.

Will lock up immediately if the application has been saved and re-opened and
the code (VBE) window is not open.

WTF?

 

[NickHK]

Gene,
My first thought would be remove the Application.DisplayAlerts = False, so
you can see if Excel is trying to tell you something.
Also, you Unloading the userform in the Savebtn_Click event and then again
after QWiz.Show.
Do you really want to close Excel every time this file is used ?

NickHK
P.S. You can use vbYes in place of 6. It more clear what you mean.

 

[Guest]

Nick,

Thanks for the reply. I have been at it most of the night.

Here is the original Workbook_Open
Sub Workbook_Open()
removerandc
QWiz.MultiPage1.Value = 0
QWiz.settips
QWiz.UpdateProgress
QWiz.Show
restorerandc
End Sub

I think the version I pasted in earlier had some of my troubleshooting
changes in it. Sorry.


Here is the workbook save button:
Private Sub Savebtn_Click()
UpdateProgress
ActiveWorkbook.Save
exitsurvey = MsgBox("Exit Survey?", vbYesNo, "UAS Evaluation progress saved.
Exit Survey?")
If exitsurvey = 6 Then
Application.DisplayAlerts = False
restorerandc
Application.Quit
End If
End Sub

removerandc & restorerandc are just removing and restoring toolbars etc.
The Application.DisplayAlerts = False is to suppress the alert box when
closing the application to save the worksheet again.

I commented out the DisplayAlerts and just got the same alert box asking me
if I wanted to save the worksheet. I clicked yes and when I reopened
it...same problem.

 

[NickHK]

Gene,
For one thing, if you have just .Saved the WB and not made any further
changes, you should not get that warning.
What do you get for Thisworkbook.saved=true ?
Or if you loop through all workbooks checking their .saved property ?
If one of them is False, it would seem you are interacting with some
unexpected WB.

Apart from that, what does UpdateProgress do ?

NickHK

 

[Guest]

Nick,

Thanks for the reply...very thought provoking. I've tried using the
"ThisWorkbook.saved = true", but that's not the cause. I think something's
up when I run the updateprogress.routine...because even when I just open and
save the app. the problem occurs...and I notice the Excel file inflates in
size from 266KB to 330KB. I had thought it was just save information...and
it may be, but it may be something else as well.

Many thanks,

 

[NickHK]

Gene,
Check those other routine to confirm they are only doing what you expect.

NickHK

 

[Guest]

That's my next step, I think. Turns out some users have had no problems,
others have. I've cleaned out my temp files to try to ensure nothing's
cached in there, but I didn't see a change in behavior.
I'm going to re-create the app from scratch and see if I can find the
problem. In the interim, I've found a workaround that -- hilariously --
works: if you open the VBE before you open the App (and leave it open),
everything works fine.

Odd.