How To Copy A Locked Cell Without Formula
May 13, 2013
I have created a spreadsheet that will be viewed/used by various users (read only as I have protected the sheet except from three fields), although I would like to give them the ability to copy the contents of a specific cell that is protected.
I have tried to give them the ability to select the locked cells and then copy, but that means that the formulae in the cell is copied as well.
how I can let people copy a locked cell without the formulae?
View 2 Replies
ADVERTISEMENT
Feb 19, 2014
I have a workbook with multiple sheets interacting with one another. On one of them the user is prompted to make lists of expenses in multiple columns. The column labels are in row one, and row two has the sums of all the cells below. I have every sheet in the workbook protected, so that only the cells that need to be modified are unlocked. The problem is that the user can drag cells around and change the range of the functions in row two (locked cells).
For example, A2 has the function =sum(A3:A100). But if the user drags the values in A3:A5 to A6:A8, the formula in A2 changes to =sum(A6:A100). Is there anyway that I can allow the user to drag cells (this could be a useful feature), but keep the formulas in the second row fixed?
View 5 Replies
View Related
Mar 29, 2014
Have you ever copy a row with formula in locked cells & insert it in a protected worksheet?
View 1 Replies
View Related
May 9, 2008
I have users that are using a protected Excel Workbook where they sometimes have a need to copy values from Internet Explorer paste them into the protected Excel Workbook. When this happens, if the users select the cell and don't actually paste the text into the formula bar (or by double clicking the cell), they can replace the destination cell's formatting - which in this case makes the cells a locked cell instead of a unlocked cell.
Now is there a way to automatically have the pasted value keep the destination formatting? Or perhaps is there a way to set the copy and paste settings so it only pastes text from Internet Explorer into Excel and not the formats?
It is not an option for the userbase to select the option choice of matching the destination formating when pasting as it is a very large user base without much Excel experience.
View 9 Replies
View Related
Apr 30, 2009
how to lock a formula in a cell, protect the sheet but still allow it to recalculate when new data is entered in a unlocked cell that feeds the formula? I am using Excel 2003.
View 5 Replies
View Related
Aug 12, 2006
I have a template that I have hidden the formulas but allowed editing. Is it possible to create a pop up box for the end user to use to edit a cell with the hidden formula? This is a budget template with already set up formulas but the end user needs to be able to make edits but the end users are beginner excel users and if they double click on the cell the formula will automatically delete leaving the cell blank. I need a way around this so they cant view the formula but only change the data if they really mean to change it.
View 3 Replies
View Related
Jan 21, 2010
I have multiple data tables in one sheet...and each one has a chart...in that sheet. To better allow the viewer to coordinate quickly which data table corresponds to which cart...i want a line between them. Excel already has the draw line function, but if you move the chart around, the line doesn't move.
View 2 Replies
View Related
May 29, 2014
I have worksheet that contain values in column E and G and the total in column K. E and G are unlocked for manual input and column K contains the formula for the total and is locked. From time to time a certain row will not be applicable and before I started locking the cells in column K i would just put "not applicable". Now that it's lock I can't do that. My only option now is to put O in column E and G but I would rather put "not applicable" in column K. Is there a way around this. I was thinking of something along the line of a macro that would unlock the worksheet input the text and re lock it. The macro would have to lunch when a locked cell is selected or have a button that would launch the macro an would prompt for which cell the text would be entered. Is this possible.
View 7 Replies
View Related
Oct 20, 2006
I have created a simple excel document with
Sub randomnumbers()
Range("B1").Formula = "=randbetween(1,100)"
Range("B1").Value = Range("B1").Value
End Sub
There is a button that i click and it creates a random number to cell 'B1'. it works fine, but i need to lock the document so that the user can only access one cell... 'A1' when i lock the document, and i click the button, it gives me a '400' error. i think it is because the button points to a locked cell.
View 5 Replies
View Related
Nov 1, 2006
I have programmed a Form in VBA that initializes by clicking a button on a worksheet. The Form features combo boxes, text fields, and a couple option buttons. Once completed, the Form enters the values entered by the user (as strings) in contiguous colums in a single row. The worksheet is locked so as to not allow users to input values into the worksheet directly and bypass the Form.
The question:
Once a row is filled by submitting the Form, i want to allow a click event on that cell (like double-clicking, for example) to pop-up the Form, but containing the corresponding values from the cells in the matching combo boxes, text fields, option buttons. I want to do this so that I can allow modification on a row without letting the worksheet be modifyable without passing through the Form. Here's the code for submitting the Form values into the worksheet:
Private Sub cmdSubmit_Click()
ActiveWorkbook.Sheets("ALTEC Growth Report").Activate
Range("A7").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
ActiveCell.Value = Trim$(txtName.Value)
ActiveCell.Offset(0, 1) = cboCC.Value
ActiveCell.Offset(0, 2) = cboProdServ.Value
ActiveCell.Offset(0, 3) = cboStatus.Value ..................
View 8 Replies
View Related
Dec 3, 2009
Is there any way to change the pattern color of a cell (or a group of non-adjacent cells), say from No Color into Red if the cell(s) protection is locked (or unlocked)? Seems to me that the Conditional Formatting cannot work this way.
View 3 Replies
View Related
Jul 11, 2009
I have one issue with my protected worksheet. Some locked cell contents are linked to cell contents located in another sheet. When I double clicked on these cells, a warning window poped up but also opened the sheet in which the corresponding cell is located. This is quite annoying to have to go back to the former sheet. How can I prevent it ?
View 3 Replies
View Related
Aug 7, 2012
What I'm trying to do has been done before, However, all of the Code i am seeing online is not working for me...so maybe i'm missing something
I want a range of cells to remain locked unless SCP is entered. I need this done for four different selections (SCP, SD, MM, PP)
So if SCP is entered, then a certain range of cells remains locked, If SD is entered then another range of cells remains locked, so on and so forth for the 4 selections.
This was what I was working with.... However, i am not sure if i need to protect or unprotect the spreadsheet before i run the macro, also am i supposed to leave all cells locked or unlocked?
Private Sub Worksheet_Change(ByVal Target As Range)
If [B3] = "SCP" Then
ActiveSheet.Unprotect ("PASSWORD")
[Code].....
View 1 Replies
View Related
May 29, 2007
I have created a workbook of 12 monthly worksheets, with an index page with hyperlinks to each worksheet. There is also a hyperlink on each worksheet to return to index page. Each worksheet is protected, but the cells which users can enter information are not locked. The protected worksheets have the 'select locked cells' unticked.
I have found that an inadvertant click on any of the protected cells in the worksheet will take users back to the index page. This is very annoying, what have I done wrong? The hyperlink is one of the unlocked cells.
View 6 Replies
View Related
Nov 30, 2007
We have Microsoft Excel 2000 and i have locked all worksheets separately. I have enabled Data Validation on a range of cells and enabled a list box. The way the workbook works is you log in with a name, the cell range references the names in each cell to another cell which was your login name. Those that match, after clicking a button will unlock those specified cells.
Then users can use the data validation listbox function to select only their name from the cell that was linked to the log in name box. (the users name that logged in only shows in the list). The problem is, all cells by default are locked in the range with the names in. You cannot delete the values in any other way, unless you unlock the cells. (at least thats what is supposed to happen)
My question is:
Is there any code anybody knows that i can use to make excel copy what excel 2003 does, im sure that the problem is a fault with excel 2000 and microsoft fixed it when they released excel 2003.
View 2 Replies
View Related
Jun 22, 2008
I have locked some cells on my Spreadsheet so other people can not change them. What I would like to know is how do I change the colour of the font? I also would like to Bold/unbold the font.
View 6 Replies
View Related
Mar 23, 2014
I'm trying as below:
If F3 contains specific word i.e. FCL then only cell K3 and L3 are unlocked or available for data input.
Additional information:
F3:F10002 contain data validation list
I need to apply above condition on K3:K10002 and L3:L10002
I did some search on this forum and I came to know above can only achieve through VBA only. Is there is any possible way without VBA? I'm not good with VBA etc
View 7 Replies
View Related
Oct 14, 2007
The spreadsheet is password protected. The only cells that students can enter values into are the answer cells.
I am trying to figure out how to have a cell switch to being locked if the correct answer is inputted. ( I have a column that I hide that has the correct answers in it. Then I use a countif function to determine if the answer cell equals the inputted answer.)
View 9 Replies
View Related
Feb 22, 2013
I have created a worksheet with many calculations based on user input into several unlocked cells. I would like to create a cell that when selected, would clear the contents of this group of unlocked cells.
View 10 Replies
View Related
Jan 12, 2010
I'm trying to make a spreadsheet more secure to prevent unintentional changes.
What i'm looking to do is to lock all cells so no changescan be made unless the user clicks column A.
Column A should remain unlocked and when the user clicks it i would like the entire row unlocked for editing (but only after column A is clicked)
I will run a macro to lock the entire sheet again once the sheet closes.
View 2 Replies
View Related
Jul 5, 2012
I need to unlock and lock certain cells when other cells have data entered.
I also need to enforce in the unlocked cells a minimum value.
In my worksheet i have the below requirement
Initially Cells C6 and 7 are unlocked and Cells C5, 8 and 9 are locked
1. Cell C6 must have a value entered greater than or equal to 50, when this value is entered I need to unlock cells C5 and C9 and lock cell C7
2. Cell C7 must have a value entered greater than or equal to 50, when this value is entered I need to unlock cells C5 and C8 and lock cell C6
I also need to unlock cells C15 and 16 when C13 has "Yes" selected in the drop down menu.
View 3 Replies
View Related
Nov 16, 2008
I just realized that some of my comments on a worksheet are being cut off or not viewable. By Default the Comment is displayed to the Right of its cell. I have a Scroll Lock to prevent users from scrolling off the work area. Some of the comments are being displayed past this scroll area and the user is unable to read the comments as they are being cut off or being displayed beyond the scroll area.
Is there anyway to reposition or set the area that these comments are displayed? For Example, to the Left of the cell so that they are able to be viewed. The Workbook is Protected and the Cells with comments are locked so the user won't be able to click on the cell. I found 2 Codes that would work if the user could either click on the cell or if the comment always visible(they are not)
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rng As Range
Dim cTop As Long
Dim cWidth As Long
Dim cmt As Comment
Dim sh As Shape
Application.DisplayCommentIndicator _
= xlCommentIndicatorOnly
Set rng = ActiveWindow.VisibleRange
cTop = rng.Top + rng.Height / 2 ................
View 9 Replies
View Related
Mar 1, 2010
i want the user to be able to change the cell color on the click of a command button. the worksheet is protected. when the user click the command button the active cell changes to red and offsets by 1. then the work sheet is locked again.
The two problems I am having is 1. I want the range to begin from row 10, column k to column FD. all cells down
the second problem is the current code allows me to edit locked cell columns A to J ...
View 9 Replies
View Related
Feb 23, 2010
I have a master spreadsheet that houses most of the information that remains in my control, and several other user spreadsheets that update cells from that master through links.
I am having some problems lately with users doubleclicking the locked linked cells in their workbooks, and excel then wanting to open and give the user access to my master spreadsheet. Is there a way to disable this feature? I would like it to not even acknowledge the action if that's even possible because it confuses them as to why they are getting a prompt to open another document.
View 5 Replies
View Related
Jul 16, 2007
I would like to copy a formula in a cell and then paste only the text of the formula, but I can't figure it out. Basically, I would like to avoid going into the cells and absolute referencing or hitting F2, then copying the text.
When I hit "Ctrl C" to copy the cell, then hit "Alt/E/S/F/Enter" to paste the formula, it is just like a regular copy/paste formula-wise in that the references move.
View 10 Replies
View Related
Sep 13, 2012
My workbook has sevaral sheets reresenting the payment methods used by our customers. Each sheet has a range of cells F9 to Q33 which should hold the value of payments for each working day. e.g. F9 represents April 1st, F10 represents April 2nd.
A daily list of values is supplied which then transfers that day's value into cell E1 on each sheet.
On each sheet I manually have to take the value in E1 and copy and paste special: value into that day's cell e.g. today I will paste into cell K21. The cell value then looks like this '12134.12'. I then edit the cell to put a calculation on the end to divide the value by the value in another cell on the sheet. The cell value ends up like this '=12134.12/$G$5'. This is so I can see the values in thousands of pounds or by changing the value of G5 to 1,000,000 in millions.
Tomorrow I will do the same but in cell K22.
I have to do this on 15 worksheets and I have been struggling to get a macro together to do this. I can get as far as copying and pasting but I don't know how to add the calculation onto the end. I also would like to be able to input the cell destination daily probably with an input box so I can be flexible and potentially run it sevaral times if I need to catch up on previous days.
View 6 Replies
View Related
Sep 8, 2007
Automatically copy formula to next cell when i enter something in perticular cell ....
View 11 Replies
View Related
Jun 24, 2008
I am currently working on a data analysis project (data mining) and need to collect and later analyze statistics for the inputs which control a series of calculations. These statistics are shown in the Statistics 1, Statistics 2 and Statistics 3 cells in the workbook that I attached. The inputs are X,Y; all possible values for these inputs are listed in the N,O columns. Basically I need a macro which would take the values from these two columns and place them pair after pair into the controlling cells (K3, L3), then it would copy cells H2 through L3 (updated stats) to a new sheet after each copy operation - so that I will finally have a list of statistics for all of the input pairs.
View 3 Replies
View Related
Sep 9, 2007
I want to copy formula from previous row to next cell when i enter something in perticular cell.
i.e
--Colomn A --- Colomn B -- --------Colomn C
1 01-09-07 ----- John ----------=vlookup(b2,$s$1:$t$10,2,false)
2 01-09-07 ----- Smith -------- =vlookup(b3,$s$1:$t$10,2,false)
3
4
5
Now if i enter date in cell A3 then cell C3 should be automatically filled/copy formula as celll C2. and so on......
then if i enter data to A4 then cell C4 should be automatically filled/copy formula from cell C3.
I have also attached example file.
View 9 Replies
View Related
Apr 29, 2006
I want a VBA function to fire "ONLY" when a cell is changed.Peramiters:
Data ertry range is full, Last cell in SubTotals range has changed from $0.00 to anything >, Last 3 cells are Locked, Protected and Unselectable Attached is a copy of the work book. I have posted on VBA Express and we have tried to solve this problem, We have come a long way. You might want to read the history link above.
View 4 Replies
View Related
