View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
Jack Sheet Jack Sheet is offline
external usenet poster
 
Posts: 47
Default 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