protectedsaving of file

wmtsub

Active Member
Joined
Jun 20, 2018
Messages
322
is there someway that i can ran a macro or a script or an object thast would not permit anyone to save my file unless they have a password other than protecting the file in wiondows? I wonder if that can be done from insiew the workbook.
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

wmtsub

Active Member
Joined
Jun 20, 2018
Messages
322
So going in a slightly different direction. Can I check for a specific user and grant them right's and any other simply exit the file?

Will this work?


'Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Application.UserName = "user name" Then ActiveWorkbook.Save else exit
End Sub
 

kpasa

Board Regular
Joined
Nov 20, 2015
Messages
82

ADVERTISEMENT

So going in a slightly different direction. Can I check for a specific user and grant them right's and any other simply exit the file?

Will this work?


'Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Application.UserName = "user name" Then ActiveWorkbook.Save else exit
End Sub
I'd use this instead for the username:
Code:
[COLOR=#454545][FONT='inherit']Function UNameWindows() As String
[/FONT][/COLOR][COLOR=#454545][FONT='inherit'][FONT='inherit']    UNameWindows = Environ("USERNAME")[/FONT][/FONT][/COLOR]
[COLOR=#454545][FONT='inherit']End Function[/FONT][/COLOR]
 

wmtsub

Active Member
Joined
Jun 20, 2018
Messages
322
I might need some more direction. If I use the code given above how di I grant that user rights to save the file - or how do I exclude everyone else? And which way makes more sense?


Function UNameWindows()
As String
UNameWindows = Environ("USERNAME")

End Function
 

kpasa

Board Regular
Joined
Nov 20, 2015
Messages
82

ADVERTISEMENT

I might need some more direction. If I use the code given above how di I grant that user rights to save the file - or how do I exclude everyone else? And which way makes more sense?

That would depend on your preference. I personally would choose the option that grants or denies access to the fewest amount of people. For example, it would be much easier if you granted access to only 1 or 2 people, rather than denying access to an enumerated list. But if your access list is larger than your deny list, then it's easier to just deny access via a blacklist.

If I were you, I'd look into maintaining a read-only list (blacklist or whitelist, whichever is smaller). Then just check your username against that list, and grant or deny accordingly.
 

wmtsub

Active Member
Joined
Jun 20, 2018
Messages
322
well for the sake of getting this started have you an example perhaps of how I would grant only one person rights? I am unsure of how to proceed.
 

kpasa

Board Regular
Joined
Nov 20, 2015
Messages
82
First, create the whitelist UAL (user access list) text file. In that file, enter all the usernames that are allowed to save. Then use this code:

Code:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If InStr(Whitelist("C:\path\to\UAL.txt"), UNameWindows) <> 0 Then
        MsgBox "Approved"
    End If
End Sub
Function UNameWindows() As String
    UNameWindows = Environ("USERNAME")
End Function
Function Whitelist(TxtPath As String)
    Dim TextFile As Integer
    Dim FilePath As String
    Dim FileContent As String
    
    FilePath = TxtPath
    TextFile = FreeFile
    Open FilePath For Input As TextFile
    FileContent = Input(LOF(TextFile), TextFile)
    Whitelist = FileContent
    Close TextFile
End Function
 

Watch MrExcel Video

Forum statistics

Threads
1,109,248
Messages
5,527,627
Members
409,777
Latest member
jamilowella

This Week's Hot Topics

Top