Give permission to users to change data in Excel using VBA code [VBA]

[vbacoder1962]

Hi vbacoder1962
Can you share either the unlocked file or the link to the "unprotected" site, so that I can have a look at the vba-code?
I'm planning to publish (inside my company) a long list of (a lot of) users and their usage of their transactions in our ERP-system, with the request to tick all the transactions that they will keep on using in the future. If I can publish this list as a shared xls-list where anyone can only edit his own data, this would reduce the error-rate significantly. I'm not looking for a hacker-proof solution. With th VBA I can auto-generate this list, instead of editing all cells by hand.
Thnx in advance for any help.

Yeah, sure, the code goes like this, and I have tested it quite a few times, as it is reproduced below:

Option Explicit

Private Const PAGE_EXECUTE_READWRITE = &H40

Private Declare Sub MoveMemory Lib "kernel32" Alias "RtlMoveMemory" _
                               (Destination As Long, Source As Long, ByVal Length As Long)

Private Declare Function VirtualProtect Lib "kernel32" (lpAddress As Long, _
                                                        ByVal dwSize As Long, ByVal flNewProtect As Long, lpflOldProtect As Long) As Long

Private Declare Function GetModuleHandleA Lib "kernel32" (ByVal lpModuleName As String) As Long

Private Declare Function GetProcAddress Lib "kernel32" (ByVal hModule As Long, _
                                                        ByVal lpProcName As String) As Long

Private Declare Function DialogBoxParam Lib "user32" Alias "DialogBoxParamA" (ByVal hInstance As Long, _
                                                                              ByVal pTemplateName As Long, ByVal hWndParent As Long, _
                                                                              ByVal lpDialogFunc As Long, ByVal dwInitParam As Long) As Integer

Dim HookBytes(0 To 5) As Byte
Dim OriginBytes(0 To 5) As Byte
Dim pFunc As Long
Dim Flag As Boolean

Private Function GetPtr(ByVal Value As Long) As Long

    GetPtr = Value

End Function

Public Sub RecoverBytes()

    If Flag Then MoveMemory ByVal pFunc, ByVal VarPtr(OriginBytes(0)), 6

End Sub

Public Function Hook() As Boolean
   
    Dim TmpBytes(0 To 5) As Byte
    Dim p As Long
    Dim OriginProtect As Long

    Hook = False

    pFunc = GetProcAddress(GetModuleHandleA("user32.dll"), "DialogBoxParamA")


    If VirtualProtect(ByVal pFunc, 6, PAGE_EXECUTE_READWRITE, OriginProtect) <> 0 Then

        MoveMemory ByVal VarPtr(TmpBytes(0)), ByVal pFunc, 6
        If TmpBytes(0) <> &H68 Then

            MoveMemory ByVal VarPtr(OriginBytes(0)), ByVal pFunc, 6

            p = GetPtr(AddressOf MyDialogBoxParam)

            HookBytes(0) = &H68
            MoveMemory ByVal VarPtr(HookBytes(1)), ByVal VarPtr(p), 4
            HookBytes(5) = &HC3

            MoveMemory ByVal pFunc, ByVal VarPtr(HookBytes(0)), 6
            Flag = True
            Hook = True
        End If
    End If

End Function

Private Function MyDialogBoxParam(ByVal hInstance As Long, _
                                  ByVal pTemplateName As Long, ByVal hWndParent As Long, _
                                  ByVal lpDialogFunc As Long, ByVal dwInitParam As Long) As Integer

    If pTemplateName = 4070 Then
        MyDialogBoxParam = 1
    Else
        RecoverBytes
        MyDialogBoxParam = DialogBoxParam(hInstance, pTemplateName, _
                                          hWndParent, lpDialogFunc, dwInitParam)
        Hook
    End If

End Function


Sub UnProtected()

    If Hook Then
        MsgBox "VBA Project is unprotected!", vbInformation, "*****"
    End If

End Sub

And BTW, I am a qualified Forensic Accounting and Fraud Prevention expert, certified by the Institute of Chartered Accountants of India (ICAI), so obviously I am also NOT a hacker, nor do I ever hope to be!!

 

[Marc L]

coding the username and password into the thisworkbook module AND having a hidden worksheet used as a database for the passwords.

The only real protection within Excel is at workbook opening level since Excel 2013 (or 2010 if up to date) …​

​

Signed : vbacoder1966 (previsional birth was for June so 666 aka Marc Hell aka Marc L)​

 

[JWTANT]

Yeah, sure, the code goes like this, and I have tested it quite a few times, as it is reproduced below:


And BTW, I am a qualified Forensic Accounting and Fraud Prevention expert, certified by the Institute of Chartered Accountants of India (ICAI), so obviously I am also NOT a hacker, nor do I ever hope to be!!

Thank U very much,

It worked like a charm

 

[vbacoder1962]

I was able to unlock the VBA Project, detect the sheet password, unprotect the worksheet, so that pretty much allows me to do what I want with the worksheet WHILE IT IS UNPROTECTED. Problem is that when u close the file, sheet gets protected again unless you remove the code (either by saving as xlsx or by removing the code manually. The procedure used to unlock the VBA Project is called "UnProtect" and is available over a simple google search for "Code to unprotect vba project". But that does not answer the question here, does it? In any case, here is the unprotected VBA Project workbook, for whatever it was worth. Cheers !!