CODEWITHSUNDEEP
excelvba
Joe Messingschlager
Joe Messingschlager

Reputation: 11

Is there a VBA process that can check for cell property change

I have a spreadsheet where all entry must be entered and edited via vba Userform. I have a process that checks if the user types directly in a cell and tells them that's a no-no and undoes their typing. This trap still allows the user to change the attributes of the cell, like color and text bolding. Is there a way to trap for that as well as direct typing in the cell?

I use the Worksheet_Change(ByVal Target As Range) to trap typing as below. The "updateIND" is a value (1 or 0) that toggles this function on or off:

Private Sub Worksheet_Change(ByVal Target As Range)
  If ThisWorkbook.Sheets("Updates").Range("updateIND") = 0 Then
    With Application
      .EnableEvents = False
      .Undo
      .EnableEvents = True
      End With
    MsgBox ("No direct typing please")
    End If
End Sub

What I would like is for my process to catch any direct change to the cell by the user.

Upvotes: 0

Views: 159

Answers (2)

Joe Messingschlager
Joe Messingschlager

Reputation: 11

The better solution was as suggested - use Protect for the worksheet. (I enhanced the protect routine to allow Autofilter to work from the headings and to allow the hiding and unhiding of columns.1):

Sub ProtectIt()
Dim myPassword As String
myPassword = "openopen"
Sheets("DataTable").Protect Password:=myPassword, _
  DrawingObjects:=True, _
  Contents:=True, _
  Scenarios:=True, _
  AllowSorting:=True, _
  AllowFiltering:=True, _
  AllowUsingPivotTables:=True, _
  AllowFormattingColumns:=True
End Sub

Sub UnprotectIt()
Dim myPassword As String
myPassword = "openopen"
Sheets("DataTable").Unprotect Password:=myPassword
End Sub

There are some many elegant and smart suggestions here. I do understand that some applications need passwords well hidden from the users. In my case, I just want them to follow the data entry rules. If they were to find the password, unprotect the tab, and save it, it wouldn't make any changes to the server copy because saves are only done via the macro, manual saves are discarded with the a replication macro that refreshes the table in the client copy. I still appreciate the comments on passwords if I should need a better method in the future.

Upvotes: 1

AJD
AJD

Reputation: 2438

The comments have already alluded to the use of protection. In your OP you suggest admonishing the user if they tried to enter anything, but a better approach is to stop them from doing it at all.

From the Microsoft site, the syntax for the Protect command is:

expression.Protect (Password, DrawingObjects, Contents, Scenarios, UserInterfaceOnly, AllowFormattingCells, AllowFormattingColumns, AllowFormattingRows, AllowInsertingColumns, AllowInsertingRows, AllowInsertingHyperlinks, AllowDeletingColumns, AllowDeletingRows, AllowSorting, AllowFiltering, AllowUsingPivotTables)

I have bolded one of the parameters because this is important to what I am about to suggest.

You completely lock the sheet for any user entry, but you allow macros to make the change. This way, if they tried to edit anything they just can't; but when they use the form, the changes are made.

Private Const UPDATES_PASSWORD As String = "DumbPassword"

Sub ProtectUpdateSheet(Optional private as Boolean)
    ThisWorkbook.Sheets("Updates").Protect UPDATES_PASSWORD, True, True, True, _
            True, False, False, False, False, False, False, False, False, _
            False, False, False)
            ' The True on the second line sets it to user interface only

Sub UnprotectProtectUpdateSheet(Optional private as Boolean)
    ThisWorkbook.Sheets("Updates").Unprotect Password:=UPDATES_PASSWORD
End Sub

This is an important note: When you save an Excel file with protected sheets, it does not save the "interface only" flag! So this has to be reset every time you open the workbook - which is now very simple:

' In ThisWorkbook code module
Private Sub Workbook_Open()
    ProtectUpdateSheet
End Sub

With this in place, you can run code whenever you want to change the sheet, but the user can't do anything directly!

Testing note: I have coded the above from memory. but I have used this approach on a work system and it is quite robust and user-safe.

@MathieuGuindon has highlighted the use of Option Private Module. The use of the Optional Boolean above is another way of preventing the routines from showing the "Macros" dialog on the developer's ribbon. This way the routines are still available to other projects but not visible to the users.

Upvotes: 0

Related Questions