Show and Hide Forms

[Guest]

Hi
I am having unpredictable results with Hide, Show and Unload in 4
non-dependent userforms: frmInput, frmAllocate, frmReport, frmPrint. On each
form are Hide and Quit (=unload) cmdbuttons. On a wsheet is a single
'Reveal' cmdbutton and cmdbuttons to invoke each form.

In each Hide button the code is simply:
Private Sub cmdHide_Click()
frmReports.Hide 'frmReports for example
End Sub

The wsheet 'Reveal' button is:
Sub RevealForm()
If UserForms.Count > 0 Then
UserForms(0).Show 'Reveal hidden form
Else
MsgBox "No forms hidden."
End If
End Sub

When unloading say frmReport, frmInput is often loaded but not shown.
This means none of the forms can be shown as in each of the wsheet cmdbutton
which calls the forms is (frmInput by example):
If UserForms.Count > 0 Then
If UserForms(0).Name = "frmInput" Then formname = "Input"
If UserForms(0).Name = "frmAllocate" Then formname = "Allocate"
If UserForms(0).Name = "frmReport" Then formname = "Report"
If UserForms(0).Name = "frmPrint" Then formname = "Print"
MsgBox "'Input Data' error" & vbCr & vbCr & _
"The " & formname & " form is still open. "
Exit Sub 'This passes code to Show form being executed
End If

There is no point in leaving forms loaded as it can be a while before
another utility is needed.
Can anyone suggest where I might be going wrong or if there is a better
strategy please?

T.I.A

Geoff

 

[Bob Phillips]

I am not getting what the problem is?

 

[Guest]

HiWhen unloading say frmReport, frmInput is often loaded but not shown.
This means none of the forms can be shown as in each of the wsheet
cmdbutton <<
What I am saying is that after a form has been unloaded I sometimes cannot
call other forms because frmInput has been loaded during the unload process.
2 thoughts:
1. I believe that Userforms(0) refers to the currently loaded form, not an
individual form?
2. frmInput uses a lot of variables, some of its functions are replicated -
like date input txtboxes in the other forms. I have tried to be careful when
unloading each form to set these global variables to nothing. But could that
be what is causing a 'reference' to frmInput when unloading others?

Geoff

 

[Bob Phillips]

Are you just trying to check whether a form is loaded or not?

If so, you need to loop through the userform collection

For i = 0 To UserForms.Count - 1
'Find the one with the matching name
If UserForms(i).Name = FormName Then
MsgBox Formname & " loaded"
Exit For
End If
Next i

 

[Guest]

Hi
I want to prevent frmInput being loaded during the unload process of other
forms.

Each wsheet cmdbutton checks to see if any form is hidden, if so, it gives a
message and exits the sub.

I think I am right in saying that if a form is 'referred' to it causes it to
be loaded. In that line of logic then somehow a reference is being made to
frmInput and it is that which causes it to be loaded?

Can Userforms(0) be the cause or some of the global variables? I've done so
much tesing on this I'm stuck and getting RSS.

Geoff

 

[Bob Phillips]

Geoff,

I am trying, believe me, but struggling somewhat.

You don't actually refer to a form, just check the userforms collection, so
that shouldn't load a form. Can you post all of the code and describe a
series of actions that will demonstrate the problem, you know, like click
button A, form a shows, click hide on that form, etc. as I cannot fully
visualise the whole picture.

 

[Guest]

Hi
Yes I really appreciate the interest shown, thank you.
Re the code, it is quite humungous in total. I think though that I have
included the relevant parts.
With regard to the mechanics:
1. Click wsheet frmReports btn to open form
This tests if other forms are open - if not then opens it
2. Do stuff with form - this seems critical, just clicking btns on an
'empty' form is ok
3. Click Hide btn on form
4. Click Reveal btn on wsheet to return frmReports
5. Click Quit btn on form to unload - so far so good
6. Click wsheet frmPrint btn to open BUT get my msgbox 'frmInput is loaded'
ALSO
7. Click wsheet 'Reveal' but no action at all. If frmInput is loaded it
should show.

Nothing seems to be running in the VBE but if I do 'Reset' then the VBE
screen changes as though something was running.
Curious.

Geoff

 

[Bob Phillips]

Geoff,

This is really odd. Needless to say, I don't get the problem.

I followed your instructions to the letter. I put two textboxes on the
report form and input data in both, then changed the data in one, before
hiding.

Forgive this question, but you unloading it correctly aren't you.

This is the code I had with thre forms and thre show buttons

Sub RevealForm()
If UserForms.Count > 0 Then
UserForms(0).Show 'Reveal hidden form
Else
MsgBox "No forms hidden."
End If
End Sub

Sub ShowInput()
Dim FormName
If UserForms.Count > 0 Then
If UserForms(0).Name = "frmInput" Then FormName = "Input"
If UserForms(0).Name = "frmAllocate" Then FormName = "Allocate"
If UserForms(0).Name = "frmReport" Then FormName = "Report"
If UserForms(0).Name = "frmPrint" Then FormName = "Print"
MsgBox "'Input Data' error" & vbCr & vbCr & _
"The " & FormName & " form is still open. "
Exit Sub 'This passes code to Show form being executed
End If
frmInput.Show
End Sub

Sub ShowPrint()
Dim FormName
If UserForms.Count > 0 Then
If UserForms(0).Name = "frmInput" Then FormName = "Input"
If UserForms(0).Name = "frmAllocate" Then FormName = "Allocate"
If UserForms(0).Name = "frmReport" Then FormName = "Report"
If UserForms(0).Name = "frmPrint" Then FormName = "Print"
MsgBox "'Input Data' error" & vbCr & vbCr & _
"The " & FormName & " form is still open. "
Exit Sub 'This passes code to Show form being executed
End If
frmPrint.Show
End Sub

Sub ShowReport()
Dim FormName
If UserForms.Count > 0 Then
If UserForms(0).Name = "frmInput" Then FormName = "Input"
If UserForms(0).Name = "frmAllocate" Then FormName = "Allocate"
If UserForms(0).Name = "frmReport" Then FormName = "Report"
If UserForms(0).Name = "frmPrint" Then FormName = "Print"
MsgBox "'Input Data' error" & vbCr & vbCr & _
"The " & FormName & " form is still open. "
Exit Sub 'This passes code to Show form being executed
End If
frmReport.Show
End Sub

 

[Guest]

Hi
Ok - hear what you say. Yes I believe I am unloading each form correctly
using:
Unload frmInput
Set frmInput = Nothing etc

The problem got worse on Hiding frmReport. The work done in the form is to
compile a fair number of pivot tables but also to add 2 extra sheets for the
reports. The extra sheets have code written to their sheet modules (using
Chip Pearson's code) to create Activate and De-Activate events. That piece
of code caused frmReport to not just Hide when required but to unload
completely! It was followed by the same behaviour of frmInput being loaded
but not shown and the 'Reveal' btn not performing. I presume that is because
frmInput has the focus. Remove this piece and the problem goes back to as
previously described.

Clearly there is something not right and I need to look at my strategy of
deploying the Hide btn and ask myse if it is appropriate for this form (I
suppose only included for form compatability reasons). In execution this
form only requires dates from the user, the code does the rest of the work.
There shouldn't be a 'pressing need' to hide it and do something else unlike
frmInput. Taking frmReport Hide away removes the other form Hide issues as
well. Somewhere there has to be a reference to frmInput.

In the meantime, now you confirm that referencing the Userforms collection
should not in itself invoke a form, could you please confirm whether using
global variables might?

Geoff