ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   UserForm remains visible after Unload - 2003 (https://www.excelbanter.com/excel-programming/425476-userform-remains-visible-after-unload-2003-a.html)

wpiet
UserForm remains visible after Unload - 2003
 
Every post I've found for this problem seems to have been resolved with
Application.ScreenUpdating = True. Not in this case.

With 2 UserForms, Form 1 remains visible behind Form 2, even though Form 1
was unloaded before Form 2 was loaded & shown.

The main code module (all variables are Public):

Sub ACRE_MthCtrb()

Application.ScreenUpdating = True

ThisWorkbook.Activate
FilPth = ThisWorkbook.Path & "\"

<< The 1st UserForm loads, executes & unloads in function VldLogin() .
This form & function are in a separate, hidden workbook, with a
reference
set in the current workbook.

LoginVld = VldLogin()
If Not LoginVld = True Then
ActiveWindow.Close _
SaveChanges:=False
End If

<< Here, the 2nd UserForm, ProgBar, appears. The 1st form already unloaded.

Application.ScreenUpdating = False

ProgBar.LblProgBar.Width = 0
ProgBar.Show

<< Remainder of processing is in Sub Prc(), called from UserForm ProgBar


Workbooks("ACRE Monthly Contributions.xls").Activate
Application.ScreenUpdating = True
ActiveWindow.Close _
SaveChanges:=False
End Sub

Sub Prc()
..
..
..
End of module

<< The 1st UserForm, QryLogin, loads in this module, Login:

Public Function VldLogin() As Boolean
Abt = vbNo
Load QryLogin
QryLogin.Show
If Abt = vbYes Then
VldLogin = False
Else
VldLogin = True
End If
End Function

<< Userform QryLogin contains the following code:

Private Sub OK_Click()
Dim LoginAut As Boolean

If Len(Trim(Me.UsrID)) = 0 Then
MsgBox ("You Must Enter A User Name")
Me.UsrID.SetFocus
ElseIf Len(Trim(Me.Psw)) = 0 Then
MsgBox ("You Must Enter A Password")
Me.Psw.SetFocus
Else
LoginAut = AutLogin(Me.UsrID, Me.Psw)
If LoginAut = True Then
Login.UsrID = Me.UsrID.Value
Login.Psw = Me.Psw.Value

<< Here is the Unload for the 1st form

Unload Me
Else
MsgBox ("Invalid Username or Password; Please Re-Enter")
Me.UsrID.SetFocus
End If
End If
End Sub

Function AutLogin(ByVal UsrID As String, _
ByVal Psw As String) _
As Boolean

Const ADS_SECURE_AUTHENTICATION = 1
Dim Aut As Object ' Authentication
Dim Dmn As String ' Domain
Dim G_C As Object ' Global Catalog
Dim Root As Object ' RootDSE

On Error Resume Next
Set Root = GetObject("GC://rootDSE")
Dmn = Root.Get("defaultNamingContext")
Set G_C = GetObject("GC:")
Set Aut = G_C.OpenDSObject("GC://" & Dmn, UsrID, Psw,
ADS_SECURE_AUTHENTICATION)

If Aut Is Nothing Then
AutLogin = False
Else
AutLogin = True
End If

Set Aut = Nothing
Set G_C = Nothing
Set Root = Nothing
End Function

<< The 2nd UserForm, ProgBar, conatains this code:

Private Sub UserForm_Activate()
Call Prc
Unload Me
End Sub

Stepping thru the logic, it works fine; Form 1 disappears before Form 2
appears.
Running full-bore, Form 1 remains visible behind Form 2 the entire time the
program runs. Since screen updating is off at this time, the processing is
not seen behind Form 1, which is correct.

I have tried moving the 'ScreenUpdating = False' statement further on in the
code but to no avail.
Is there, perhaps, a UserForm property or some Excel setting that would
cause this?

--
Will

[email protected]
UserForm remains visible after Unload - 2003
 
Hi
Try throwing in a
DoEvents

after you unload Form1. Sometimes kicks the screen into action. It may
be that your form is unloaded but the screen has not updated.
regards
Paul

On Mar 12, 6:28*pm, wpiet wrote:
Every post I've found for this problem seems to have been resolved with
Application.ScreenUpdating = True. Not in this case.

With 2 UserForms, Form 1 remains visible behind Form 2, even though Form 1
was unloaded before Form 2 was loaded & shown.

The main code module (all variables are Public):

Sub ACRE_MthCtrb()

* *Application.ScreenUpdating = True

* * ThisWorkbook.Activate
* * FilPth = ThisWorkbook.Path & "\"

<< *The 1st UserForm loads, executes & unloads in function VldLogin() .
* * * *This form & function are in a separate, hidden workbook, with a
reference
* * * *set in the current workbook.

* * LoginVld = VldLogin()
* * If Not LoginVld = True Then
* * * * ActiveWindow.Close _
* * * * * * SaveChanges:=False
* * End If

<< Here, the 2nd UserForm, ProgBar, appears. The 1st form already unloaded.

* * Application.ScreenUpdating = False

* * ProgBar.LblProgBar.Width = 0
* * ProgBar.Show

<< Remainder of processing is in Sub Prc(), called from UserForm ProgBar *

* * Workbooks("ACRE Monthly Contributions.xls").Activate
* * Application.ScreenUpdating = True
* * ActiveWindow.Close _
* * * * SaveChanges:=False
End Sub

Sub Prc()
.
.
. *
End of module

<< The 1st UserForm, QryLogin, loads in this module, Login:

Public Function VldLogin() As Boolean
* * Abt = vbNo
* * Load QryLogin
* * QryLogin.Show
* * If Abt = vbYes Then
* * * * VldLogin = False
* * Else
* * * * VldLogin = True
* * End If
End Function

<< Userform QryLogin contains the following code:

Private Sub OK_Click()
* * Dim LoginAut As Boolean

* * If Len(Trim(Me.UsrID)) = 0 Then
* * * * MsgBox ("You Must Enter A User Name")
* * * * Me.UsrID.SetFocus
* * ElseIf Len(Trim(Me.Psw)) = 0 Then
* * * * MsgBox ("You Must Enter A Password")
* * * * Me.Psw.SetFocus
* * Else
* * * * LoginAut = AutLogin(Me.UsrID, Me.Psw)
* * * * If LoginAut = True Then
* * * * * * Login.UsrID = Me.UsrID.Value
* * * * * * Login.Psw = Me.Psw.Value

<< Here is the Unload for the 1st form

* * * * * * Unload Me
* * * * Else
* * * * * * MsgBox ("Invalid Username or Password; Please Re-Enter")
* * * * * * Me.UsrID.SetFocus
* * * * End If
* * End If
End Sub

Function AutLogin(ByVal UsrID As String, _
* * * * * * * * * * * * * *ByVal Psw As String) _
* * * * *As Boolean

* * Const ADS_SECURE_AUTHENTICATION = 1
* * Dim Aut * * As Object * * * ' Authentication
* * Dim Dmn * * As String * * * ' Domain
* * Dim G_C * * As Object * * * ' Global Catalog
* * Dim Root * *As Object * * * ' RootDSE

* * On Error Resume Next
* * Set Root = GetObject("GC://rootDSE")
* * Dmn = Root.Get("defaultNamingContext")
* * Set G_C = GetObject("GC:")
* * Set Aut = G_C.OpenDSObject("GC://" & Dmn, UsrID, Psw,
ADS_SECURE_AUTHENTICATION)

* * If Aut Is Nothing Then
* * * * AutLogin = False
* * Else
* * * * AutLogin = True
* * End If

* * Set Aut = Nothing
* * Set G_C = Nothing
* * Set Root = Nothing
End Function

<< The 2nd UserForm, ProgBar, conatains this code:

Private Sub UserForm_Activate()
* * Call Prc
* * Unload Me
End Sub

Stepping thru the logic, it works fine; Form 1 disappears before Form 2
appears.
Running full-bore, Form 1 remains visible behind Form 2 the entire time the
program runs. Since screen updating is off at this time, the processing is
not seen behind Form 1, which is correct.

I have tried moving the 'ScreenUpdating = False' statement further on in the
code but to no avail.
Is there, perhaps, a UserForm property or some Excel setting that would
cause this?

--
Will

ryguy7272
UserForm remains visible after Unload - 2003
 
I didn't see the Unload syntax in there.
Something like this may work:

Unload UserForm1
UserForm1.Show

That works for me.

Regards,
Ryan---
--
RyGuy


" wrote:

Hi
Try throwing in a
DoEvents

after you unload Form1. Sometimes kicks the screen into action. It may
be that your form is unloaded but the screen has not updated.
regards
Paul

On Mar 12, 6:28 pm, wpiet wrote:
Every post I've found for this problem seems to have been resolved with
Application.ScreenUpdating = True. Not in this case.

With 2 UserForms, Form 1 remains visible behind Form 2, even though Form 1
was unloaded before Form 2 was loaded & shown.

The main code module (all variables are Public):

Sub ACRE_MthCtrb()

Application.ScreenUpdating = True

ThisWorkbook.Activate
FilPth = ThisWorkbook.Path & "\"

<< The 1st UserForm loads, executes & unloads in function VldLogin() .
This form & function are in a separate, hidden workbook, with a
reference
set in the current workbook.

LoginVld = VldLogin()
If Not LoginVld = True Then
ActiveWindow.Close _
SaveChanges:=False
End If

<< Here, the 2nd UserForm, ProgBar, appears. The 1st form already unloaded.

Application.ScreenUpdating = False

ProgBar.LblProgBar.Width = 0
ProgBar.Show

<< Remainder of processing is in Sub Prc(), called from UserForm ProgBar

Workbooks("ACRE Monthly Contributions.xls").Activate
Application.ScreenUpdating = True
ActiveWindow.Close _
SaveChanges:=False
End Sub

Sub Prc()
.
.
.
End of module

<< The 1st UserForm, QryLogin, loads in this module, Login:

Public Function VldLogin() As Boolean
Abt = vbNo
Load QryLogin
QryLogin.Show
If Abt = vbYes Then
VldLogin = False
Else
VldLogin = True
End If
End Function

<< Userform QryLogin contains the following code:

Private Sub OK_Click()
Dim LoginAut As Boolean

If Len(Trim(Me.UsrID)) = 0 Then
MsgBox ("You Must Enter A User Name")
Me.UsrID.SetFocus
ElseIf Len(Trim(Me.Psw)) = 0 Then
MsgBox ("You Must Enter A Password")
Me.Psw.SetFocus
Else
LoginAut = AutLogin(Me.UsrID, Me.Psw)
If LoginAut = True Then
Login.UsrID = Me.UsrID.Value
Login.Psw = Me.Psw.Value

<< Here is the Unload for the 1st form

Unload Me
Else
MsgBox ("Invalid Username or Password; Please Re-Enter")
Me.UsrID.SetFocus
End If
End If
End Sub

Function AutLogin(ByVal UsrID As String, _
ByVal Psw As String) _
As Boolean

Const ADS_SECURE_AUTHENTICATION = 1
Dim Aut As Object ' Authentication
Dim Dmn As String ' Domain
Dim G_C As Object ' Global Catalog
Dim Root As Object ' RootDSE

On Error Resume Next
Set Root = GetObject("GC://rootDSE")
Dmn = Root.Get("defaultNamingContext")
Set G_C = GetObject("GC:")
Set Aut = G_C.OpenDSObject("GC://" & Dmn, UsrID, Psw,
ADS_SECURE_AUTHENTICATION)

If Aut Is Nothing Then
AutLogin = False
Else
AutLogin = True
End If

Set Aut = Nothing
Set G_C = Nothing
Set Root = Nothing
End Function

<< The 2nd UserForm, ProgBar, conatains this code:

Private Sub UserForm_Activate()
Call Prc
Unload Me
End Sub

Stepping thru the logic, it works fine; Form 1 disappears before Form 2
appears.
Running full-bore, Form 1 remains visible behind Form 2 the entire time the
program runs. Since screen updating is off at this time, the processing is
not seen behind Form 1, which is correct.

I have tried moving the 'ScreenUpdating = False' statement further on in the
code but to no avail.
Is there, perhaps, a UserForm property or some Excel setting that would
cause this?

--
Will

wpiet
UserForm remains visible after Unload - 2003
 
That's it, exactly.
Thank, you Paul.
--
Will


" wrote:

Hi
Try throwing in a
DoEvents

after you unload Form1. Sometimes kicks the screen into action. It may
be that your form is unloaded but the screen has not updated.
regards
Paul

On Mar 12, 6:28 pm, wpiet wrote:
Every post I've found for this problem seems to have been resolved with
Application.ScreenUpdating = True. Not in this case.

With 2 UserForms, Form 1 remains visible behind Form 2, even though Form 1
was unloaded before Form 2 was loaded & shown.

The main code module (all variables are Public):

Sub ACRE_MthCtrb()

Application.ScreenUpdating = True

ThisWorkbook.Activate
FilPth = ThisWorkbook.Path & "\"

<< The 1st UserForm loads, executes & unloads in function VldLogin() .
This form & function are in a separate, hidden workbook, with a
reference
set in the current workbook.

LoginVld = VldLogin()
If Not LoginVld = True Then
ActiveWindow.Close _
SaveChanges:=False
End If

<< Here, the 2nd UserForm, ProgBar, appears. The 1st form already unloaded.

Application.ScreenUpdating = False

ProgBar.LblProgBar.Width = 0
ProgBar.Show

<< Remainder of processing is in Sub Prc(), called from UserForm ProgBar

Workbooks("ACRE Monthly Contributions.xls").Activate
Application.ScreenUpdating = True
ActiveWindow.Close _
SaveChanges:=False
End Sub

Sub Prc()
.
.
.
End of module

<< The 1st UserForm, QryLogin, loads in this module, Login:

Public Function VldLogin() As Boolean
Abt = vbNo
Load QryLogin
QryLogin.Show
If Abt = vbYes Then
VldLogin = False
Else
VldLogin = True
End If
End Function

<< Userform QryLogin contains the following code:

Private Sub OK_Click()
Dim LoginAut As Boolean

If Len(Trim(Me.UsrID)) = 0 Then
MsgBox ("You Must Enter A User Name")
Me.UsrID.SetFocus
ElseIf Len(Trim(Me.Psw)) = 0 Then
MsgBox ("You Must Enter A Password")
Me.Psw.SetFocus
Else
LoginAut = AutLogin(Me.UsrID, Me.Psw)
If LoginAut = True Then
Login.UsrID = Me.UsrID.Value
Login.Psw = Me.Psw.Value

<< Here is the Unload for the 1st form

Unload Me
Else
MsgBox ("Invalid Username or Password; Please Re-Enter")
Me.UsrID.SetFocus
End If
End If
End Sub

Function AutLogin(ByVal UsrID As String, _
ByVal Psw As String) _
As Boolean

Const ADS_SECURE_AUTHENTICATION = 1
Dim Aut As Object ' Authentication
Dim Dmn As String ' Domain
Dim G_C As Object ' Global Catalog
Dim Root As Object ' RootDSE

On Error Resume Next
Set Root = GetObject("GC://rootDSE")
Dmn = Root.Get("defaultNamingContext")
Set G_C = GetObject("GC:")
Set Aut = G_C.OpenDSObject("GC://" & Dmn, UsrID, Psw,
ADS_SECURE_AUTHENTICATION)

If Aut Is Nothing Then
AutLogin = False
Else
AutLogin = True
End If

Set Aut = Nothing
Set G_C = Nothing
Set Root = Nothing
End Function

<< The 2nd UserForm, ProgBar, conatains this code:

Private Sub UserForm_Activate()
Call Prc
Unload Me
End Sub

Stepping thru the logic, it works fine; Form 1 disappears before Form 2
appears.
Running full-bore, Form 1 remains visible behind Form 2 the entire time the
program runs. Since screen updating is off at this time, the processing is
not seen behind Form 1, which is correct.

I have tried moving the 'ScreenUpdating = False' statement further on in the
code but to no avail.
Is there, perhaps, a UserForm property or some Excel setting that would
cause this?

--
Will


All times are GMT +1. The time now is 05:14 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com