+ Reply to Thread
Results 1 to 3 of 3

Code Mix Up

  1. #1
    Registered User
    Join Date
    08-27-2018
    Location
    London
    MS-Off Ver
    office 2016
    Posts
    45

    Code Mix Up

    I have this file with the code to lock the cell after input with the code below

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("b9:h9, b12:h12, b19:h19, b21:h21, b31:h31")) Is Nothing Then Exit Sub
    If Target.Value = "" Then Exit Sub
    Application.EnableEvents = False
    ActiveSheet.Unprotect Password:="cream"
    Target.Locked = True
    ActiveSheet.Protect Password:="cream"
    Application.EnableEvents = True
    End Sub

    However I tried to implement this code below to clear the cell range at the end of the week with the clear button in sheet called setup

    Sub dotch()
    Application.EnableEvents = False
    With Sheets("£SAFE LOG")
    .Unprotect Password:="cream"
    With .Range("B9:H9, B12:H12, B19:H19, B21:h21, B31:H31")
    .ClearContents
    .Locked = False
    End With
    End With
    Application.EnableEvents = True
    End Sub

    This code does work to clear the cells but the cells will not lock anymore
    I tried reverting the clear code to what it was before..(the original code was throwing up errors hence the change)

    even stranger the original code now clears the cell range as well but i think that is because the locking code in Safe Log is no longer working

    Sub Rectangle3_Click()

    With Sheets("£SAFE LOG")
    .Unprotect Password:="cream"
    With .Range("B9:H9, B12:H12, B19:H19, B21:h21, B31:H31")
    .ClearContents
    .Locked = False
    End With
    End With
    End Sub

    How do I make the cell locking code work again?

    Thank you
    Attached Files Attached Files
    Last edited by diddy47; 05-02-2019 at 04:07 PM.

  2. #2
    Registered User
    Join Date
    08-27-2018
    Location
    London
    MS-Off Ver
    office 2016
    Posts
    45

    Re: Code Mix Up

    file attached

  3. #3
    Registered User
    Join Date
    08-27-2018
    Location
    London
    MS-Off Ver
    office 2016
    Posts
    45

    Re: Code Mix Up

    my mistake the code works perfectly somehow all vba scripts had been turned off, had to turn them back on using

    Open the VBA window (press ALT+F11)

    Click View -> Immediate Window


    Application.EnableEvents = True
    and press enter.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 9
    Last Post: 05-21-2018, 04:34 PM
  2. [SOLVED] Need help with VBA code. Tons of code seperated in two, second part of code doesn't work.
    By FragaGeddon in forum Excel Programming / VBA / Macros
    Replies: 20
    Last Post: 11-30-2015, 11:46 AM
  3. [SOLVED] Pattern Building VBA Code - Working code, would like to use cleaner code
    By Benisato in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-10-2015, 03:05 PM
  4. [SOLVED] Excel VB code. Message pops up while code running asking question. Code must not wait.
    By Heinrich Venter in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-28-2014, 06:10 AM
  5. [SOLVED] VBA code for assigning a numeric code to text; then numeric code populates table
    By cteaster in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-14-2014, 08:01 PM
  6. Replies: 2
    Last Post: 03-09-2013, 04:30 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1