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.
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?
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.
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.
Is it possible to lock all cells with the exception of a select few(say a column) based upon a date? I have a spreadsheet where I want to lock all cells with the exception of the current months information. I want the spreadsheet to auto unlock the date that is listed on the top of the spreadsheet. I have columns for all months of the yea and I have the current month listed in cell A1. I want match the A1 cell to the column header to unlock the correct column.
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"
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?
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
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.
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.
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?
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
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim Rng Dim MyCell Set Rng = Range("A1:AA7672") For Each MyCell In Rng
[Code] ....
The problem is that it is not dynamic and it's super slow. The larger my range is the slower it operates and right now it's almost unusable.
The workbook I am using gets touched by 10 different people and I want to prevent tampering. I want for the workbook to be, when protected, have all the blank cells editable and once a value is inserted, it cannot be edited again and not work so unbelievably slow.
I have a macro in Module1 of workbook "BAU" called "ExtractToSheets" which exports data depending on the contents of a cell (Thanks royUK et al)
At the moment however, you must run the macro manually to export the data.
Now, i have put some simple validation in column K of worksheet "BAU Data" within the "BAU" workbook. Whenever a cell within a predefined range with column K is changed to "Yes" i want the macro to run AND i want the row the cell is in to be locked so it cannot be edited further by the user. I have had too many users mess up previous sheets i have made to collect my data, and i wish to make this one somewhat more idiot-proof! Any pearls of wisdom folks?
I would have attached a representation of my sheet, unfortunately because there is now a macro in my spreadsheet, our trigger-happy security software won't let me post it!
I have a search form that will filter the result based on the requested category. Is it possible to lock the cells once it has been filtered and then remove the lock after they close the workbook?
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"
I believe it was yesterday this board had a post how to lock and protect cells with data and leave them unlocked and unprotected if they're blank, but I can't find it. Does anyone know where it was?
I'm using Excel '03 and I need to be able to lock certain cells after input, but only for certain users on my network. Ideally, I (administrator), would still be able to edit the cells in case of a mistake or whatever reason. I want the other users limited to adding information and not editing the existing information in a range. I would need this applied to several columns (B, C, I, and K) if this is possible.
