How To Lock Certain Cells If Another Cell Is Blank
Mar 8, 2014i have a situation where i want cells c13:e13 to be locked if cell g5 is blank. and same cells will be unlocked only if g5 contains text only.
View 4 Replies
ADVERTISEMENT
Lock Used Cell. Unlock Blank Cells
Nov 6, 2006I need to lock cells or ranges in a worksheet which has value (any value)....
The cells which are blank should be unlocked so that the users can enter data.
Macro To Lock All Non-Blank Cells After Saving Workbook
Jul 23, 2014I have been searching for a macro that would lock all the non blank cells (cells that have any value/text) after the workbook is saved.
When the user clicks on save a message box should prompt stating that "The data you entered has been locked successfully".
Lock & Unlock A Cell Based On Another Cells Value
Dec 20, 2007trying to put together a formula, I am trying enable two cells to change between being 'Locked' and 'Unlocked'. I am doing this by using a CheckBox which is referenced to cell [E16] so that when it is checked, 'TRUE' will be displayed and when Unchecked 'FALSE' is displayed. From this I have tried to devise a formula for the cells [c26:I26] and [K26] that when cell [E16] shows 'TRUE' the cells [c26:I26] and [K26] are Unlocked and when it shows 'FALSE' the cells [c26:I26] and [K26] then become locked.
View 3 Replies View RelatedLock/unlock Cell Based On Another Cells Input
Feb 25, 2009I want cell g12 to be locked unless cell h7 is Grass Fire or Timber Fire (H7 is a drop down validated cell)
View 8 Replies View RelatedExcel 2010 :: Lock Out Cells If A Value Is Entered In Another Cell
Mar 14, 2012I am trying to lock out cells if a value is entered in another cell in Excel 2010. For instance, when entering a value into N7 (merged Columns N7,O7,P7 into one - not sure if that matters) I want Q7 (same thing, merged Q7,R7,S7 into one) and T7 (merged T7,U7,V7 into one) to be locked out.
So, if N7 has anything typed into it, I would like Q7 and T7 (merged) to be locked.
If that's possible, I would also like for it to happen from rows N7 - N21 ...
Is this hard to do? My understanding is that it's only possible through VBA ... am I accessing the script correctly? (right click the 'sheet 1' tab [named Issues log], select 'view code').
Macro To Lock A Number Cells After Changing A Cell
Jan 5, 2009I have an excel sheet which is protected (mainly so that the users will not change any formulas) the excel sheet is basically a list
Just to put you in the picture
Column B Is PO Number
Column C Is Remarks
Column D Date
Column E Description
Column F Supplier
Column G Amount
I would like that when Column C changes to "OK Checked" column B C D E F G are protected and locked. But obviously for that particular row.
For example the input of Cell C15 changes to "OK Checked" then B15, C15,E15,F15,G15.
the macro must NOT be fixed to a particular row (in this case 15)
Leaving Blank Cells Blank In Dragging Formula Combining Different Formulas In One Cell?
Aug 2, 2014I can't modify my formula to leave blank cells blank when dragging it down, Also, I've got two formulas that i need to combine. Please view the comments I've put in cells E4, F2,F3,H2 and I2 to understand clearly what am seeking. See the attached worksheet.
View 8 Replies View RelatedIF Statement To Leave Cell Blank If Multiple Cells Are All Blank?
Mar 12, 2014I am looking for an IF statement that would leave a balance cell blank if both the revenue and expense cells are blank, otherwise a formula would be calculated.
View 8 Replies View RelatedHow To Populate Blank Cells With Sequence Until Non Blank Cell Is Encountered
Mar 22, 2014I presume this is fairly simple to do, since it's certainly easy enough to do manually by filling in a couple of rows and dragging them down, but I need it to be performed in a macro that I can run before other macros run.
What I need specifically is for the macro to go to G1 and insert the number .01... Then go to G2 and insert .02... Then G3 and insert .03... And repeat this until it finds the first non-blank cell ( row number this occurs at varies), at which point it ends and does nothing to that populated cell or any other cell in the column thereafter (including other blanks farther down).
This all needs to be done in Arial, 10pt, white.
List - Filling In Each Blank Cells With Value Contained In First Non-blank Cell Above It
Feb 27, 2013I have a list that looks something like this:
Column B
Row 4 Item 1
Row 5 Item 2
Row 6
Row 7
Row 8 Item 3
Row 9
Row 10 Item 4
Row 11
Row 12
Row 13
Row 14 Item 5
The range of cells in column B containing the items has a name "ColStreams"
I need to go through the list, filling in each blank cells with the value contained in the first non-blank cell above it - so, in this case, rows 6 and 7 would contain "Item 2", row 9 would contain "Item 3", rows 11-13 would contain "Item 4" and so on.
Replace #VALUE! Error For Blank Cells With Blank Cell
Nov 17, 2008The formula below calculates appropriately, however, if any of the cells (E12,E14, E21, E22, E28, E29) are blank, it returns a #VALUE! error. I would like the cell to remain blank. How can I do this? The formula is listed below.
=(SUM(E34-E6)-(SUM((E12)+E14)-(SUM((E21)+E22)-(SUM(E28)+E29))))
How To Make Macro To Lock Certain Cells Connected To Another Cells Value Then Copy Daily
Mar 29, 2014am 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
Macro To Combine Cells In A Row To 1 Cell While Skipping Blank Cells
Jul 2, 2014I need to combined all non blank cells in a given row into 1 cell. But within each row i have 5 phases that the values fall into, which is denoted by the 1st charter 1, 2, 3, 4, or 5. for example, in a given row i have 1-a, 1-c, 2-d, and 1-f and these values occurs in non consecutive columns starting from G to ALR. I need a macro that sorts these values in one of the 5 phases. So in another sheet the macro would combined [1-a 1-c 1-f] in Phase 1 and [2-d] in phase 2. and if there are other phases it would put them in the appropriate cell.
See attached workbook : Work Order Summary Sheet.xlsx
Macro To Combine Cells In Row To 1 Cell While Skipping Blank Cells
Jul 17, 2014However, in addition to what the macro already does, it is possible to add another work sheet in the same work book that outputs the values in separate cells? It also has to output the data in order: for example, in the first work sheet the data is inputted at random and has spaces but the macro will have to remove all the blank cells and output them in order based on the first value in the test string, 1, 2, 3, 4, and then 5;
input:
blank 3-x Blank 1-y blank 2-z 1-k
output:
1-k 1-x 2-z 3-y
Lock Cells Based On Value Of Other Cells - Code Simplification Query
Apr 22, 2009I'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?
Lock Empty Cells In Range Based On Other Cells Content
Apr 18, 2008I 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.
Lock Certain Cells By Vba
Nov 7, 2006I 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.
Ignore Blank Cells And Truly Blank Cells In Named Range?
Jan 13, 2014Ok so my named range looks like this:
[Code]....
However, I want to ignore the "" cells and the truly blank cells... However, I think all of them will have "" since I have this formula in all of the ones I'm putting in the range:
[Code]....
How do I go about getting these results into a named range so I can use it on validation since validation only seems to ignore truly blank cells and not the "" ones.
Lock Cells After Done With Formula?
Feb 8, 2014I 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 RelatedLock Dragging Of Cells
Oct 22, 2008Is there a way to protect a sheet from a user dragging a cell?
View 6 Replies View RelatedLock Cells When Saving?
Aug 1, 2014I 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]....
Lock Cells On Condition
May 1, 2009W3: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.
Lock Cells Upon Selection
Jun 20, 2006I 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?
Lock Cells Over Four Worksheets In One Go
Mar 12, 2007I 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
Zero Amount If Cell C14 Is Blank AND Add Cells
Apr 15, 2008I need a formula that will give a zero amount if cell C14 is blank AND add cells C14 & C13 if cell C14 & C13 are not blank AND add Cells C14 & C12 if Cell C13 is blank but cell C14 is not.
I tried this formula but it will not work: ....
Re-Calc From New Cell If So Many Cells Are Blank
Nov 11, 2008I have 2 columns.
Column E has the amount of time each day.
Column G is where I want the Sum to be displayed.
I need Column G to do a =Sum(E4:E33), but if anywhere in between E4 and E33 there is a period of 5 days where it totals 0 then I want column G to re-add from the first day where there is a amount in it.
Ex: E4 to E10 totals 7 (1 per day), from E11 to E15 totals 0, from E16 to E33 totals 18 (1 per day again).
In Column G at G10 it should total 7, at G11 it should be 6, G12 5 etc until it hits G15 where it sees the 5 previous days nothing was entered so it enters a value of 0, because G15 is set to 0, G16 will start adding from E16 till E33, until somewhere down the line it sees another period of 5 days where it adds up to 0 again.
Count Cells Until First Non-blank Cell
Feb 15, 2009So what I have is an unknown number of rows, starting at row 2. The first row (row 2) contains information from A2:M2 but all rows after this in column M will be blank up to a particular row. What I want to do, is count how many rows are blank in the column M up to the last blank cell and fill this with a specific word. I have attached a basic layout workbook to show what I want. There is a comment on cell M3.
View 2 Replies View RelatedFilling In Blank Cells With Value From Another Cell
Apr 9, 2009What I would like to do is to take the name values in column B and fill that into the region of blank cells below. I know that a special cells function could probably do this paired with a looping structure. I'm new to VBA and am trying to learn it so that I can use it comfortably.
View 8 Replies View RelatedIgnore One Of The Cells (P29) If Cell Value Is Zero Or Blank?
Dec 13, 2011I have a spreadsheet and need to ignore one of the cells (P29) if cell value is Zero or Blank and display a blank cell which in this case is (Q2
My current formula is
=SUM($P$19:P29)/2