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.
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.
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 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.
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
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?
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.
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.
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.
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?
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.
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.
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.
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.
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?
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.
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 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.
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.
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.
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.)