Userform won't show when file re-opened
 

I have a password validation file that will execute code to open another file
when successful. User ID and Password are input in a userform when file
opens. My problem is after successful execution, if I re-open the file in
the same Excel session, the userform does not show. Not certain why this is.

Following code runs when workbook is opened:

Option Explicit

Private Sub workbook_open()

Application.EnableCancelKey = xlDisabled
Application.ScreenUpdating = False
Windows("ABS Open1.xls").Visible = True
Application.Run ("HideAll")
Windows("ABS Open1.xls").Visible = False
Application.ScreenUpdating = True
Application.Run ("Start")

End Sub

VBA Macro "Start" is modular. Code is:

Option Explicit

Private Sub Start()

Password_Validation.Show

End Sub

This code executes when command button is pressed on userform:

Private Sub cmbEnter_Click()

Dim UserName
Dim Password
UserName = txbUserName.Value
Password = txbPassword.Value

Application.EnableEvents = False
Application.ScreenUpdating = False
Windows("ABS Open1.xls").Visible = True
Application.Run ("UnhideAll")

On Error GoTo errorhandler

With Password

If Password =
Application.WorksheetFunction.VLookup(txbUserName. Value, _
Range("'Users'!PassTable"), 2, False) Then

If UserName = "admin" Then
Unload Me
Application.EnableEvents = True
Exit Sub
End If

With Worksheets("Cost Center")
Range("'Cost Center'!D1").Value = UserName
Range("'Cost Center'!B3").AutoFilter Field:=1, Criteria1:="x"
End With

Windows("ABS Open1.xls").Visible = False
Unload Me
Select_Cost_Center.Show
Exit Sub

End If

End With

errorhandler:
MsgBox ("User Name or Password Invalid. Please try again.")
txbUserName.Value = ""
txbPassword.Value = ""
Me.txbUserName.SetFocus
Windows("ABS Open1.xls").Visible = False

End Sub

Not sure why userform will not show when file is closed then re-opened in
same Excel session? If I restart Excel, everything works fine.
--
Thanks,

Gerry O.

Userform won't show when file re-opened
 

As a guess you are having an issue with Events... You turn them off in
cmbEnter_Click() without turning them back on again (they might get turned on
but only if UserName = "admin")... Events are a persistent setting so you
need to be very careful with it...
--
HTH...

Jim Thomlinson


"Gerry O" wrote:

I have a password validation file that will execute code to open another file
when successful. User ID and Password are input in a userform when file
opens. My problem is after successful execution, if I re-open the file in
the same Excel session, the userform does not show. Not certain why this is.

Following code runs when workbook is opened:

Option Explicit

Private Sub workbook_open()

Application.EnableCancelKey = xlDisabled
Application.ScreenUpdating = False
Windows("ABS Open1.xls").Visible = True
Application.Run ("HideAll")
Windows("ABS Open1.xls").Visible = False
Application.ScreenUpdating = True
Application.Run ("Start")

End Sub

VBA Macro "Start" is modular. Code is:

Option Explicit

Private Sub Start()

Password_Validation.Show

End Sub

This code executes when command button is pressed on userform:

Private Sub cmbEnter_Click()

Dim UserName
Dim Password
UserName = txbUserName.Value
Password = txbPassword.Value

Application.EnableEvents = False
Application.ScreenUpdating = False
Windows("ABS Open1.xls").Visible = True
Application.Run ("UnhideAll")

On Error GoTo errorhandler

With Password

If Password =
Application.WorksheetFunction.VLookup(txbUserName. Value, _
Range("'Users'!PassTable"), 2, False) Then

If UserName = "admin" Then
Unload Me
Application.EnableEvents = True
Exit Sub
End If

With Worksheets("Cost Center")
Range("'Cost Center'!D1").Value = UserName
Range("'Cost Center'!B3").AutoFilter Field:=1, Criteria1:="x"
End With

Windows("ABS Open1.xls").Visible = False
Unload Me
Select_Cost_Center.Show
Exit Sub

End If

End With

errorhandler:
MsgBox ("User Name or Password Invalid. Please try again.")
txbUserName.Value = ""
txbPassword.Value = ""
Me.txbUserName.SetFocus
Windows("ABS Open1.xls").Visible = False

End Sub

Not sure why userform will not show when file is closed then re-opened in
same Excel session? If I restart Excel, everything works fine.
--
Thanks,

Gerry O.

Userform won't show when file re-opened
 

Thanks for the quick response. This did indeed appear to be the problem. I
added to code to re-enable events and it now works just fine.

--
Thanks,

Gerry O.


"Jim Thomlinson" wrote:

As a guess you are having an issue with Events... You turn them off in
cmbEnter_Click() without turning them back on again (they might get turned on
but only if UserName = "admin")... Events are a persistent setting so you
need to be very careful with it...
--
HTH...

Jim Thomlinson


"Gerry O" wrote:

I have a password validation file that will execute code to open another file
when successful. User ID and Password are input in a userform when file
opens. My problem is after successful execution, if I re-open the file in
the same Excel session, the userform does not show. Not certain why this is.

Following code runs when workbook is opened:

Option Explicit

Private Sub workbook_open()

Application.EnableCancelKey = xlDisabled
Application.ScreenUpdating = False
Windows("ABS Open1.xls").Visible = True
Application.Run ("HideAll")
Windows("ABS Open1.xls").Visible = False
Application.ScreenUpdating = True
Application.Run ("Start")

End Sub

VBA Macro "Start" is modular. Code is:

Option Explicit

Private Sub Start()

Password_Validation.Show

End Sub

This code executes when command button is pressed on userform:

Private Sub cmbEnter_Click()

Dim UserName
Dim Password
UserName = txbUserName.Value
Password = txbPassword.Value

Application.EnableEvents = False
Application.ScreenUpdating = False
Windows("ABS Open1.xls").Visible = True
Application.Run ("UnhideAll")

On Error GoTo errorhandler

With Password

If Password =
Application.WorksheetFunction.VLookup(txbUserName. Value, _
Range("'Users'!PassTable"), 2, False) Then

If UserName = "admin" Then
Unload Me
Application.EnableEvents = True
Exit Sub
End If

With Worksheets("Cost Center")
Range("'Cost Center'!D1").Value = UserName
Range("'Cost Center'!B3").AutoFilter Field:=1, Criteria1:="x"
End With

Windows("ABS Open1.xls").Visible = False
Unload Me
Select_Cost_Center.Show
Exit Sub

End If

End With

errorhandler:
MsgBox ("User Name or Password Invalid. Please try again.")
txbUserName.Value = ""
txbPassword.Value = ""
Me.txbUserName.SetFocus
Windows("ABS Open1.xls").Visible = False

End Sub

Not sure why userform will not show when file is closed then re-opened in
same Excel session? If I restart Excel, everything works fine.
--
Thanks,

Gerry O.