Results 1 to 8 of 8

VBA lock cells based on their content

Threaded View

  1. #1
    Registered User
    Join Date
    08-06-2015
    Location
    England
    MS-Off Ver
    2013
    Posts
    68

    VBA lock cells based on their content

    Hello everyone! Because my VBA skills are almost non-existent, I don't know how to go about doing what I need to do.
    So basically, I have a workbook that will be used by multiple users. There is a sheet with an excel table in it. Some columns have formulas and some need the user to type in data. As I need to protect the formulas in the sheet, I've locked the cells with formulas and kept the rest unlocked so I can protect the sheet.

    The trouble is that in one of the columns (column Q) I have a formula which will return either a number or, if there is an error in the formula, it will return the text "Please input value". So I need this column to be locked except for cells where there is the text "Please input value". I need this formula to keep recalculating after any change in this sheet so I cannot lock the cell since the result might change.

    I came up with a code to protect the sheet with a password by adding a button on the sheet (so the user cannot change the password when protecting the sheet). The button can protect and unprotect the sheet. This is my code (I also wanted the caption on the button to change depending on whether the sheet is protected or not):

    Private Sub ToggleButton1_Click()
        ToggleButton1.Caption = "Unprotect"
        If ActiveSheet.ProtectContents = True Then
            On Error GoTo Message
            ActiveSheet.Unprotect
            ToggleButton1.Caption = "Protect"
        Else
            ToggleButton1.Caption = "Unprotect"
            ActiveSheet.Protect "Password123", AllowFiltering:=True, AllowSorting:=True
           
        End If
        Exit Sub
    Message:
            MsgBox ("Incorrect password entered. Please try again")
    End Sub
    I'm unsure if I can integrate additional code for locking cells in the above since the above code only runs when clicking on the Protect/Unprotect button and I actually need a macro to run all the time, I think, to pick up on any changes that might alter the result in cells in column Q. I also don't know if this will make the workbook significantly slower or not. But any help is appreciated. Thank you

    PS:I posted this on MrExcel as well but no luck there with finding a solution.
    Last edited by Sinon05; 11-30-2015 at 06:22 AM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Lock/unlock specific cells in a row based on another cell content of the same row
    By st_rod000 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-10-2014, 02:58 AM
  2. Automatically copy cells content based on the content of another cell.
    By chrisbarlow1984 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-01-2014, 11:13 AM
  3. Replies: 3
    Last Post: 06-28-2012, 09:05 AM
  4. Lock cells based on value of other cells - code simplification query
    By glenin in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 05-07-2009, 03:11 AM
  5. Lock cells so content or formula can't be changed
    By Rayan83 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-14-2008, 09:01 AM
  6. Lock and clear cell content based on another cell's value
    By mohitmahajanin in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-23-2008, 06:30 AM
  7. [SOLVED] How do I lock a cell based on content of another cell?
    By Alan T in forum Excel General
    Replies: 3
    Last Post: 10-23-2006, 08:57 AM

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