I need to allow only the cells covered in the date (example today is 30 May) to be unlock for data entry. However i need the rest of the cells to be lock. The date should refer as =today().
View 6 RepliesTrying to incorporate the following in a worksheet:
Lock the cells automatically in the columns that are older than 2 days or more
Lock the cells corresponding to today...
Trying to incorporate the following in a worksheet: Lock the cells automatically in the columns that are older than 2 days or more Lock the cells corresponding to today and yesterday using a command button after entering X Copy the sheet multiple times with the same features
View 14 Replies View RelatedI build a lot of spreadsheets at work that I email out and the person I send them too has to enter information in the cells highlighted in yellow. Since not many of the end users are too computer savvy I lock and protect all the cells that they shouldn't be changing.
This can take forever, is there any way to have all the cells in a worksheet become locked and protected based on the formatting they have? All cells that aren't yellow?
I am working on my spreadsheet and was wondering if anyone knows if cells can be Protected or Unprotected based on the value of another cell?
Here is what I am looking for:
If cell "A16" on the "Input Sheet" equals AR then Protect Cells J16, K16, L16, M16, N16, and O16
If the value of A16 on the Input Sheet DOES NOT equal AR then leave those cells unprotected.
Has anyone ever done anything like this? On my spreadsheet it would allow the user to skip the unnecessary cells as opposed to having to Tab through them. Not a big deal but definitely would be a time saver and a lot more user friendly.
If you have any questions or concerns just let me know. I will wait to see what the Excel Guru's come up with.
This is a project plan with tasks and dates. Column A is the activity number. (Example 1, 2, 3" etc). Column B is the task (Ex. "Complete Report"). Column C is number of days required to complete the task. Column D is the dependency column. (Ex. Cell D2 =1 in other words Task 2 is dependent on task 1). Column E is the date.
I would like to have a seperate start date cell and a go live date cell.
The objective is to enter a start date, and have each column E date increase based on the number of days entered in Column C. If a task is dependent on another and I change the number of days in Column C I need the dependent task to change the same amount of days.
I have a form to imput a date but the problem is that it changes the computer date aswell. Is there another code i can use? Im using CDate code.
Private Sub CommandButtonProceed_Click()
Sheet2.Cells(2, 57) = TextBoxInitials
Cells(1, 2) = TextBoxSiteName
Cells(2, 2) = CDate(TextBoxDate)
Date = Cells(2, 2)
Sheet2.Cells(3, 28) = TextBoxPlant1
Sheet2.Cells(3, 31) = TextBoxPlant2
Sheet2.Cells(3, 34) = TextBoxPlant3
Sheet2.Cells(3, 37) = TextBoxPlant4
Sheet2.Cells(3, 40) = TextBoxPlant5
Sheet2.Cells(3, 43) = TextBoxPlant6
Sheet2.Cells(5, 27) = ExtractElement(Date, 3, "/")
Unload Me
DataImput.Show
End Sub
i woul like to make a macr which will check the system date on the computer to see what month it is and the populate cells A14 - A44 with the dates from the 1st till the end of the month.
View 9 Replies View RelatedI got this wonderful, beautiful spreadsheet/template i'm using now for my small business, and well, i've got it all tweaked and tuned so it looks beautiful on my screen (100% view in excel). I can look at it, and they way it looks, is the way it prints whether i opt for a PDF or (EEEEEK!) paper. I've sent this report and template i've put together with Mrexcel's support, and it's all primp and pretty and the person i send the spreadsheet to for review? he's looking at it at the same 100%, and lines are cut off when multiple rows are merged that have multiple sentences typed (technically reference to) in. he's tried to print both pdf and paper from his computer and had no issue. it comes out perfect. It only looks visually cut off. we're trying to avoid a second transmission back to me to reformat say something needs to be omitted. QA if you will.
my question: Is this some sort of issue relative to our screen resolution? is there a way i can optimally get what he is seeing when he views it electronically to be what i'm seeing electronically?
1.I need to protect certain locked cells from editing and allow certain unlocked cells to be changed on multiple worksheets.
2.When all of the changes are made to the unlocked cells, I need to password protect the entire workbook (except one worksheet) from any changes. (i.e. Prevent even the unlocked cells from being edited)
3.I also need a password to un-protect the workbook and return it to the state described in # 1. above .
How to protect or lock their spreadsheets after certain date.
My question is how to protect or lock workbooks before certain date?
Example:
I have individual woorkbooks for each week in a calendar year saved in one folder.
Is there any way that I can protect them from either being opened or if opened than not be able to save anything in them until one week before the calendar week they are designed for (or whatever date I wish to set)?
way of protecting an array of cells on the condition that information is entered in another array of cells or vice versa. So, put simply, if data goes into a1 then a2 becomes protected (or vice versa).
View 14 Replies View RelatedI try this method but data entry not allowed
Here is a scenario to protect
formula cells but allow access to data cells:
. Select all cells in the worksheet by clicking on the top left corner of the sheet.
. On the Format menu Click Cells.
. Click on the Protection tab.
. Uncheck the Locked and
Hidden check boxes, then click OK.
These four steps will make all cells in the worksheet unprotected and can therefor be modified. Next step is to
protect the formula cells, and here is how we do it:
. Select all cells that have formulas.
. On the Format menu Click Cells.
. Click on the Protection tab.
. Check the Locked and Hidden check boxes, then click OK.
. On the Tools menu click
Protection and select Protect sheet
I have a worksheet that is protected with data entry cells unlocked.
1. Unprotect worksheet
2. Lock all cells in the worksheet and then protect.
3. After a date in a cell in the worksheet.
I'm a newbie to vba and at least know vba is the only way. And this need to be automated.
I have a workbook list of activities that I am hoping will visualise the scheduling of routine maintenance tasks in my engineering department. The date of the most recent check is entered in row 6 and the interval in row 5. Hence the number 7 denotes a weekly check. The conditional format is such that the reds indicate that a check needs to be carried out. If the check is done on the scheduled day then a "Y" is entered into the square. This will then turn the square green. If "Y" is entered on any other day then the square turns orange to indicate the check is done, if not on time. The red squares do not change colour unless a yes is entered. This is designed to show the history of checks and give us the opportunity to show compliance to routines. What I need to do is lock the rows that have passed the current date, to prevent unscupulous modification. Thus the macro would look down column A and if the date is less than today() would lock the row. I have truncated the file as much as I can. A nice to have would be the facility for an adminstrator to go in (perhaps password protected) to make changes if necessary.
View 3 Replies View RelatedI am currently using a piece of code (within a bigger piece) that colours the dates in two columns based on how close it is to the current date. It works fine but sometimes the sheet it is looking in has over 3500 rows so it is taking for ever (well over 6 mins)
The code is
HTML Code:
Sub ColDate() 'date case opened
application.ScreenUpdating = False
Dim MyRg As Range
Dim F As Range
Dim DateDiff As Long
Set MyRg = Range("g1:h" & Range("H" & Rows.Count).End(xlUp).Row)
For Each F In MyRg
[code]...
Is there anyway to have columns automatically be hidden based upon a cell value? I have a column for each month, and I would only like to show months that have occured, and keep future months hidden from view. I have the current month end date in cell a1 and the following columns each have a date as the header?
View 2 Replies View RelatedSome sensitive data is held on worksheets that are used by people that do not have access to see the data.
It would be most convenient to keep the data in these worksheets but hide it from view of the users.
Is there a way to password protect a users ability to unhide a column?
How can i protect the particular cells
Ex.
All the column g
I have a date in a cell that when it is less than today() (so yesterday's date) would turn red and the 5 cells to its immediate right would also turn red, if the date is still current then it would be green and so would the right hand cells.
View 4 Replies View RelatedAttached simplified sheet gives background - but need to update the cells in the aggregate monthly table C44:C55 based on data in single cell above, and according to the correct month. Need do this without the previous months data changing.
Month is determined by cell E8 and data to be updated to the agg month range is to come from cell C15. The month date will change automatically as will the data in C15.
I have one excel sheet. In the first column there are various Project Codes and in the first row there are various date. Cells in the matrix contains 0 or 1 value. If value is equals to 1 that means the project was on hold during this months and if the cell value is null of 0 (Zero) it means the project was active. For a project in a particular row there are several times a project went on hold and now I want a date range showing the project's hold duration. If project went on hold more than one time the result should also give different range for each time when project went on hold.
follow the link to view the excel file.
http://www.excelforum.com/attachment...3&d=1336994800
[IMG]http://www.mrexcel.com/forum/data:image/png;base64,
iVBORw0KGgoAAAANSUhEUgAABAAAAAMACAIAAAA12IJaAAAgAElEQVR4nOy9e3gT153/P99fd7vd77fxV+3udtO06fr3PL
[code].....
I would like to filter between a date range. the dates i need to filter between are in cells A1 and A2. this range contains the start and end of month. It is referenced from another sheet in the same workbook (='Job List'!C2).
i want to filter; greater than or equal to A1 and less than or equal to A2. How I can do it using the advanced filter method. My data range is C2:C9.
I have a range (D5:BU5) that I will have various dates in each cell. At the end of the row is a percentage block. It has the formula count=(D5:BU5)/70 which gives me a percentage complete. The fun begins where some dates are based off annual, semi-annual, and quarterly. What I want to do is be able to delete cell values so they are not reflected in the final percentage. I know there is a marco that deletes the entire row but that is too much I just want a target cell.
View 3 Replies View RelatedI have a spreadsheet that i use as a log for recording when certain tasks have been completed.
Each worksheet in the workbook represents one month. Within each month, there are monthly tasks and weekly tasks. Currently i manually unlock the relevant weekly cells at the beginning of each week, and lock them again at the start of the next week. Similarly with the monthly task cells.
Is there a way for Excel to automatically unlock the relevant cells based on date so i don't have to go through this task at the start of every week and every month?
I am trying to make a certain cell range turn red on the week that a cell has listed.
What this is being used for is to track the work of our contractors, and I currently have this set up to auto calculate the jobs that are being done and when, but I would also like to include something in there that when the contractor is set to head back to India, that day forward is listed as "red" under his name. This will allow me to see if I need to extend his work based on the number of hours he has left in his jobs. I will attach the picture of it...on the top you will see dates, and the pivot table on the bottom shows when he will need new work, but I am going to add a field that has the date he is set to leave, this is what I want the coloring of fields to go off of! ...
I have the following:
A/B/C/D/E/F/G/H/I/J/K
April 2008 / August 2008/Jan/Feb/Mar/Apr/May/Jun/Jul/Aug/Sept/Oct....
How can I get excel to fill in a constant number say '3' automatically based on the date range in column A and B. For example, in this case Since the date range is from April to August how can I get excel to automatically fill in 3 for apr/may/jun/jul/aug
we are using a shared excel file in a network. Is there a way to protect cells/columns from updating by other users, and i can only update them.
View 9 Replies View RelatedI know how to protect a sheet and unlock some cells, but how can I lock the format of ALL cells while still allowing users to enter information? Ifve been instructing people to Paste Special  Values, but of course itfs more difficult. For example, Ifd like to protect a sheet but allow cells B2:C2 to be editable in value only – NOT in format. I tried searching the forum for a solution but was unable to find anything.
View 2 Replies View Relatedhow do i protect a cell from being changed? I don't want to protect the whole worksheet, just a few cells within a worksheet? I have tried the Allow Range Users function, but cannot get it to work? Kind regards, Michele
View 8 Replies View Related