UserInterfaceOnly on startup

[Jack Sheet]

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.

 

[Dave Peterson]

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]

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]

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.

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.

 

[Jack Sheet]

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.

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.