insert who accesses in the workbook

[xam99]

Hello everyone.
In the attached workbook there are macros in thisworkbook:

in Private Sub Workbook_Open()

 '---------------------------------------------------------------------------
 'ACCESSI
 
 Dim nomefoglio As Variant
    Dim Urec   As String

    Application.ScreenUpdating = False
    Application.EnableEvents = False            '<-aggiunta
   
    'With Sheets("ACCESSI")
       ' nomefoglio = .Cells(10000, "II")
       ' Sheets(nomefoglio).Activate             '<<< apre il foglio e sprotegge
     
    With Sheets("ACCESSI")                         '<<< apre il foglio e sprotegge
        .Unprotect "987654"
        .Cells(10000, "II") = ActiveSheet.Name
        .Unprotect "987654"
       
        'Urec = .Cells(Rows.Count, 52).End(xlUp).Row + 1
        '.Cells(Urec, 52) = Application.UserName
        '.Cells(Urec, 53) = Now
        '.Cells(Urec, 54) = "ACCESSO"
       
        Urec = .Cells(Rows.Count, 1).End(xlUp).Row + 1
        .Cells(Urec, 1) = Application.UserName
        .Cells(Urec, 2) = Now
        .Cells(Urec, 3) = "ACCESSO"
       
        .Protect "987654"
    End With
    Application.ScreenUpdating = True
    Application.EnableEvents = True             '<-aggiunta
   
     
  '---------------------------------------------------------------------------

and in:

Private Sub Workbook_BeforeClose(Cancel As Boolean)


'---------------------------------------------------------------------------
 'ACCESSI
   
    Dim nomefoglio As Variant
    Dim Urec   As String

    Application.ScreenUpdating = False
    Application.EnableEvents = False            '<-aggiunta
    With Sheets("ACCESSI")                         '<<< apre il foglio e sprotegge
        .Unprotect "987654"
        .Cells(10000, "II") = ActiveSheet.Name
       
        'Urec = .Cells(Rows.Count, 52).End(xlUp).Row + 1
        '.Cells(Urec, 52) = Application.UserName
        '.Cells(Urec, 53) = Now
        '.Cells(Urec, 54) = "FINE SESSIONE"
       
         Urec = .Cells(Rows.Count, 1).End(xlUp).Row + 1
        .Cells(Urec, 1) = Application.UserName
        .Cells(Urec, 2) = Now
        .Cells(Urec, 3) = "FINE SESSIONE"
       
        .Protect "987654"
    End With                                    '<<< ritorna al foglio
    ThisWorkbook.Save                                       'salva
    If SoloVisione = True Then ThisWorkbook.Saved = False   'se ci sono stati interventi proponi popup Salva
    Application.ScreenUpdating = True
    Application.EnableEvents = True             '<-aggiunta
             
'------------------------------------------------------------------------------------------------------

which are used to enter in the "ACCESSI" sheet whoever accesses the workbook.
Also in this workbook there is a macro:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'------------------------------------------------------------------------------------------------------
'per utente autorizzato

Dim avviso As String
Dim cercarange As Range

     

    Set cercarange = Foglio8.Range("E2:E11").Find(Foglio8.Range("F2"))
     

    If cercarange Is Nothing Then
   
   
      ' MsgBox "Value not found"
      avviso = MsgBox(Environ("UserName") & " non sei autorizzato a modificare questo workbook", vbCritical + vbDefaultButton2, "AVVISO!")

        If avviso = vbOK Then

      Me.Saved = True

Exit Sub



    ' MsgBox "Value not found"
    'ThisWorkbook.Saved = True
    ' ThisWorkbook.Close


    Else

        'MsgBox foundRng.Address

    End If


End If
'---------------------------------------------------------------------------

to authorize the use of the workbook to certain users who are in the sheet "utenti_errori" column E

There is a problem:

if changes are made and at closing I say "do not save" these changes remain.
Help to correct?
I hope I have explained.
xam

[cytop]

ignore - misread the issue

[xam99]

I badly explained myself?
Sorry for my English

[xam99]

Hello on the net I found

Private Sub Workbook_BeforeClose(Cancel As Boolean)  
    
'------------------------------------------------------------------------------

Dim info As String
    string1 = ThisWorkbook.BuiltinDocumentProperties("content status").Value
    If info = "ACCESSI" Then

        ThisWorkbook.BuiltinDocumentProperties("content status").Value = ""
        Application.DisplayAlerts = False
        ThisWorkbook.Save
    End If
    
'---------------------------------------------------------------------------

and in the sheet "AVVISI"

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    ThisWorkbook.BuiltinDocumentProperties("content status").Value = "ACCESSI"
End Sub

work well.

Now how to insert the exact variable here?

string1 = ThisWorkbook.BuiltinDocumentProperties("content status").Value

long? variant? string.......

[xam99]

Ops not work the modification the post# 4

[xam99]

Basically what I ask is that if changes are made or not to the workbook, whoever makes the changes must be entered

[cytop]

I'm lost as to some of the logic, but the attached has been chopped around a little more and rather than trying to handle everything in the Before_Close event the code has been split out so the Before_Save event is used to determine if the user can save or not.

If the user is authorised to save then it just saves and will prompt to backup immediately after (I've not looked at that, and it may not be the best place for it).

If the user is not authorised to save then the (Italian) 'You are not authorised...' message box will be displayed, the workbook Saved Flag is set to True to prevent other prompts before the next change and the procedure exits.

In the Before_Close event, it again checks and if the user is not authorised it will prompt "Edits will not be saved..." and gives a choice to continue to close or keep the workbook open (in case they might need to copy changes, or whatever).

I'm not saying this is the answer to your question but it is probably a better place to start from. To be honest, the best way to handle this would be to manipulate the ribbon and disable the Save options if the user is not authorised to save - this would greatly simplify things.

[xam99]

Hi cytop,
what I'm looking for is this

Basically what I ask is that if changes are made or not to the workbook, whoever makes the changes must be entered

[cytop]

"...if changes are made or not to the workbook, whoever makes the changes must be entered..."

I'm sorry. I'm confused but this is not what you posted in your first thread. Probably best if I leave it for someone else with a new perspective on this.

[xam99]

Thanks the same cytop.

The change is that I ask is in the post #6
I hope I have explained

[xam99]

Hello,
is it possible to save only 1 sheet?
So it doesn't work:

Sheets("info").Save