Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Odd UserForm Behavior
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Odd UserForm Behavior
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, -- Gene "Gene" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Odd UserForm Behavior
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? -- Gene "Gene" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Odd UserForm Behavior
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. "Gene" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Odd UserForm Behavior
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. :( -- Gene "NickHK" wrote: 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. "Gene" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Odd UserForm Behavior
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 "Gene" wrote in message ... 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. :( -- Gene "NickHK" wrote: 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. "Gene" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Odd UserForm Behavior
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, -- Gene "NickHK" wrote: 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 "Gene" wrote in message ... 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. :( -- Gene "NickHK" wrote: 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. "Gene" wrote in message ... 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Odd UserForm Behavior
Gene,
Check those other routine to confirm they are only doing what you expect. NickHK "Gene" wrote in message ... 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, -- Gene "NickHK" wrote: 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 "Gene" wrote in message ... 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. :( -- Gene "NickHK" wrote: 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. "Gene" wrote in message ... 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Odd UserForm Behavior
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. -- Gene "NickHK" wrote: Gene, Check those other routine to confirm they are only doing what you expect. NickHK "Gene" wrote in message ... 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, -- Gene "NickHK" wrote: 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 "Gene" wrote in message ... 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. :( -- Gene "NickHK" wrote: 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. "Gene" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
odd behavior | Excel Discussion (Misc queries) | |||
Alt+E Behavior | Excel Discussion (Misc queries) | |||
Odd Tab behavior | Excel Programming | |||
Tab Key Behavior | Excel Programming | |||
Control code behavior with userform ? | Excel Programming |