Unlock Cell Range If Row Is Blank

Nov 9, 2012

I have a worksheet with active range A9 thru K200 that is locked. When worksheet is opened, I need it to automatically unlock all rows that are blank, for users to input data. It would be great if this could also require data in column A, C and K before allowing workbook to be saved and closed.

View 2 Replies


ADVERTISEMENT

Lock Used Cell. Unlock Blank Cells

Nov 6, 2006

I 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.

View 5 Replies View Related

Unlock Columns If Column Is Not Blank In Any Row

Jan 30, 2009

Unlock columns if Column is not blank in any row

Using VBA
On Sheet("Draft Final") , search every row and

If Col A is not blank, then unlock Columns G: M and unlock Columns O:U

View 4 Replies View Related

Unlock Blank/Empty Cells

Jul 4, 2007

Is it possible to unlock Blank or Empty cells within a Range?

" for example; all cells in the range (A1:F10) > 0 .locked = False "

I have some code from something simmilar i have added below:


Sub test_Click()

Dim rngTemp As Range

For Each rngTemp In Range("grey").Cells
If len(rngtemp.value) > 0 Then
LockMatchedCells rngTemp, Range("against")
endif
Next

End Sub

View 9 Replies View Related

Unlock Cell Using Code

Mar 6, 2009

code that will unlock cell g12 and af9 when cell e9 equals "WF-"

Basically I need it to say (I really don't know how to write code)
If "E9"="WF-" unlock "G12" and "AF12"
If not, clear contents of "G12" and "AF12"

View 11 Replies View Related

Unlock A Cell With A Button

Mar 19, 2009

I want to let the user unlock certain cells but only after they have clicked a button. To notify that the cell is unlocked I also want it to change colour.
Is this possible?

I have attached a small example. Password is mg

unlock.xlsx

View 6 Replies View Related

Lock / Unlock Excel Cell Based On Contents Of Another Cell?

Sep 12, 2013

How to Lock/Unlock an Excel Cell Based on the Contents of Another Cell?

View 1 Replies View Related

Unlock Sheet On Cell Selection?

Jul 4, 2014

I have a table and need to protect the sheet. From searching I see its a common problem as the table wont auto expand on a protected sheet.

I was thinking maybe I could create some sort of change event so if a user selects cells on Column A,B,G,H rows 13-1000 the sheet will auto unlock then lock again once they leave those cells.

I think it may need a few seconds delay to give table chance to autoexpand before sheet is locked again.

View 3 Replies View Related

Conditional Cell Lock Unlock

Dec 10, 2006

None of the information Ive found on the forums seem to work in my sheet and Im not sure why. After protecting my sheet/workbook, I would like a particular input of a cell to Unlock another cell.

ie if the contents of cell A1 is the phrase "TRUE", then cell B1 will be Unlocked for editing. If the contents of A1 is "FALSE", then B1 shall remain locked. From what i can tell, this must be carried out using VBA code. If anyone can resolve this issue, Id be also very grateful if you could explain what each line of the code means

View 6 Replies View Related

VBA To Clear And Lock / Unlock Cell Based On Another Cell

Jan 14, 2013

I'm trying to write a vba code that does the following....

There is a question in column A to which the user chooses yes or no from column B. Based on the response in column B, I'd like the same row column D to be formatted so that if the anser is yes, the cell is white, and unlocked. If the response is no, the cell is cleared, locked and the greyed out. (e.g. if B4 is "Yes", the format in D4 will change) So far I've come up with the following which formats the colour:

Dim response As Range
For Each response In Range("$C$10:$C$73")
If response.Value = "Yes" Then

response.Select
ActiveCell.Offset(0, 2).Range("A1").Select
With Selection.Interior

[Code] .......

How to add in a .clearcontents function, so that the contents are cleared if the response is not "yes", and also what I would need to add to unlock the cell in column D?

View 1 Replies View Related

Lock & Unlock A Cell Based On Another Cells Value

Dec 20, 2007

trying 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 Related

Conditional Multiple Column Unlock Via Cell Reference?

Aug 2, 2013

I have an inventory log that requires multiple cells in different columns to be unlocked based on a reference cell's input.

So, if a cell in column E has "MORNING" entered then cells L/M/N are unlocked and said user can input data for that row, and only that row. If anything else is in E, then L/M/N are left locked.

Is there a way to do this without coding, just using regular IF() in the cell directly; IF(ISTEXT(E3)=MORNING, Unlock, KeepLock)? I know that's nowhere close to being a legitimate statement, but it's the best way I can translate my thoughts.

View 9 Replies View Related

Lock/unlock Cell Based On Another Cells Input

Feb 25, 2009

I 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 Related

Unlock Cell/s Dependant Upon Entry In Another Cell

Jan 3, 2010

I've never dealt with vba before and I'm not even sure if that's correct. I wish to lock a worksheet with two cells left unlocked, when an entry is made in one or other of these two cells I need other cells to become unlocked.

View 2 Replies View Related

VBA Code To Unlock Cell If Another Cell Value Is Greater Than 6.00

Feb 28, 2013

I have a protected worksheet with most cells locked and some that are unlocked. I also know the password to unlock the sheet. VBA code to monitor a cell(B29 in my case) and if it has a value of 6.00 or more than it will unlock cell B34?

View 7 Replies View Related

Excel 2010 :: Unlock Whole Workbook By Putting Password In A Cell

Jul 11, 2014

I'd like to be able to unlock the whole of a workbook by putting the password into a specific cell and then the whole thing locks again when it is saved. I have been able to find the coding to do this for a worksheet but I'd really like to be able to unlock the whole project.

View 7 Replies View Related

Next Non-blank Cell In Range

Feb 13, 2014

I need a macro that will work through a range, find the first non-blank cell, put that cell's info in another cell, then continue from where it left off and find/record the next non-blank cell. And the next, and the next, until the range is completed. If A1 had "1" and A5 had "2" and A10 had "3" then record those ,say in C1:C3 "1", "2", and "3".

I can find the first cell, but can't continue where I left off to get the next.

View 4 Replies View Related

First Blank Cell In Range

Oct 17, 2008

trying to find the first blank cell in column H. However this gives me 1048576 - i.e the number of rows in Excel 2007. I have checked and all the rows below row 33 are blank.


No_of_Rows = Sheets("Sheet2").Range("H2").End(xlDown).Row

the data is an extract from sage accounts.

View 9 Replies View Related

Unlock A Cell Acording To Another Cell

Aug 10, 2006

I am trying to make a sheet that is protected. It's protected in that way that all cells are protected and locked except cell C3. When someone then type anything in cell C3 and leave the cell then cell D3 automatic unlock. I know how to do it with a macro. But I don't want that the user have to press the macro-buttom to unlock cell D3. It should unlock cell D3 automatic.

View 9 Replies View Related

Find Next Blank Cell In Range

Jul 22, 2009

I had a macro to do this but forgot to save before close now i can't find it.

I need to find the next blank cell in range F15:F240 and select it so i can paste data there.

View 9 Replies View Related

Retrieving The Address Of The Last Cell Before The FIRST Blank In A Range

Sep 28, 2007

Retrieving the address of the last cell before the FIRST blank in a range.

OK, I have searched the web through six different search engines and explored too many dead end solutions to this problem that I am nearly ready to just scrap the whole sheet.

The problem is very simple.

I need to return the address of the last cell that contains data before the FIRST blank cell.

Heck, I don’t even need to have the address, I can just index the position.

Problem is this question has been posted on nearly every excel help forum from here to pokipsy.
Unfortunately EVERY solution I have seen fails the “FIRST blank” requirement.

I have a column of data that never has blanks until the end of the data. I need to know what that cell address is in order to identify a range.

This data has a table above it and below it so none of the “dynamic range” or “Dynamic range name” solutions will work.

How do you get that address without the function continuing to the last blank cell?

View 14 Replies View Related

If Cell In Range Is Blank Then Enter 99999

Mar 16, 2009

In the attached sheet, there is a range with lots of entered data (a small section of the total) where I would like to enter into all of the blank cells the value 99999.

I have been selecting the range as follows:

View 4 Replies View Related

Move To Next Blank Cell When Pasting Range

Jun 10, 2009

VBA to find the next appropriate blank cell in column "A" when copying ranges of varying sizes and pasting them in a new worksheet one after the next.

Currently I have recorded a macro that uses the code below to get to this location, but I am worried this will break when I get new data of a different range size;

Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select 'my initial range selection
Selection.Copy
Windows("myfile.csv").Activate
ActiveSheet.Paste
ActiveCell.SpecialCells(xlLastCell).Select 'finds last cell of my paste
Range("N69").Select 'I hit the down arrow once to get to new blank row
Selection.End(xlToLeft).Select 'to go to begining/col A of new row for next paste

I have tried playing around with

ActiveCell.Next

but can't get it to work.

View 3 Replies View Related

Loop To Paste To First Blank Cell In Range

Nov 16, 2007

This loop is overwriting the first cell in range and not writing to the first blank cell in range.

For Each cnote In NOTE
If Trim(cnote).Value = "" Then
cnote.Value = notes.Value
Exit For
Else
End If
Next cnote

If I take out the exit for , it copies to all cells in range.

View 4 Replies View Related

Last Blank Cell Within Limited Column Range

Mar 17, 2008

I understand the following code finds and selects the last empty cell in column A

Range("A65536").End(xlUp).Offset(1, 0).Select

I am looking for a way to find last empty cell withing a specifice range of cells in column A. Example, I want to loop through from A6 to A16, find the last empty cell and fill it up.

View 3 Replies View Related

Conditional Formatting - VLOOKUP And Blank Cell In Range?

May 22, 2012

I need to format cell D1 to have the fill color be red if any cell in D2:D21 is blank. I've tried

=VLOOKUP("", $D$2:$D$21, 1, FALSE) but that returns #N/A (there is one blank cell in the range at the moment).

I really don't want to use =OR($D$2="", $D$3="", ...$D$21="") if I can avoid it.

View 3 Replies View Related

Excel 2003 :: Count Where Value In One Cell Range But Blank In Another?

Nov 21, 2013

I am trying to perform a count against two ranges of data.Both ranges contain contain values (dates in my case).I am trying to perform a count where cells in column A have data but cells in column B do not.

Is there a way this can be done in Excel 2003? I have tried numerous COUNT and SUMPRODUCT queries but have not found a way to get this to work.

View 7 Replies View Related

Count Blank Cells Within A Range Not Including Fully Blank Rows

Jul 15, 2008

I can count the blank cells withiin a range using

=COUNTBLANK(C6:AD2506)

But I dont want it to count the cells if the entire row, within that cell, i.e. C6:AD6, is blank.

It should only count the blank cells within a row if there has been some data entered on that row..provided it has been entered within the specified range.

View 14 Replies View Related

VBA - Copy Range Of Cells And Paste If Offset Cell Not Blank

May 23, 2012

What can I add to the macro I already have in place (below) to accomplish what I'm looking for (2 parts)? ...

1) I need to copy everything (formulas) that is in C7:F7 and paste it down to all "active" rows - I'm defining an active row by any row where column A is not blank.

2) I need to copy everything (formulas) that is in Q7:AF7 and paste it down to all "active" rows - I'm defining an active row by any row where column P is not blank. (You'll notice by the screenshot that there will be blank cells in column P mixed in with non-blank cells.)

Code:
Sub AdminTool()
'
' CreateAdminTool Macro
'

'
ActiveWindow.Zoom = 90
Rows("1:1").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

[Code] ...........

View 7 Replies View Related

Excel 2007 :: Average Of Set Range - Display Blank If Cell Contains 0

Mar 13, 2013

I have searched and trying to manipulate various formulas with no success...

I am entering an AverageIF formula into cell J15

I want to calculate the average of the folllowing ranges cells J5:J8,J10:J13

However, I want the cell J15 to display blank if cell J14=0

On reflection I am unsure an AverageIF formula is even correct.

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved