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.
I want to select the rows that i want to copy then I would like a command button to unlock the sheet using password "2014", copy & paste my selection in the last empty row, and then lock the sheet.
View 9 Replies View RelatedI have been really trying to get this sorted myself and I can get it to work with one exception. I can't get the sheet to lock with a password. It locks, but if I try to unlock it again, it does so without prompting me to enter a password.
Essentially I am wanting to unprotect two sheets, unlock the cells that were previously locked (so users couldn't enter data in them when the sheet is protected), then reapply the same password to protect the sheet, but now they will be able to enter data in the unlocked range of cells).
The second macro is to reverse the changes made in the first and 're-lock' the unlocked cells) (and again apply the same password to the sheet).
this is the code I have used.
Sub PART_YEAR()
Sheets("2012 Calculator 1 week").Select
ActiveSheet.Unprotect "taado"
Range("D20:D23").Select
Selection.Locked = False
Selection.FormulaHidden = False
Sheets("2012 Calculator 2 week").Select
[Code]...
I am assuming I have superfluous code in there, in addition to my mistake with the reapplication of the password.
I have a workbook with two sheets. The idea behind the workbook is an Interview Guide to be used just before an Interview. For now my problem is this.
Sheet 2 "Competencies" is just data. It stores competencies with their associated definitions and questions.
Sheet 1 "Control Page" is the sheet where the questions will eventually go. The user (Interviewer) will input data on the first two pages which will include name of candidate, date of interview etc. but they will also select 5 Competencies from the already existing drop down menus on page two. From there as the selections are made I wish for a code to copy the corresponding definition on sheet 'Competencies" to cells lower down in sheet "Control page".
I don't see a place to upload a file as I have a sample of the sheet ready to go.
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"
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
Can I have a formula in A1 cell to display the No. of cells in selection (in a range) any where in the sheet. Just the No. in A1 cell is sufficient
View 4 Replies View RelatedHow to Lock/Unlock an Excel Cell Based on the Contents of Another Cell?
View 1 Replies View RelatedI 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 View RelatedNone 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?
I am trying to create individualized worksheets based off a master worksheet. I have different committee names that I want to appear on the individualized sheets, based on whether the person is apart of that committee or not. On my master sheet, I choose the persons name under the committee and I want that committee name to show on the individualized sheet. For example, I want A1 on Master to show on Name 1, B3 and A10 on Master to show on Name 1, C3. Can I write a formula to achieve this or or am I asking too much? In my actual file, I will have about 10 names and 5 committees with one person being on up to all 5 of the committees.
View 1 Replies View RelatedI 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.
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 RelatedI 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 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 Relatedfind attached zip file . if you open the file then you can understand the problem.
I need to change cells color and text color as per user choice in Password protected sheet : (user is useing excl2000)
Kindly open attahced file i putted the note inside the file.
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 RelatedI 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 RelatedI'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 RelatedI 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.
I tried very hard to design a leave roster for user to mark their leave application. The criteria are as follows:
1. 4 applications per date
2. a region of worksheet (i.e. "A2 to H20) will be defined for users to "click" on the cells (within the defined region) to mark their application.
3. once a cell is clicked (i.e. marked), it cannot be altered.
4. each click will automatically increase the total by 1.
I got the following code to deal with the situation but user can still click on any cell outside the region (in fact I fail to define the region).
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim m
ad = Mid(ActiveCell.Address, 2, 1)
m = Range(ad & 24).Value 'here a formula "CountA(A2,A20)" will be place in the cell (24, c).......................
I have an excel workbook with two sheets. Sheet 1 has a list of people names, rank, address, and phone numbers. Sheet 2 has boxes where a user can select a last name from all last names in sheet1. What I want to happen after the user selects the last name in sheet 2 is for it to automatically fill in the rest of the data (ie Rank, First Name, Address, city, Home phone number, Cell Phone number). What do I need to do in order for this to work?. Attached is a very small example of my much larger project.
View 3 Replies View RelatedI have a sheet that is password protected. I have this code attached to a command button. It will unlock the worksheet, autofilter it, print it, unfilter it, and password protect the sheet again. However it is protecting it without a password. I need to have it protected with the password so that someone will not be able to just go to tools to unprotect the sheet.
Private Sub CommandButton1_Click()
ActiveSheet.Unprotect "rainforest"
Columns("O:O").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:=">0", Operator:=xlAnd
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Selection.AutoFilter Field:=1
Selection.AutoFilter
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowSorting:=True, AllowFiltering:=True
End Sub
I'm trying to build an investment calculator. Tried searching "calculator" as keyword but return nothing similiar.
There are eight cell : [ A ][ B ][ C ][ D ][ E ][ F ][ G ][h]
[ H ] is price of the leverage
[A] is X balance
[b] is X lot size
[C] is X amount of money use to get x lot
[D] is X percentage of money use to get x lot
[E] is X point gain
[f] is X money gain
[G] is balance + money gain
Ignore the formulae for leverage to determine 1 lot price. Take 1 lot = 250.
What I'm looking for is when I enter any value at cell B, C or D, it will automatically calculate the value for either B, C or D cell.
Let say, the balance is 10000.
If I enter 2 at cell B. Then, it will calculate the value for cell C & D.
If I enter 750 at cell C, then it will calculate value for cell B & D.
If I enter 25 at cell D, it will calculate value for cell B & C.
how to use OR Selection in excel.
i have a buttion on a whole heap of worksheets that is linked to this sub. I want it to print the range on one sheet of paper. though it will print over 4 sheets.
View 2 Replies View RelatedI am getting US formatted dates with this procedure, I require UK dates, but I don't understand why its giving me US format
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
If UCase(Sh.Name) "ALL BILLS" Then Exit Sub
With Target
If .Row > 5 And .Column = 19 Then
If Cells(.Row, "B") = "" Then
Else
Application.EnableEvents = False
Cells(.Row, "S") = "CLOSED"
Cells(.Row, "E") = Format(Date, "DD/MM/YYYY")
Application.EnableEvents = True
Range("E:E").EntireColumn.AutoFit
Range("S:S").EntireColumn.AutoFit
End If
End If
End With
End Sub
I have Listbox on my User Form with 10 items say "A", "B","C", and so on. I wnt User to select any one item or any two or three items or even all items. Whatever user selects will be transferred in one cell only like "a","C". Is it possible to do this with listbox ?
View 3 Replies View Relatedhttp://www.excelforum.com/excel-prog...reedsheet.html . This is the original post.. and the original code was
Quote:
Any easy way of finding out if deleting a particular sheet / range or selection will mess up any references elsewhere in the workbook?
View 1 Replies View Related
