CODEWITHSUNDEEP
excelvba
Newbie
Newbie

Reputation: 81

Worksheet Audit Log Part 2

I have created a script which provides me with an Audit Log of changes made on my Data Tab (I would like to provide the user access to make one off changes and therefore don't want to completely lock and protect the tab).

When the user selects 1 cell this works perfectly fine, however, when the user goes to select multiple cells and attempts to delete them or change them the script crashes.

What line of code is required to either, not allow the user to select more than one cell, or if they do then produce a popup mentioning they can only select 1 cell.

Dim PreVal

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    'This sets our previous value once we have selected the cell value to change
    PreVal = Target.Value
End Sub
    
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim LastRow
    
    'If we change the cell we selected then the worksheet change event is triggered
    If Target.Value <> PreVal Then
    
        LastRow = Worksheets("Logged Changes").Cells(Rows.Count, 2).End(xlUp).Row
        
        'If the new value of the cell is not the same a previous value then logging of details begins
        Worksheets("Logged Changes").Cells(LastRow, 2).Offset(1, 0).Value = _
        Application.UserName & " changed cell " & Target.Address _
        & " from " & PreVal & " to " & Target.Value
    End If
End Sub ```

Upvotes: 1

Views: 75

Answers (1)

Samuel Everson
Samuel Everson

Reputation: 2102

You can catch this by checking the Selection.Count result.

Something like this in it's simplest form:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    'Checks for the count of selected cells and only assigns PreVal if = 1
    If Not Selection.Count > 1 Then
        PreVal = Target.Value
    Else
        MsgBox "You can only select 1 cell at a time.", VbCritical + VbOkOnly, "To Many Cells Selected!"
    End If
End Sub

You could also write it using Target.Count instead of Selection.Count.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    'Checks for the count of selected cells and only assigns PreVal if = 1
    If Not Target.Count > 1 Then
        PreVal = Target.Value
    Else
        MsgBox "You can only select 1 cell at a time.", VbCritical + VbOkOnly, "To Many Cells Selected!"
    End If
End Sub

This will only assign PreVal = Target.Value if the count of selected cells is not greater than 1 (and considering the function won't fire if nothing is selected, it will only work when 1 cell is selected).

There are a few ways to achieve preventing errors occuring with multi-cell selections, some things that come to mind are;

  • Throw the message as above but add another global variable, e.g. CellCount and then check in your Worksheet_Change event - If CellCount > 1 Then Exit Sub to prevent the code running.
  • Protect the worksheet and programmatically unprotect, apply changes and reprotect. Incorporate some checking like the point above to dictate when this code runs so that way if conditions aren't met the cell is locked.
  • Force selection to a different cell altogether so if multi-cells are selected the user doesn't have a chance to make any edits based on that range (with warnings to only select 1 cell etc.)
  • Resize the Target range using Set Target = Me.Range(Target.Resize(1, 1).Address(False, False)) which will change the Target.Address to the top left cell of the selected cells. This alone won't move or change the actuall selection on sheet but could be used to advise the user this cell will be used in place of the multi-cells selected etc.

Upvotes: 1

Related Questions