Attribute VB_Name = "RROExcelClientWork"

Function ProtectSheet(ByVal nMode As Long, ByVal oSheet As Worksheet) As Long
    Dim cWord As String
    
    On Error GoTo ErrMsg

    cWord = ScriptGet(oSheet, "protect_word")
    nMode = VBA.IIf(cWord = "", 0, nMode)
    Select Case nMode
    Case 1
        If VBA.Val(Application.Version) <= 9 Then
            oSheet.Protect Password:=cWord, DrawingObjects:=True, Contents:=True, Scenarios:=True , UserInterfaceOnly := True
        End If
        If VBA.Val(Application.Version) > 9 Then
            Call ProtectSheet9(oSheet, cWord)
        End If
    Case 2
        oSheet.Unprotect Password:=cWord
    End Select
    
    ProtectSheet = nMode
    Exit Function

ErrMsg:
    ProtectSheet = 0
    Call VBA.MsgBox("Номер: " + VBA.str(Err.Number) + VBA.vbNewLine _
        + "Источник: " + Err.Source + VBA.vbNewLine _
        + "Описание: " + Err.Description, VBA.vbOKOnly + VBA.vbCritical, "Ошибка")
End Function

' ProtectSheet9
Sub ProtectSheet9(ByVal oSheet As Worksheet, ByVal cWord As String)
    oSheet.Protect Password:=cWord, DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True, UserInterfaceOnly := True
End Sub
' Проверка на наличие скрипта cIDScript на листе oSheet
Function ScriptExists(ByVal oSheet As Worksheet, ByVal cIDScript As String) As Boolean
    Dim bRes As Boolean, n As Long
    bRes = False
    cIDScript = VBA.Trim(cIDScript)
    
    For n = 1 To oSheet.Scripts.Count
        If VBA.UCase(VBA.Trim(oSheet.Scripts(n).ID)) = VBA.UCase(VBA.Trim(cIDScript)) Then
            bRes = True
        End If
    Next n
    
    ScriptExists = bRes
End Function

' Получение текста скрипта cIDScript на листе oSheet
Function ScriptGet(ByVal oSheet As Worksheet, ByVal cIDScript As String) As String
    Dim cRes As String

    cRes = ""
    cIDScript = VBA.Trim(cIDScript)
    
    If ScriptExists(oSheet, cIDScript) Then
        cRes = VBA.Trim(oSheet.Scripts(cIDScript).ScriptText)
    End If
    
    ScriptGet = cRes
End Function

' Добавление текста скрипта cIDScript в лист oSheet
Sub ScriptSet(ByVal oSheet As Worksheet, ByVal cIDScript As String, ByVal cValueScript As String)
    cIDScript = VBA.Trim(cIDScript)
    cValueScript = VBA.Trim(cValueScript)
    
    If Not ScriptExists(oSheet, cIDScript) Then ' если скрипт cIDScript отсутствует
        oSheet.Scripts.Add Location:=2, Language:=2, ID:=cIDScript, ScriptText:=cValueScript
    Else
        oSheet.Scripts(cIDScript).ScriptText = cValueScript
    End If
End Sub