Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
UserInterfaceOnly on startup
My code in ThisWorkbook includes the following
Private Sub Workbook_Open() Dim lCalculationStartup As Long Unload UserForm1 ' in case it previously crashed before unloading With Application .EnableCancelKey = xlDisabled .ScreenUpdating = False .DisplayAlerts = False lCalculationStartup = .Calculation .Calculation = xlCalculationManual .EnableEvents = True On Error Resume Next 'Until CommandBar attached With .CommandBars("Project Manager") .Enabled = True .Visible = True End With '.CommandBars("Project Manager") On Error GoTo 0 With .ThisWorkbook .Worksheets("Template").Visible = xlVeryHidden .Worksheets("Clients").Protect userinterfaceonly:=True End With 'ThisWorkbook .ScreenUpdating = True .DisplayAlerts = True .Calculation = lCalculationStartup If Val(.Version) < 9 Then .Calculate Else .CalculateFull End If End With 'Application Me.Saved = True End Sub Problem: If the worksheet "Clients" is unprotected when last saved then the above routine does not protect it at all (whether userinterfaceonly or not). If the worksheet "Clients" is protected when last saved then on next opening it remains protected but not userinterfaceonly. In other words, it is as if the line .Worksheets("Clients").Protect userinterfaceonly:=True is ignored when running the Workbook_Open() routine. Any explanation for this would be gratefully received. -- Return email address is not as DEEP as it appears |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
UserInterfaceOnly on startup
Your code worked ok for me in xl2002.
If Clients was unprotected, it got protected. If clients was protected, it stayed protected. (and userinterfaceonly was applied in both cases.) Any chance you have an extra "on error resume next" that wasn't part of your post? I think excel changed behavior between xl2k and xl2002, too. In earlier versions, you could set the userinterfaceonly setting without the password. In xl2002, you need to provide the correct password. (But I got prompted for the password if I didn't include it in code.) Jack Sheet wrote: My code in ThisWorkbook includes the following Private Sub Workbook_Open() Dim lCalculationStartup As Long Unload UserForm1 ' in case it previously crashed before unloading With Application .EnableCancelKey = xlDisabled .ScreenUpdating = False .DisplayAlerts = False lCalculationStartup = .Calculation .Calculation = xlCalculationManual .EnableEvents = True On Error Resume Next 'Until CommandBar attached With .CommandBars("Project Manager") .Enabled = True .Visible = True End With '.CommandBars("Project Manager") On Error GoTo 0 With .ThisWorkbook .Worksheets("Template").Visible = xlVeryHidden .Worksheets("Clients").Protect userinterfaceonly:=True End With 'ThisWorkbook .ScreenUpdating = True .DisplayAlerts = True .Calculation = lCalculationStartup If Val(.Version) < 9 Then .Calculate Else .CalculateFull End If End With 'Application Me.Saved = True End Sub Problem: If the worksheet "Clients" is unprotected when last saved then the above routine does not protect it at all (whether userinterfaceonly or not). If the worksheet "Clients" is protected when last saved then on next opening it remains protected but not userinterfaceonly. In other words, it is as if the line .Worksheets("Clients").Protect userinterfaceonly:=True is ignored when running the Workbook_Open() routine. Any explanation for this would be gratefully received. -- Return email address is not as DEEP as it appears -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
UserInterfaceOnly on startup
Thanks for the thoughts/confirmation. I stripped out most of the code to
eliminate coding errors as the possibility, just left a msgbox in workbook_open() and it still failed to trigger. It looks like a corruption - will have to start again I think. "Dave Peterson" wrote in message ... Your code worked ok for me in xl2002. If Clients was unprotected, it got protected. If clients was protected, it stayed protected. (and userinterfaceonly was applied in both cases.) Any chance you have an extra "on error resume next" that wasn't part of your post? I think excel changed behavior between xl2k and xl2002, too. In earlier versions, you could set the userinterfaceonly setting without the password. In xl2002, you need to provide the correct password. (But I got prompted for the password if I didn't include it in code.) Jack Sheet wrote: My code in ThisWorkbook includes the following Private Sub Workbook_Open() Dim lCalculationStartup As Long Unload UserForm1 ' in case it previously crashed before unloading With Application .EnableCancelKey = xlDisabled .ScreenUpdating = False .DisplayAlerts = False lCalculationStartup = .Calculation .Calculation = xlCalculationManual .EnableEvents = True On Error Resume Next 'Until CommandBar attached With .CommandBars("Project Manager") .Enabled = True .Visible = True End With '.CommandBars("Project Manager") On Error GoTo 0 With .ThisWorkbook .Worksheets("Template").Visible = xlVeryHidden .Worksheets("Clients").Protect userinterfaceonly:=True End With 'ThisWorkbook .ScreenUpdating = True .DisplayAlerts = True .Calculation = lCalculationStartup If Val(.Version) < 9 Then .Calculate Else .CalculateFull End If End With 'Application Me.Saved = True End Sub Problem: If the worksheet "Clients" is unprotected when last saved then the above routine does not protect it at all (whether userinterfaceonly or not). If the worksheet "Clients" is protected when last saved then on next opening it remains protected but not userinterfaceonly. In other words, it is as if the line .Worksheets("Clients").Protect userinterfaceonly:=True is ignored when running the Workbook_Open() routine. Any explanation for this would be gratefully received. -- Return email address is not as DEEP as it appears -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
UserInterfaceOnly on startup
So the real problem is workbook_open() isn't running???
How do you open this troublesome workbook? If it's via code, do you disable events in the routine that opens this workbook? If it's via a shortcut key, does that shortcut key combination include the shiftkey? (If yes, try using a different shortcutkey (w/o the shift).) If you move the code to a general module and call the procedure auto_open, does it work? I've never experienced the problem, but I've seen posts that do this kind of thing: Private Sub Workbook_Open() Application.OnTime Now, "Continue_Open" End Sub Then in a General module, sub Continue_Open() 'your real code here end sub I guess the thought is to let excel do some housekeeping and get caught up. Jack Sheet wrote: Thanks for the thoughts/confirmation. I stripped out most of the code to eliminate coding errors as the possibility, just left a msgbox in workbook_open() and it still failed to trigger. It looks like a corruption - will have to start again I think. "Dave Peterson" wrote in message ... Your code worked ok for me in xl2002. If Clients was unprotected, it got protected. If clients was protected, it stayed protected. (and userinterfaceonly was applied in both cases.) Any chance you have an extra "on error resume next" that wasn't part of your post? I think excel changed behavior between xl2k and xl2002, too. In earlier versions, you could set the userinterfaceonly setting without the password. In xl2002, you need to provide the correct password. (But I got prompted for the password if I didn't include it in code.) Jack Sheet wrote: My code in ThisWorkbook includes the following Private Sub Workbook_Open() Dim lCalculationStartup As Long Unload UserForm1 ' in case it previously crashed before unloading With Application .EnableCancelKey = xlDisabled .ScreenUpdating = False .DisplayAlerts = False lCalculationStartup = .Calculation .Calculation = xlCalculationManual .EnableEvents = True On Error Resume Next 'Until CommandBar attached With .CommandBars("Project Manager") .Enabled = True .Visible = True End With '.CommandBars("Project Manager") On Error GoTo 0 With .ThisWorkbook .Worksheets("Template").Visible = xlVeryHidden .Worksheets("Clients").Protect userinterfaceonly:=True End With 'ThisWorkbook .ScreenUpdating = True .DisplayAlerts = True .Calculation = lCalculationStartup If Val(.Version) < 9 Then .Calculate Else .CalculateFull End If End With 'Application Me.Saved = True End Sub Problem: If the worksheet "Clients" is unprotected when last saved then the above routine does not protect it at all (whether userinterfaceonly or not). If the worksheet "Clients" is protected when last saved then on next opening it remains protected but not userinterfaceonly. In other words, it is as if the line .Worksheets("Clients").Protect userinterfaceonly:=True is ignored when running the Workbook_Open() routine. Any explanation for this would be gratefully received. -- Return email address is not as DEEP as it appears -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
UserInterfaceOnly on startup
Correct. Workbook_open() isn't running.
If I copy the code into Auto_Open() in general module it runs. Workbook_Close() does run. I am not using any esoteric method of opening the workbook. I have tried launching Excel and then opening the file from file/open, and I have tried doubleclicking on the excel file from Windows explorer. I am not trying to open it from a macro in another book. I shall have a look at your other suggestion below but I am fairly resigned to starting afresh as being the only way to get rid of the corruption. "Dave Peterson" wrote in message ... So the real problem is workbook_open() isn't running??? How do you open this troublesome workbook? If it's via code, do you disable events in the routine that opens this workbook? If it's via a shortcut key, does that shortcut key combination include the shiftkey? (If yes, try using a different shortcutkey (w/o the shift).) If you move the code to a general module and call the procedure auto_open, does it work? I've never experienced the problem, but I've seen posts that do this kind of thing: Private Sub Workbook_Open() Application.OnTime Now, "Continue_Open" End Sub Then in a General module, sub Continue_Open() 'your real code here end sub I guess the thought is to let excel do some housekeeping and get caught up. Jack Sheet wrote: Thanks for the thoughts/confirmation. I stripped out most of the code to eliminate coding errors as the possibility, just left a msgbox in workbook_open() and it still failed to trigger. It looks like a corruption - will have to start again I think. "Dave Peterson" wrote in message ... Your code worked ok for me in xl2002. If Clients was unprotected, it got protected. If clients was protected, it stayed protected. (and userinterfaceonly was applied in both cases.) Any chance you have an extra "on error resume next" that wasn't part of your post? I think excel changed behavior between xl2k and xl2002, too. In earlier versions, you could set the userinterfaceonly setting without the password. In xl2002, you need to provide the correct password. (But I got prompted for the password if I didn't include it in code.) Jack Sheet wrote: My code in ThisWorkbook includes the following Private Sub Workbook_Open() Dim lCalculationStartup As Long Unload UserForm1 ' in case it previously crashed before unloading With Application .EnableCancelKey = xlDisabled .ScreenUpdating = False .DisplayAlerts = False lCalculationStartup = .Calculation .Calculation = xlCalculationManual .EnableEvents = True On Error Resume Next 'Until CommandBar attached With .CommandBars("Project Manager") .Enabled = True .Visible = True End With '.CommandBars("Project Manager") On Error GoTo 0 With .ThisWorkbook .Worksheets("Template").Visible = xlVeryHidden .Worksheets("Clients").Protect userinterfaceonly:=True End With 'ThisWorkbook .ScreenUpdating = True .DisplayAlerts = True .Calculation = lCalculationStartup If Val(.Version) < 9 Then .Calculate Else .CalculateFull End If End With 'Application Me.Saved = True End Sub Problem: If the worksheet "Clients" is unprotected when last saved then the above routine does not protect it at all (whether userinterfaceonly or not). If the worksheet "Clients" is protected when last saved then on next opening it remains protected but not userinterfaceonly. In other words, it is as if the line .Worksheets("Clients").Protect userinterfaceonly:=True is ignored when running the Workbook_Open() routine. Any explanation for this would be gratefully received. -- Return email address is not as DEEP as it appears -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Userinterfaceonly | Excel Worksheet Functions | |||
UserInterfaceOnly & Password Protection | Excel Discussion (Misc queries) | |||
UserInterfaceOnly | Excel Discussion (Misc queries) | |||
UserInterfaceOnly on the Mac | Excel Programming | |||
UserInterfaceOnly on the Mac | Excel Programming |