Locking Portions Of Spreadsheets
Apr 11, 2007Is it possible to lock portions of spreadsheets? Like a certain range
Also how do you get the HTML posting thing to work?
ADVERTISEMENT
Locking Macros In Spreadsheets At Work
Jun 27, 2014I need to lock the macros in spreadsheets at work.
If I go to Developer>Macros I can edit any of the macros.
So to protect them I went to Visual Basic>Tools>VBA Project
What I don't understand is that there is already a password there.
How it is possible that I can edit any of the macros under Developer>Macros when there is a password which I haven't entered under Visual Basic>Tools>VBA Project
Locking Different Cell Ranges Then Locking Whole Sheet With Macros
Jan 4, 2010I have been assigned the task of creating our new integrated time and leave sheet in Excel 2007, previously we had 2 sheets, one for times and one for leave.
The problem I have is that I need to protect the authorisaton columns therre are 2 one for AM and one for PM, so that only Managers can authorise leave by inputting a password and then initialing the leave.
What I then need to do a the end of each month is for the manager to be able to click a Button with a Macro which will then lock the whole worksheet so that employees cannot change their times after the manager has checked the sheet.
I can lock the authorisation columns and password protect but I have to enable protection on the sheet, when I do this I cannot then figure out a way of running a macro which will then protect the whole sheet and assign a password to it so it cannot be changed after being checked and signed off.
I have tried to create a macro (button) to remove the protection on the 2 columns and then reapply protection to the whole sheet with another button but to no avail.....
If anyone has any suggestions I would be eternaly greatful.
Passwords on the sheet for the different buttons are abc or cobra.
You can download my Spreadsheet here.
[url]
Locking One Cell If Another Had A Value In It (conditional Locking)
Oct 10, 2008I recently posted a thread on locking one cell if another had a value in it, and vice versa for the other cell. I got this great response (thanks se1429!) and it works GREAT, but I failed to mention that the worksheet is password protected. It asks for a password when I enter a value in one of the cells. I just need help adjusting this code so I can put my password in the code and allow the worksheet to unlock and lock at will by using this password.
View 9 Replies View RelatedLookups That Retrieve Portions Of Text
Aug 20, 2014If you look at the workbook I have attached, I have made a mock up of a file that I have to use. Cells A1:F12 are part a "Situation Status" file. My team uses this file to update that status of certain events. It shows the current planned estimated completion date for each event in each area. We are required to strikethrough every time the estimated completion date slides out. In some cases, the dates have move 3 or more times so there are cells with multiple dates with strikethroughs. The current date is always the last date in the text string and does not have a strikethrough.
Cells H1:J17 are part of a matrix that I am making to try and capture only the most current ECDs. I want I3 to show "8/10" instead of "8/1 8/3 8/10."
The Event Status and Event Matrix are part of two separate files that I have. The Event Status file is constantly changing so I can't simply say =RIGHT(B4,4) because I could wind up showing the date of a different situation.
Is there a way to lookup the situation name (i.e. "Situation 1"), the Area number, and the event number and return ONLY the latest ECD (the date without the strikethrouh)?
Ranking Portions Of Data With Same Number
Mar 18, 2013I have 4,300 cells populated with data. I have sorted the data and now I need to rank the data in 1% increments. For example, the first 1% of data (43 cells) will be ranked #1, the second 1% of data (43 cells) will be ranked #2 etc. How could I automate this process? I was trying to figure something out with the "Rank" command but that doesn't seem to work in this situation.
View 3 Replies View RelatedShade Select Portions Of Line Chart?
Jan 30, 2013I have a line chart that tracks daily inventory and daily distribution. The chart also has a static value of Max inventory and Min inventory levels. Is it possible to shade the area between the Min and Max levels on the chart a different color from the rest of the chart. For example the chart has a white background and I would like to shade the area betwen the min a max a light blue across the width of the chart.
View 4 Replies View RelatedRemove Portions Of Formula Based On Surrounding Characters?
May 13, 2014I have about 25 workbooks with 180 formulas in them that I would like changed. The formulas are all in the range I10:Q29 and all have the same basic structure:
[Code]....
I would like to remove "filepath1!cell1*filepath1!cell2-" and "filepath1!cell2-" so that only "(filepath2!cell3*filepath2!cell4)/(filepath2!cell4)" remains.
My VBA is pretty weak, but I imagine it should be possible to use something like the VBA equivalent of len() and mid() to isolate the components of the equation to the left of the - in the numerator and denominator, and remove those components (as well as the -) from the formula.
Attached File : trim formulas.xlsx
Scroll Area And Hiding Unused Portions For Entire Workbook
Jun 27, 2012I use all of column A, B, and C, but i only use D1 to H44 other than that. Is it possible to hide everything to the right of Column I and below D44:H44, but leave the scroll area for columns A B C able to scroll to the bottom?
View 6 Replies View RelatedCopy And Paste Portions Of Excel Worksheets - Picture Is Too Large
Dec 2, 2013Recently, when trying to copy and paste portions of excel worksheets, I've been getting the message: The picture is too large and will be truncated.
I have copied and pasted this same way for years and the problem has just arisen over the past month. An excel issue or possibly something else and just getting the message with Excel since it's the software I'm using 90% of every day?
My files are stored on a network drive, not my hard drive.
Saving Portions Of Selected Cell Data As New Text Files
Jun 21, 2009I have a worksheet in my workbook that contains data for 25 different airfoils, each one in a cell space of 3 columns and 50 rows.
In order to do some analysis on these airfoils, I need to save each 3x50 as separate text file to define input for analysis code.
How can I do this in VB in one macro?
Excel 2010 :: Making Portions Of Drag Down Formula Skip Cells
Feb 11, 2014I have set up a formula to sum data from specific columns in multiple sheets in the same file. The simple formula had worked perfectly until I needed the data from the last sheet. In all of the other sheets the data that I need to pull is all in consecutive rows but in the last sheet the data that I need pulled and added is in every 4th row. The sheet looks like this:
All of the columns I've filled in work fine. The ones blank are the ones I am running into issues with. When I drag down the formulas excel pulls the next consecutive rows, which is perfect for the first four sheets I am summing, but for the fifth sheet (I'll ref the Test1 column) I need it to pull 'Wayne 2014'!C6 and then 'Wayne 2014'!C10 in the next row and so forth.
Date
All Test
Test1
Test2
Test3
Test4
$ Amt.
Jan.16
=SUM(C2+D2+E2+F2)
[Code] ........
The first four sheets look like this, which is why they work fine:
Date
All Test
Test1
Test2
Test3
$ Amt.
Jan. 16
=SUM(C2:E2)
[Code] ........
The fifth sheet looks like this: I only want the formulas from the first sheet to pull the data from the dated rows in this sheet.
Date
All Test
Test1
Test2
Test3
$ Amt.
Jan. 16
=SUM(C2:E2)
=SUM(C3:C5)
[Code] ......
Is there any way in Excel 2010 to get it do what I want? (I am not sure how that one cell became outlined and I do not know how to fix it.)
Match Text Against Portions Of Other Text
Dec 15, 2006I am trying to see if part of a text string matches part of another text string. Example: In C1:C100, I have names. In C101:C300, I have names that are similar to those in C1:C100, but may include a "The" in front or have "Inc" in the back, or both. Is there a way for me to check if each of the names in C101:C300 match one of those in C1:C100 and then get the matching value from C1:C100?
View 9 Replies View RelatedCode Locking Up
Apr 13, 2008I'm trying to run the below code, but every time I do, it locks up excel. The code is running on 900 rows of data within the "loop to delete rows" section and in the main for loop there are 49 values to evaluate for the string strSA. how I can improve this code? When I step through it with F5 it works fine.
View 14 Replies View RelatedLocking Column A Only
Dec 10, 2008Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B:B")) Is Nothing Then
Application.EnableEvents = False
Cells(Target.Row, 1) = Now
End If
Application.EnableEvents = True
End Sub
everytime i lock the column a by using the protect sheet, the vba does not work or debugs. is there a way for me to lock the cell in column a when data is inputted or changed in column b? i am using date and time for column a
Locking Timestamp
Mar 27, 2009=IF(D2<>"",IF(A2="",NOW(),A2),"")
Whenever I type into field D2 it populates current date and time in field A2 and down the A column as I write into current D columns. The next step I would like to do in which he was able to figure out so far is how to lock in the date and time after something has been typed into D2. If I make any changes to the D2 field it updates the date and time, which I dont want to happen.
Locking Workbook
Mar 27, 2008I have a user that is trying to lock certian cells within a spreadsheet but to allow certian users still to be able to edit these cells.
When this users is trying to do this and locks the cells it is locking for all users and not applying the "allowed" list of users to make changes.
Locking Worksheets ...
Apr 1, 2008is it possible to lock a worksheet. For example, i have a single worksheet in a workbook that i do not want people viewing. Is there any way that a password would have to be used to gain access to it? I have to keep the sheet inside this workbook because information is pulling from other sheets inside the workbook.
View 9 Replies View RelatedLocking Cells
Oct 6, 2008I did review some examples your members have already provided however I have a slight twist.
I have multiple users adding to a spreadsheet from which data is later copied to a master sheet. I need to lock ONLY cells which contain data when user chooses to save spreadsheet or saves speadsheet on exit. If the cells do not contain data, they should remain unlocked.
Locking A Graph
May 25, 2006how to lock a graph in an unprotected worksheet?
View 7 Replies View RelatedLocking The Top Rows
Jul 28, 2006top three or four rows so that scroilling down the screen leaves the title rows where they are .
I know that this is possible and have done it several times in the past, age however, dulls the memory and I have just forgot the procedure,
Locking Numbers
Nov 9, 2006my spreatsheet calculations work, but the overall thing is too large to print; i am running many numbers and formulas. I made them all finally refer to a small 'summary' box which i created...but when i try to transfer the box to a new worksheet so that i can print it and only it; it automatically has no references for the formulas on the new (blank) sheet, and comes up all errors. to the point, how do i lock the way a box appears (when it contains formulas) so that it will remain sensible after it is transferred elsewhere?
View 3 Replies View RelatedLocking Cell If Value
Mar 29, 2007I am trying to unlock a cell if another cell is a certain Value and if it is not that Value to lock again i have got as far as unlocking it but i can not get it to lock if the value is faulse. This is what i have so far!!
Private Sub Worksheet_Change1()
If ("K4") = "Daily" Then
ActiveSheet.Unprotect ("Password")
Range("F6").Locked = False
ActiveSheet.Protect ("Password")
End If
If ("K4") = "Monthly" Then
ActiveSheet.Unprotect ("Password")
Selection.Locked = True
ActiveSheet.Protect ("Password")
End If
End Sub
Conditional Cell Locking?
Mar 8, 2013Is it possible to have a formula in a cell, based on the value in another cell, but if the other cell value is not true then allow for data entry in the cell
So
J10 executes a formula if the value in I10 (a list field) is 'Cash and Shares' =IF(I10="Cash & Share",ROUND(IF(AND(K10<>0,O10>0),K10/O10,0),0)).
However if the value of I10 is anything other than 'Cash and Shares' then do not execute the formula in J10 but allow users to enter another value.
Note: the cell needs to be locked. The worksheet will be protected and will allow users to Select Locked Cells and Unlocked Cells.
Locking A Certain Text Boxes, And Not Others
Feb 4, 2009For example:
Locking a certain text boxes, and not others. Under review, protect sheet, the Edit Objects function is all or nothing
Locking Rows Together When Sorting
Apr 30, 2009I need to lock consecutive rows together before sorting. e.g. A1 contains a name and A2 is blank: B1 contains data related to name in A1 - so does B2, and so on. Therefore need to lock rows 1 and 2 together, then 3 and 4 together and so on, but sorting on the data in the first cell of the group e.g. A1
View 3 Replies View RelatedConditional Locking Of Cells
Jul 29, 2009Lock cells based on conditions? For example, in my spreadsheet I need my cell J42 to stay locked at all times unless my cell C12 reads "Klongtoey".
View 10 Replies View RelatedLocking Cells On Certain Columns
Jan 24, 2013WILL IT BE possible to lock columns / cells for any user to avoid editing? Only the creator of the file should be able to edit?
View 2 Replies View RelatedTwo Different Persons Locking Cells?
Jul 16, 2013Is it possible for 2 different persons to lock different cells on the same worksheet?
View 3 Replies View RelatedAutomatic Locking Down Cells After Use?
Oct 21, 2013I am designing a car park rota on a spredsheet.Each person has a limited number of spaces. Is it possible to automatically stop people going in and deleting out entries from previous weeks (to stop any cheating!) or would I have to manually go in at the end of each week and protect.
View 7 Replies View Related