Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need Programmer Advice concerning NonModal Forms
To all,
Can someone PLEASE advise how they address the following problem that is easily recreatable and ends with eXcel aborting ... eXcel 2000 (SP3) I have a workbook with a number of sheets ... one of the sheets has an activeX CommandButton that launches a NonModal Form. 1) If I have the NonModal Form displayed and I group select and delete some of the worksheets (including the one that had the NonModal Form displayed), eXcel aborts ... 2) I added code to the Worksheet_Deactivate event to determine when the user has left the worksheet containing the NonModal Form, I then remove the Form from the Display... With the code: "frmNAVIGATION.Hide" ... excel aborts when the WorkSheet is Deleted also ... ''''''''''' Private Sub Worksheet_Deactivate() ' If FORM is being displayed as NonModal, Hide it Application.ScreenUpdating = False On Error Resume Next frmNAVIGATION.Hide : Application.ScreenUpdating = False On Error GoTo 0 End Sub ''''''''''' 3) If I use "Unload frmNAVIGATION" ... then no abort occurs but NOW I'm left with a programming problem that I don't know how to solve ... That is, when the User eventually gets back to the specific sheet ... I'm uncertain how I should have Recorded the 'state' of the Nonmodal Form at the time the WorkSheet was Deactivated ... so that I can restore it if need be ... ''''''''''' Private Sub Worksheet_Deactivate() ' If FORM is being displayed as NonModal, Unload it Application.ScreenUpdating = False On Error Resume Next Unload frmNAVIGATION: Application.ScreenUpdating = False On Error GoTo 0 End Sub ''''''''''' QUESTION: ANY IDEAS how to track the state of a FORM at the time that a Worksheet is Deactivated until the time (if ever) that it is "Activated" again ????? Thanks, JimP |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need Programmer Advice concerning NonModal Forms
Define a Public Variable at MODULE level:
Public fLoaded as Boolean Then in your frmNAVIGATION form's code add this in UserForm_Initialize() procedu fLoaded = True and this in UserForm_Terminate() procedu fLoaded = False fLoaded being defined as Public variable at module level, it will provide the state of the Form all the time, True if form is loaded, False if it is unloaded. Sharad "JimP" wrote in message om... To all, Can someone PLEASE advise how they address the following problem that is easily recreatable and ends with eXcel aborting ... eXcel 2000 (SP3) I have a workbook with a number of sheets ... one of the sheets has an activeX CommandButton that launches a NonModal Form. 1) If I have the NonModal Form displayed and I group select and delete some of the worksheets (including the one that had the NonModal Form displayed), eXcel aborts ... 2) I added code to the Worksheet_Deactivate event to determine when the user has left the worksheet containing the NonModal Form, I then remove the Form from the Display... With the code: "frmNAVIGATION.Hide" ... excel aborts when the WorkSheet is Deleted also ... ''''''''''' Private Sub Worksheet_Deactivate() ' If FORM is being displayed as NonModal, Hide it Application.ScreenUpdating = False On Error Resume Next frmNAVIGATION.Hide : Application.ScreenUpdating = False On Error GoTo 0 End Sub ''''''''''' 3) If I use "Unload frmNAVIGATION" ... then no abort occurs but NOW I'm left with a programming problem that I don't know how to solve ... That is, when the User eventually gets back to the specific sheet ... I'm uncertain how I should have Recorded the 'state' of the Nonmodal Form at the time the WorkSheet was Deactivated ... so that I can restore it if need be ... ''''''''''' Private Sub Worksheet_Deactivate() ' If FORM is being displayed as NonModal, Unload it Application.ScreenUpdating = False On Error Resume Next Unload frmNAVIGATION: Application.ScreenUpdating = False On Error GoTo 0 End Sub ''''''''''' QUESTION: ANY IDEAS how to track the state of a FORM at the time that a Worksheet is Deactivated until the time (if ever) that it is "Activated" again ????? Thanks, JimP |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need Programmer Advice concerning NonModal Forms
Sharad,
Thanks for the prompt reply, I'll be trying that out in the morning ... J.Pellechi *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need Programmer Advice concerning NonModal Forms
Well, I now understood what exactly you want:
Add the code as I already mentioned in previous post. Additionally do following: Define one more Public variable at Module level as under: Public IunLoaded as Boolean Then your code to unload form, in worksheet_deactivate procedure should be as under: Application.ScreenUpdating = False If fLoaded then Unload frmNAVIGATION: Application.ScreenUpdating = False IunLoaded = True End If (No need for on error resume next) And the code in worksheet_activave procedure should be as under: If IunLoaded Then frmNAVIGATION.Show '(Or Load (frmNAVIGATION, if you want to only load and now show the form.) IunLoaded = False End If (Don't forget to set IunLoaded to False, after you show / load form again.) Sharad "microsoft.public.excel.programming" wrote in message ... Sharad, Thanks for the prompt reply, I'll be trying that out in the morning ... J.Pellechi *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
need an Excel VBA programmer | Excel Discussion (Misc queries) | |||
MODAL vs. NonMODAL | Excel Programming | |||
Beginner programmer problem | Excel Programming | |||
VBA programmer feedback | Excel Programming | |||
Non programmer needs calculation help!!!!! | Excel Programming |