31492

Protect non-empty cells VBA

I've added VBA code which will insert either the time or date to a cell upon double clicking. I managed to get that going pretty well.

The bit I'm struggling with is getting the cell to protect and lock after the time/date has been entered.

I've got to a point where when I double click/try to edit a non-empty cell, I get a runtime error. Upon debugging, the line that throws me up is "Target.Formula = Format(Now, "ttttt")".

I'm also not able to throw an error message either.

I'm so close!

Any advice would be truly appreciated!

My code:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) If Not Intersect(Target, Range("C:E")) Is Nothing Then Cancel = True Target.Formula = Format(Now, "ttttt") End If If Not Intersect(Target, Range("A:A")) Is Nothing Then Cancel = True Target.Formula = Format(Now, "dd/mm/yyyy") End If End Sub Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ErrorHandler Dim xRg As Range Set xRg = Intersect(Range("A:A,C:E"), Target) If xRg Is Nothing Then Exit Sub Target.Worksheet.Unprotect Password:="123" xRg.Locked = True Target.Worksheet.Protect Password:="123" Exit Sub ErrorHandler: MsgBox "Cell already filled" Resume Next End Sub

Answer1:

The reason for your error is that the sheet is locked until some change happens on the worksheet, so if you remove the Worksheet_Change event and have your code as follows then it should work:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Target.Worksheet.Unprotect Password:="123" If Not Intersect(Target, Range("C:E")) Is Nothing Then If Target.Value = "" Then Cancel = True Target.Formula = Format(Now(), "ttttt") End If End If If Not Intersect(Target, Range("A:A")) Is Nothing Then If Target.Value = "" Then Cancel = True Target.Formula = Format(Now, "dd/mm/yyyy") End If End If Target.Worksheet.Protect Password:="123" End Sub

Answer2:

Protect your worksheet once with the UserInterfaceOnly:=True parameter and you won't have to unprotect/protect to alter cell contents with VBA.

sub protectOnce() worksheets("sheet1").unprotect password:="123" worksheets("sheet1").protect password:="123", UserInterfaceOnly:=True end sub

Recommend

  • CSS multi level menu with nested list
  • How to delete column from range if cell contains specific value in VBA/Excel
  • What exactly is the height of modalPresentationStyle - FormSheet on iPad?
  • Rails redirect_to another controller method throwing “Template missing”
  • NativeQuery Spring Data return object
  • Is it mandatory to have a doGet or doPost method?
  • calculating number of bytes of each row in an image
  • Excel distinct count with conditions
  • multidatatrigger with multibinding in ControlTemplate.Triggers
  • Authentication failed with Azure Active Directory in Windows Phone
  • Spring integration inbound-gateway Fire an event when queue is empty
  • Cloud Code function running twice
  • Debugging VB6 Code From Visual Studio 2010
  • Creating Java object from class name with constructor, which contains parameters [duplicate]
  • Thread safety of a fluent like class using clone() and non final fields
  • Converting a WriteableBitmap image ToArray in UWP
  • How to use RequestBodyAdvice
  • debug library loaded with ctypes using gdb
  • NHibernate Validation Localization with S#arp Architecture
  • Debugging ASP.NET on a built-in web server suddenly stops
  • How can I send an e-mail from a vbs script
  • Using $this when not in object context
  • Array.prototype.includes - not transformed with babel
  • How do I fake an specific browser client when using Java's Net library?
  • How reduce the height of an mschart by breaking up the y-axis
  • Accessing IRQ description array within a module and displaying action names
  • Shallow update not allowed (git > 1.9)
  • Perl system calls when running as another user using sudo
  • Font Awesome Showing Box instead of Icons
  • jquery mobile loadPage not working
  • Properly structure and highlight a GtkPopoverMenu using PyGObject
  • How to include full .NET prerequisite for Wix Burn installer
  • Hits per day in Google Big Query
  • FormattedException instead of throw new Exception(string.Format(…)) in .NET
  • Getting Messege Twice Using IMvxMessenger
  • Qt: Run a script BEFORE make
  • Linking SubReports Without LinkChild/LinkMaster
  • Is it possible to post an object from jquery to bottle.py?
  • XCode 8, some methods disappeared ? ex: layoutAttributesClass() -> AnyClass
  • Python/Django TangoWithDjango Models and Databases