Verifying Sheet Changes

Hello Everyone,

I'm Gadi, Eric presented me in the blog last month. Starting from today I'll periodically write posts in ExcelGeek.com, it's something I promised to help Eric with, as part of our collaboration on projects. It is my pleasure to share my knowledge with others. I'll try to post neat Excel tricks, which some of you may find useful (I hope).

My first post I would like to dedicate to a solution which I lately applied at my work place, related to verifying with the user, whether the changes he made to certain critical fields in the worksheet were made deliberately or by accident. An alternative solution could have been to protect the workbook, however I did want to allow users the ability to change these values. The solution is based on a message box, that pops up whenever a sensitive cell is being changed. It prompts the user to confirm the change of the cell.

The following code has to be placed in a sheet module. Copy paste it into the modules of the sheets where you want to enable user's confirmation on any changes made to sensitive data cells. Change the VerifyRng to the range where you want the confirmation message to popup. In the following code it is set to any cell in column A.

Public Trigger As Boolean

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Resets the trigger if a new cell is selected.

    Trigger = False

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
'Verifies that the user deliberately changed data in column A.

    Dim LastRow As Integer
    Dim VerifyRng As Range
    Dim Response

    If Trigger = False Then

        Set VerifyRng = Columns(1)
        'Change this variable to your desired range
        
        If Not Intersect(Target, VerifyRng) Is Nothing Then
            Response = MsgBox("You are about to change the cell " & Target.Address(False, False) & _
                "." & Chr(13) & Chr(13) & "Are you sure you want to modify this cell?", _
                vbYesNo, "Cell Change Alert")
    
            If Response = vbNo Then
                Trigger = True
                Application.Undo
            End If
    
        End If
    
    End If

End Sub

2011 March Madness Bracket is Here!

Looking for 2014 brackets? Check here.

Ok, people. Here they are -- this year's March Madness Bracket spreadsheets. Again, there's a Master sheet for the organizers of pools, as well as a Participant version, just for people to make their picks and send them to the organizer.

Master File

Participant File

Once you've downloaded the Master file and open it (make sure you've got Macros enabled), it'll prompt you to send me a "Lock Code". When you do, I'll send you back a "Key Code" to unlock the file for use. Before I send you this code, however, you'll need to pay the $2.00 price tag.

Incidentally, it took me a bit longer that I'd planned to get these out because I was caught a bit off-guard by the NCAA's use of the four play-in games. I'd ASSUMED (yes, I know what ASSUMING can do.) that each of the four play-in winners would be the 16 seed for each of the respective quarter brackets -- EAST, WEST, SOUTHWEST, and SOUTHEAST. This was not the case, though. Two of the play-in game winners will effectively be 16 seeds (EAST & SOUTHEAST), one an 11 seed (SOUTHWEST), and one a 12 seed (EAST). If you didn't catch that, yes the EAST quarter bracket has two play-ins, meaning that the WEST has none. Very odd, I thought.

As always, let me know if you've got questions.

Insiders, if you need a Key Code, let me know. It's part of your subscription.

Happy March Madness, everyone!

Later,

Excel_Geek

Wait until this evening to download this year's March Madness spreadsheet

In an effort to try to stem the many emails, instant messages, etc., that I'll get today as people are looking for this year's version of the ExcelGeek.com March Madness Spreadsheet, let me just say this:

After all of the tournament selections have been made, and the bracket has been laid out by the Selection Committee, I'll post the two spreadsheets -- one for individual participants and the other for pool organizers. That will be late this evening.

For those of you who've decided to pay the $2.00 in advance, you'll still have to wait for the files to be posted. I plan to email you, if you've paid in advance, when the files are available. Then, when you open the "master" spreadsheet (supposing you've got your macros properly enabled), you be given a "lock code". Send this to me via email (address you may already know, but if not, it's provided in the file), and I'll send you back the associated "key code" to give you access to the content of the file.

Now...back to updating those files...