Need Programmer Advice concerning NonModal Forms

[JimP]

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

 

[Sharad Naik]

Define a Public Variable at MODULE level:

Public fLoaded as Boolean

Then in your frmNAVIGATION form's code add
this in UserForm_Initialize() procedure:
fLoaded = True

and this in UserForm_Terminate() procedure:
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

 

[microsoft.public.excel.programming]

Sharad,
Thanks for the prompt reply, I'll be trying that out in the morning ...

J.Pellechi

 

[Sharad Naik]

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