Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Currency Format on UserForm when opened! | Excel Discussion (Misc queries) | |||
Properties window show nothing for a userform | Excel Discussion (Misc queries) | |||
cannot open the excel file, the file is already opened | Excel Discussion (Misc queries) | |||
Copying the Editing in one file to Another opened XLS file | Excel Worksheet Functions | |||
Can macro/userform run depending on HOW workbook is opened? | Excel Discussion (Misc queries) |