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
I have a locked spreadsheet with unlocked cells and checkboxes. The unlocked cells is for the user to enter text. The user navigation is top to botton in column a in sequential order by row.
For example: I have an unlocked cell in A1, then 20 checkboxes from A2:A21 and then another unlocked cell in A22. My problem is that when the user types text in A1 and hits ENTER - the cursor jumps to A22 (the next unlocked cell and scrolls the screen down accordingly. The user then has to scroll back up to select the checkbox in A2 and so on down to A22 before typing in A22.
I have created a command button that floats...that ultimately I would like the user to be able to select after typing in an unlocked cell(instead of hitting ENTER) and it removes the cursor from the unlocked cell - leaving the text entered - freeing the user to select checkboxes because excel didn't advance to the next unlocked cell.
I have had a workbook that has 2 sheets.
When I protect the main sheet with a password, the unlocked cells are also locked.
When I move my cursor over any cells, it has the pointed finger as though it has a macro over it, which it doesn't.
When I click on any cell, the screen flashes and a large part of the screen momentarily it looks like a selected area.
I also am using Freeze Panes. Removed the Freeze and still no good.
If I use the horizontal scroll bar and click the right arrow 3 times, they become unlocked.
If I move the screen 3 clicks to the left, it re-locks all of the unlocked cells.
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.
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 .
I have a workbook with several spreadsheets in it. My 1st sheet is a summary sheet, the rows represents the sum total find each worksheet, normally i will just clk on the cell in the summary sheet and clk on the worksheet i need and clk on the total.
It always worked but suddenly won't respond. When i put= in a cell in the summary sheet and clk on the sum total of the other worksheets, it won't clk, it's like it has been frozen. I unlocked the cell protection but it still won't respond when i clk on any cells on the worksheets.
I am trying to select range of unlocked cells and clear it's contents? How can i do that?
Right now I have a code to select ALL cells.
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 RelatedThe 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.)
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?
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 RelatedI 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.
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 ..................
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 RelatedI 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 RelatedWhat 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].....
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.
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.
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 RelatedI have 2 columns and 10 rows (A1:B10). In column A are certain text values. How do I write a macro to go through the table and preform an operation in the corresponding cell in column B depending on the value in cell A? for example, if A3 = "Complete", overwrite the formula in B3 with the value (paste value)?
View 2 Replies View RelatedI am trying to create a sheet in XL 2010.
In Column A each cell will contain a date (differnet from other cells in that column) when inspection was last done.
Column B is when the weekly inspection is due.
Column C is when bi-weekly inspection is due.
Column D is when monthly inspection is due.
Column E is when 6 monthly inspection is due.
I need a formula to change the colour of cells B, C, D & E when each inspection is due depending on the date entered in A
I am hoping its possible that the cell colour can stay for 2 days after the due date and then return back to blank after the second day.
For example if cell A1 has a date of January 1 2013 then on January 8 2013 cell B1 turns red then on January 10 2013 the cell returns back to normal.
A1
B1
C1
D1
E1
Inspection Date
Weeekly Due
Bi-WeeklyDue
Monthly Due
6 Monthly Due
January 1 2013
Change red Jan 8 & return blank Jan 10
Change red Jan 15 & return blank Jan 17
Change red Feb 1 & return blank Feb 3
Change red June 1 & return blank June 3
Is it possible to use a formula to shade a cell dependent on a condition? I have tried an if formula (see below) but it is incorrect. =if('November 2006 SVOC'!B6>'March 2006 SVOC'!C6,'November 2006 SVOC'!B6 [red],if('November 2006 SVOC'!B6<'March 2006 SVOC'!C6,'November 2006 SVOC'!B6 [blue],))
View 3 Replies View RelatedI'm trying to create a tag with a color border. What I desire is to fill the BLANK cells around the tag, A1:D1 + D1:D19 + A1:D19 + A1:19 in a certain color based on the text value of the cell B11. There are 5 different values, such that if the B11 read Red Sox - the boarder is going to be red, if it reads Houston Astros it will be dark blue, etc..
I have a similar problem with changing the color of the cell based on the month. So regardless of the year, 2014, 2015, 2016, etc... If I use MONTH() function I can just get numbers from 1-12. I want Cell C16-C18 to be certain color depending the date entered in cell C17 such that for each quarter, months 1-3, 4-6, 7-9, 10-12 they are different color.
I have had no luck with conditional formatting (and I also believe that it is good up to 3 cases only). I am decent in logic/programming language but have little knowledge with macro notation and especially how to run them in excel 2013. I do know how to start it alt+F11 and that I need to make sure that code is written under the specific sheet where my tag is located.
I'm using Excel 2010 and I would like to format a cell (say, the font of that cell turns RED) if the value of another cell meets a certain criteria.
View 9 Replies View RelatedI have the names of companies in one column, and the amount they owe in cells in the column beside them. I then have a second list of companies that is a subset of the first. Is there a formula that would place the amount they owe in teh corresponding cell adjacent to the compny in the second list? I've attached a sample workbook, Full Company List in column A, amount owing in B, trimmed down list in D and ideally I'd like the corresponding values in E.
View 3 Replies View RelatedI 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?
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 RelatedI'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.
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.
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 ................
