Change History with Password Protection.

[HansV]

Columns K, N and P aren't logged because there is special code for them in the Worksheet_SheetChange event procedure that prevents us from using the logging code.

[DVM]

Alright Sir, that means if logging is necessary I need to makes these cells either dorp down or manually entered. Currently I would let it be as it is. No problems on that. Thanks again and best regards, Vilas Desai

[DVM]

Dear Hans,

(coming back to the forum because I do not have any big attachments now)
I tried some research on where the error exactly slipped in by comparing the file which I sent to you and the one which I received from you (4 of them) and found that the first file that I received too gave this error. I did not notice it because I was focusing only on the Change_History code, I was only changing the old used cells to test the function. I am now so much worried as to why the same file works with you and not with me? Have you seen this happening any time with Excel applications, I remember some people saying that if things go wrong somewhere, Excel VBA files can go astray. May be something like that has struck my PC. With this thought I opened the file in another PC and to my bad luck saw the same error. Now I start thinking if version compatibility is any issue. I use Office 2007 and Windows 7.

I had actually arranged for a client demo for tomorrow (Sunday, yes, on a Sunday). I think I will go ahead without the Change_History feature (which incidentally is the best part of this project) and buy some time to see where I am going wrong.

Thank you so much for all this help that you have given me. With best regards
Vilas Desai

[HansV]

Excel and the Visual Basic engine sometimes become confused, but that will only last during the current session. Quitting and restarting Excel, or in extreme situations, restarting Windows, will end such confusion.

An alternative would be to create an entirely form-based application, but that would be a LOT of work. The advantage would be that you wouldn't be dependent on the complex interaction of worksheet events; the disadvantage would be that you'd have to code all the functionality of those events yourself. I'm not sure it would be worth the trouble...

[DVM]

If there are repetitive codes with only changes in referencing, I am prepared to do that, this was I can also learn a lot of things. So anything that is manually done, please give that work to me. This is also applicable for any work that you do in your profession.

[HansV]

I wasn't actually suggesting that you should rewrite the entire application; it was idle early-morning musing...

[DVM]

Ok, no problems. Hans, please advise me how does any one solve this problem - "I dont see the error that you are seeing"
I change my computer, I changed the browser to download the file from email, nothing could solve my problem.

[HansV]

I'm very sorry, but I have no idea.

[DVM]

Dear Hans,
Great News! I dont know what happened, but things started working great. (though this is highly amusing). I let it be that way for the moment - atleast for tomorrows demo. Thanks for your help.
Best Regards
Vilas Desai

[HansV]

I hope that the demo will go well too!

[DVM]

THANK YOU FOR YOUR WISHES

[DVM]

Dear Hans,
The demo went off very very well though there was one instance where the Runtime error cropped up. fortunately I had made 3 copies of the good file so I could switch to the next one.
I figured out that this is related to the 'Exempt Range" in the Change_History module. The Runtime error showed up when I entered a formula in Col X. Since the formula was wrong it was not evaluated and showed up the Run time error. This did not allow me to move to the next row when i hit the Tab key at the end of the table. I debugged the program and found this error which is in the attachment.
This file never worked again.Which would suggest perhaps there is something that is damaging the code permanently.
If this information can be useful to you to know what exactly is happening I could take care not to do certain things.
Pease advise
Best Regards

[HansV]

Dear Vilas,

The code in the screenshot that you sent me by e-mail is different from the code in the workbooks that you sent me earler - there is no Exempt_Range in those.

S0057.png

[vilas desai]

Dear Hans,

I have added a button for "Clear_History" in the "Change_History" worksheet which will clear the History rows. I have also assigned the same passwords (10 p/w ) That works. But I want to assign three of these 10 pw to Clear the History and not ANY of the 10 pw. Let us say the three passwords are 75291, 75292 and 75293 Second, after the rows are deleted, I want to have in the third row the following information: Col A=current Date / time, ColC: Name corresponding to the password.

In MasterList, the password gets assigned to changes even beyond the region of the table. For example Any change that I make in the Range A1:AA4 is asking for a password. Also, all cols beyond AA are also asking for password, however that may not be a problems because those cols will eventually be blocked for edition.

May I please request your advise in this.

Best regards
Vilas Desai

Code: Select all

Option Explicit

Sub Clr_History()
 Application.ScreenUpdating = False
     Worksheets("Change_History").Range("A3:G50000,F3:V50000").Clear
End Sub

[HansV]

Perhaps something like this:

Code: Select all

Sub Clr_History()
    Dim strPW As String
    Dim strName As String
    strPW = InputBox("Enter password to clear history")
    Select Case strPW
        Case "75291", "75292", "75293"
            strName = application.vlookup(...) ' see code for MasterList
            Application.ScreenUpdating = False
            With Worksheets("Change_History")
                .Range("A3:G50000,F3:V50000").Clear
                .Range("A3").Value = Now
                .Range("C3").Value = strName
            End With
        Case Else
            MsgBox "You don't have permission to clear history!", vbExclamation
    End Select
End Sub

Do you really want users to be able to change the headings in rows 1 - 4?
In the Worksheet_Change event procedure for MasterList, you could add a line near the beginning

Code: Select all

    If Target.Row < 5 Or Target.Column > 27 Then Exit Sub

[vilas desai]

Hello Hans,

I replaced (.....) with

Code: Select all

strName = Application.VLookup(strPW, wshDV.Range("PasswordList"), 2, False)

I checked that the name range is correct, I also changed strName to varUser, but it throws an error on this code line Error 424 Object Required.
For your ready reference part of the relevent Masterlist code is as below:

Code: Select all

Else
        strPW = InputBox("Enter your password")
        Set wshDV = Worksheets("DV")
        varUser = Application.VLookup(strPW, wshDV.Range("PasswordList"), 2, False)
        If IsError(varUser) Then
            MsgBox "Password invalid. Change not allowed!", vbCritical
            GoTo ExitHandler
        End If
        Target.Value = vCurValue
        Call SetHistory(varUser, vPrevValue, vCurValue, Target.Address)
    End If
    Application.EnableEvents = True

Regards
Vilas Desai

[HansV]

Have you added the lines

Code: Select all

    Dim wshDV As Worksheet
    Set wshDV = Worksheets("DV")

to the Clr_History macro? Without those, wshDV will not be valid in this macro.

[hobbes]

Hi Hans V

I have a problem similar to what you were discussing before. I tried testing your formula and I could not get it to work. I have Office 2010. Basically I have a spreadsheet ( which is attached for your reference) I want to keep protected and I have formulas in it, I lock the cells with formulas, but allow for my users to put their data in unlocked cells. However on occasion they may need to insert a row, I know how to give them that capability the formula just doesn't follow when protected.

Can you please provide me the VB codes for the same?

Thanks for your help with this.

Kind Regards
Hobbes

[vilas desai]

Dear Hobbes,

The above code works great. What problem did you face? Please advise, I am not a problem solver, but am keen to know where did it go wrong.

Regards
Vilas Desai

[HansV]

However on occasion they may need to insert a row, I know how to give them that capability the formula just doesn't follow when protected.

Welcome to Eileen's Lounge!

The following macro will
- Unprotect the sheet
- Insert a row
- Enter the formula in column M
- Protect the sheet again

Code: Select all

Sub InsertRow()
    Dim lngRow As Long
    Dim lngLastRow As Long
    lngRow = ActiveCell.Row
    lngLastRow = Range("A" & Rows.Count).End(xlUp).Row
    If lngRow < 11 Or lngRow > lngLastRow - 9 Then
        MsgBox "You can only insert a row in the body of the expenses table!", vbExclamation
        Exit Sub
    End If
    ActiveSheet.Unprotect Password:="secret"
    ActiveCell.EntireRow.Insert
    Range("M" & lngRow).FormulaR1C1 = "=SUM(RC4:RC12)"
    ActiveSheet.Protect Password:="secret"
End Sub

Change the password to the one you use.

In the attached version of the workbook, I have copied your sheet to Sheet2 since Sheet1 is protected with a password.
I have assigned the macro to a command button on the sheet and to the keyboard shortcut Ctrl+Shift+I.

Employee Expense Claim Form.xls