Automatically Lock Cells In VBA Code?
Nov 12, 2013
I have a code which should automatically lock the cells when ever i change a certain value of cells. the code is given below. But the change doesn't happen. I've first protected the sheet then using code unlock and then again protect the sheet after locking the cells. I'm getting no errors but the cell doesn't get locked.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 14 Then
ActiveSheet.Unprotect Password:="password"
[Code]....
View 5 Replies
ADVERTISEMENT
Apr 23, 2008
I have an excel sheet which has a macro which captures the current time in a cell with the formula =now(). What I am looking for is that once a user runs a macro and the time is inserted into that cell then that cell should be locked and cannot be changed then. If this can be done by any formula / function or VBA.
View 2 Replies
View Related
Nov 27, 2006
I have a grid used as a shared diary and I need to automatically protect a cell by changing its colour to red, can this be done using VBA?
View 9 Replies
View Related
May 6, 2004
a code that would automatically lock a cell every time I input a value to it?
View 2 Replies
View Related
Apr 16, 2008
I am trying to apply a conditional cell lock to a worksheet. I am using Excel 2003, not 2007.
In column J, I have a list validation being used. The default value is "Not Claimed", I want to lock the cell from being edited once the value has been changed to any other value on the list.
The worksheet will be password protected to prevent entries into static fields (A:I & N:P).
I also would like to give the user the ability to unlock a specific cell, not the entire worksheet, by entering a password or something if they accidently choose a wrong value from the list.
I have column Q and beyond available if I need to have the password entered somewhere else.
I have attached a sample of the file I am working with.
View 4 Replies
View Related
May 2, 2012
I need a macro where user choose a value from validation, automatically lock the other cells and should show an error message.
Here is what I am looking for. User will choose Yes or no from cell H15 validation. If user choose "NO" from the validation then all other cells has to be locked in the workbook automatically except Cell H15 and a msg box needs to appear stating: "Select Yes from the validation".
View 3 Replies
View Related
Apr 22, 2009
I'm using the code below to lock certain cells depending on the value of other cells. The code below deals with one line of my spreadsheet only and as the spreadsheet comprises 38 data entry rows I've repeated this code 38 times in the worksheet module with the appropriate changes to row numbers.
It works, but causes much screen flickering and "thinking". I'm new to vba so no doubt I've made this code too extensive or lengthy or whatever (or just plain wrong).
Can anyone assist with suggestions on how to simplify the code and/or help with code to handle all 38 lines without repeating the routine 38 times?
View 7 Replies
View Related
Apr 9, 2009
I am trying to lock cells after today's date has passed so that no one can make changes to it after today's date has lapsed. This is for protective reasons so that people do not remove their names from reserving something after using it. Now the code should disallow locking after cell input entry when today's date hasn't passed so that changes can still be made by the user. I am trying to determine the code to do this but I have no idea as to how to do it.
Here's a scenario: I reserve something for Aprill 11, 2009. I input my name. Since it's April 9th, 2009, I am still able to make changes up and until April 11, 2009. After this date, the cell is locked and no changes can be made, except for the administrator.
View 14 Replies
View Related
Sep 25, 2009
Is there a way to automatically conditionally format each column in each row, when either open, closed or cancelled is selecting from a drop down in the Status column. For example every cell is blank in colour, but if I select open in the drop down menu for row A2, the entire row will turn Red. Just wondered if there is a way instead of individually formatting every row separately. I would like to be able to copy the formula or whatever down the entire sheet.
View 2 Replies
View Related
Mar 12, 2014
I have a workbook with dozens of filled cells.
The cells contian the file names: "filen_name.extension" (for example word.doc)
Also I have these files located in some directory.
Is there any possibility to add simoltaneously links to the cells which contain the appropriate file name.
View 2 Replies
View Related
Oct 15, 2011
how to lock a cell date automatically after the user saves changes, it's current format it is a tick box with a reference cell Today() but it need to be locked once completed,
View 5 Replies
View Related
Feb 26, 2008
I need to create a macro to where once a cell has had a formula or data inputed that it locks afterwards and cannot be edited without unportecting the sheet or not at all. The problem is I have no idea how to program in VBA. I can get there and select the worksheet but after that I am stumped. How would I enter the macro and what should the macro look like?
View 2 Replies
View Related
Jan 19, 2008
I understand that we can protect the VBA coding through check the "Lock project from viewing in the Tools>VBAProject Properties. However I would like to know if this is possible in code?
I have a mainworkbook for users that has the VBA coding protected (it contains passwords to unlock protected sheets / main workbook). In my program, the users will be entering data and then push a button which calls a macro to email a "skinny down version" of the main book to other users. Problem is, these users that receive the email will be able to get into the VBA code and see what the password is on the sheets. (even if the new workbook/worksheets are password protected) Is there any way when I save the new book to add this protection to the VBA code?
View 7 Replies
View Related
Feb 2, 2014
I have a worksheet, i want to lock the data entered in a cell of a particular column . e.g. if i will enter the data in A2, A2 should be locked, when user enter data in cell A3, A3 should be locked. I want this code to apply on multiple columns (A,C,E,G).
View 3 Replies
View Related
Mar 29, 2014
am making Excel for private use, i need the following makro to be active, here is the idea because i didnt yet work with makros at all:
If A1 = False
then Range B1:D1 will be locked cells
If A1 = True
then Range B1:D1 will be unlocked cells
Those rules apply to 1 day in the year.
the range A1:D1 will be copied about 400 so the makro should be active to each day separatly.
Check the picture attached for example of one day
View 1 Replies
View Related
Apr 18, 2008
I am trying to lock the unused cells in 32, 2 column by 7 row named ranges, based on whether or not two cells, above each range are equal or less than each other. In other words while one of the cells is less than or equal to the second cell all cells in the range below should be unlocked, as soon as that condition is no longer true the blank cells need to be locked.
I am trying to use this in the Workbook_Sheetcalculate so that the macro will run automatically.
View 3 Replies
View Related
Sep 16, 2013
I am using Excel 2010. I have been given a task at work that can save my team a lot of time if I can solve the problem. Every month, we have a spreadsheet with about 5000 rows that we have to email. In each row, there is a range that we have to email to a specific email. For example, I would have to copy and paste Range A2-R2 in the body of the email, and then email it to whatever email is in cell S2. I would then continue this for the next 5000 lines, making it a possibility that i will be sending 5000 emails manually.
I have been trying to come up with a solution through VBA that would automatically send these emails. My goal is to automatically send the Range A2-R2 to outlook email, then cell S2 into the "To" email address box, and then automatically send it. So far, i have successfully been able to send one row, but cannot figure out how to loop it for the remainder of the rows.
A couple other key points are that I have column headings as well (Range A1-R1). If possible, I want to be able to include the column headings in the email body as well. Example - first email would be range A1-R2. second email would be range A1-R1 and A3-R3, and so forth. The body of the email would also contain a standard script, such as "Please review the information below."
The goal here is to save everyone from having to send 5000+ manual emails. This would be a big boost for my team.
Code:
Sub Email()
Dim rng As Range
Dim OutApp As Object
[Code].....
View 5 Replies
View Related
Nov 7, 2006
I am aware of setting protection to certain cells by the Excel Options. which is unlocking the by defalut locked cells and then setting the sheet protect in tools->protection. I want this to be done through VBA code.
i have a template which has text in some cells and some cells are blank, i need to lock the cells which has text and keep the blank cells unlocked for user input. As this is a template it will be copied to another sheet, so i need to retain the protection properties in all new sheets (copied from the template). Attaching the template for which the protection needs to be done.As of now the sheet1 has protection property set through the toolbar properties.
View 2 Replies
View Related
Feb 8, 2014
I use the attached workbook to keep tack of feed use in turkeys. when a new flock is added to a farm (Airport View or Home farm) A template is copied and pasted from the "templates" sheet depending on what type of diet they will be on. (Ralco ABF or Ralco Standard) that template refers to the Ration sheet to calculate how much corn is in each diet. After a flock is complete I will never change the numbers again, it is just for record keeping. I want to be able to change the Corn/ton numbers in a ration (on the rations tab) if need be and not affect all the older completed records.
View 1 Replies
View Related
Oct 22, 2008
Is there a way to protect a sheet from a user dragging a cell?
View 6 Replies
View Related
Aug 1, 2014
I have used the following code to lock cells when saving.
I need to exclude Cell B2 when saving
let me know what changes i should make.
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
if ActiveSheet.Name = "Summary" Then
On Error Resume Next
'Resume to next line if any error occurs
Dim Cell As Range
With ActiveSheet
'first of all unprotect the entire
'sheet and unlock all cells
.Unprotect Password:="pw"
[code]....
View 2 Replies
View Related
May 1, 2009
W3:W300 is a protected range containing formulas returning numbers from one upwards, with two decimal places.
I need some vba to check this range and, if a number greater than 1 is found, to make the corresponding cells in Columns X and Y blank and lock them.
View 9 Replies
View Related
Jun 20, 2006
I am assigned to a very tedious task which requires me to lock selectively, cells from Jan through to Dec. Instead clicking the cells and selecting the various options from the menu bar, I would like to do it the other way.
If I want to go locking a cell upon selecting it, what are the VB codings?
View 9 Replies
View Related
Mar 12, 2007
I am trying to get user to enter month and goto sheet named the month they enter. I would also like to tag on the end of each one a 1,2,3,4 as I have four sheets four each month. Jan1, Jan2, Jan3, Jan4 etc..
Sub Cell()
Dim InptPass As String
Dim Pass As String
Dim month As String
Pass = "mypassword"
InptPass = InputBox("Please Enter Password")
If InptPass <> Pass Then
MsgBox "Incorrect Password", vbCritical
Exit Sub
Else
month = InputBox("Enter Month to Lock")
month1.Activate
ActiveSheet.Range("A1:BK22").Locked = True
MsgBox "Cells Locked", vbInformation
End If
End Sub
View 4 Replies
View Related
Oct 10, 2012
I've got a spreadsheet set up for a simple barcode scanning system. The user scans the barcode of the product when complete, and it logs it to a sheet along with a date & time timestamp. I'm then planning to use a v-lookup (barcode to product name) & pivot-table to count instances of 'product name' so that I can monitor what is being produced easier, nothing too tricky.
The problem I'm having comes from the products of the same spec having the same barcode, so data validation is hard to apply, as I can't 'not allow dupes' - the concern I have is that if the user unknowingly (or knowingly!) scans the same ticket twice, I think I've got 2 of a product when I've only got one. The idea I had was to lock all cells for a set time period (say 1 minute) after an entry so that it couldn't be accidentally scanned twice.
View 1 Replies
View Related
Mar 27, 2014
I want to copy a formula and want to lock a certain column, just like you use the dollar signs in normal formulas. Because the data in tables can't be locked with dollar signs.
Example:
example 1.xlsx
View 2 Replies
View Related
Sep 8, 2009
Basically I have recently started my own personal music chart and I'm having problems with a Year To Date page.
Basically all I want to know how to do is how to lock some cells together and rank many rows of those cells with 1 column where the total is?
I have attached the spreadsheet as a zip files as I was having problems uploading it as a xls file.
View 13 Replies
View Related
Nov 6, 2009
I would like to lock some collumns and yet change the formating on the unlocked cells mi other columns. Is there another way to protect cells other than locking cells and then protecting the worksheet?
View 5 Replies
View Related
Jul 10, 2013
I have a worksheet i want to disable
(1) save as function (if someone wants to saveas another name then it will ask for password
(2) lock all cells but unable autofilter (in my document if some one type in textbox given above it filter as typing....
My target to make it copyproof sheet and if someone copies my sheet than it is useless without password although user can only search books...
View 1 Replies
View Related
May 17, 2014
I finally have the merged cells resizing the way I need, however it seems to lock the cell after the process not allowing it to be edited again until I unlock the sheet. below is the current code I am using
[Code] .....
View 5 Replies
View Related
