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.
Having the cell protected after entering the data. From the below table, i need when a date is entered and moved to next cell, the cell which contains the data should be protected. I need to this for the comments column as well.
Number DateStart timeEnd TimeActual Time takenComments 123459132546132156135469654814846543
I'm trying to set a cell so that it has a certain value based on other cell(s) information. I can put a formula into the cell to do that, but I also want to make the cell available for user input, preserving the formula should the user change their mind and delete their input.
Basically, it's a cell with a protected formula that can be overridden with user input, then if the user decides to take their input back out, the formula kicks back in.
I have a cell with data validation: list, that allows values contained in named range. I want to disallow to delete contents of this cell (with 'del' key, or when i edit cell, delete text and then press 'Enter'). Is it possible to do with formula or VBA
I have 2 spreadsheets: in a Sheet1 I keep some values and in a Sheet2 5 names of users with special rights (range A1:A5). I want to have sure that anybody will change/delete that names without password. Only the user "Administrator" can do that(I check the name of the user using "Application.UserName"). Additional question: is it possible to still protect those cells if somebody turn off macros during opening file?
I have a worksheet with multiple entry boxes in a row. Depending on the value in one row, another row will either yield the result from a formula, or remain blank, and this cell needs to be locked. I was able to do this by first putting the formula into the destination cell, and then using custom data validation that says the cell value = "". So this prevents anyone from typing in that cell, no matter if it yields the formula result or must stay blank. What it doesn't protect against is someone deleting the contents of the cell. The reason I don't want to protect the cell is because I want people to be able to highlight the whole row past that cell and delete it all at once. So either using Data Validation, a formula, or VB programming I need to not allow someone to do ANYTHING to that cell, even delete the contents, without locking the cell. Hopefully it can be done, thanks!
For example, if E17 = "X", then K17 = INPUT!$M$28*J17. If E17 <> "X" then K17 is blank. Either way, I don't want any typing or deleting of K17. This applies for range K17:K91, and corresponding E17:E91.
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.
I work in the field of education and the spreadsheet I'm working will require pupils to enter answers into the spreasheet. When a command button (New Questions) is pressed I want the VBA code to switch the cell protection (locked) on or off depending on a random value in cell c2. If c2 = 1 I want f3 to be locked, if c2 = 2 then d3 needs to be locked, but not f3. I've attached a copy of the spreadsheet to this email
I am trying to use the status of Conditional Formatting to toggle on/off protection for a cell.
I have a cell with Conditional Formatting applied if the result of a formula is true. If the Conditional Formatting is applied, I want the cell protection turned on so a user can not change the entry in the cell. If Conditional Formatting is not applied (false), cell protection is turned off and the user may edit.
I have a macro that copies the contents of a cell, and pastes it into the the first blank cell of a range. Its important that the entire sheet is protected, but the macro won't allow the paste function because of the protection.
Is there a VBA code to unprotect the sheet, run the copy/paste macro, then protect the sheet again. THe problem is I would prefer the protection to use a password, as I don't want the user to simply unprotect the sheet from the menu bar.
I have a worksheet where there are a few columns. The columns involved in my problem is Column A and B. So the users open the worksheet and they change the values of column A. Column B has a vlookup formula and if the value of column A is changed than column B automatically changes its value as well (vlookup).
My problem is that the users of this file are not experienced computer peoples so, sometimes (by accident) they change the value of column B (deleting the formula). I tried to set the protection for column B.... but then it will not allow any change (vlookup will not work) to the cells in column B. So my question is that how can I allow the users to see the values in column B but not to edit it..and also let excel to let the formula to change the values of column B (if column A value is changed)?
I know how to protect VB code (e.g. Protection tab of VBAProjectProperties), however I would like to know how to embed code to stop the user from accessing the Macro (via Run Macro).
I understand you can do this by adding "Private" to the subroutine, however, is there some code I could add into a macro/connecting to a button, that would enable the user to protect the sheet (without needing to manually type private within the Module subroutine)?
I'm trying to protect my project so that others can't unhide a sheet I have tagged as veryhidden within VB.
I followed these steps: 1. Select project in the projects window 2. choose Tools 3. Project Properties 4. Protection tab 5. checked lock porject for viewing 6. entered passwords twice 7. clicked ok and saved
On another file this has worked perfectly as I wanted it to.
However on another very large file with multiple VB projects it is not "taking" on the project I need it to.
I can open the file, the VB project and change the setting on any sheet I need to without entering a password.
I have a scoresheet with 60 contestants. Each contestant takes up 7 rows, the first six of which are hidden to start with and I have put macros in the adjoining column so that when they are clicked, the full 7 rows open up and the table of scores can be entered, When entry is complete for that contestant, a further macro when clicked will close up the 6 rows, leaving just the main line (line 14) with the No, Name, “OPEN” macro and other Totals in adjoining columns.
The sheet works fine, but as many people will use this programme, I need to protect the sheets against mistaken entries etc., and as soon as I protect it, the macros wont work and throw up a “unable to set the property of a hidden range class, run time error 1004. I don’t want to leave the sheet unprotected, can anyone advise me where I am going wrong.
I am also trying to find a way to validate “time taken” entries so that they can only be input as minutes and seconds in the format of 09.56, within a range of 00.01 – 10.00. Not having any success with this as it keeps converting the data into something like a date.
On my worksheet i am using advanced filters to view the data in the sheet. But when I protect the sheet they do not work, I have unlocked those particular cells (Row 1). But it still does not allow the use of the advanced filter when the sheet protection is on.
I use the following piece of code to show/hide certain worksheets in a workbook. To access the hidden sheets, a command button runs the code. It works very well, except that the password is openly displayed in the message box (as opposed to returning asterisks for the typed characters).
Sub togglesheets() Dim Ws As Worksheet Dim strPassword As String strPassword = InputBox("Enter Password") If strPassword "Password" Then MsgBox "Wrong Password": Exit Sub Application.ScreenUpdating = False For Each Ws In ActiveWorkbook.Worksheets If Ws.Name = "Apr-Sep" And Ws.Visible = xlSheetVisible Then Ws.Visible = xlSheetVeryHidden
ElseIf Ws.Name = "Apr-Sep" And Ws.Visible = xlSheetVeryHidden Then Ws.Visible = xlSheetVisible End If..............
This is something someone asked me and told me it's possible without VBA. I don't know if it is. I'm sure if it is possible, someone here would definitely know!
I have workbook, which I can not protect. It's sort of a template, so is used again and again with different data. It's accessible to many users. However, I don't want any changes in that workbook once it is closed.
For example, a user opens the workbook, he makes changes in the data, takes the outputs and uses it somewhere else. Now, when he closes it, it should revert back to the same as it was when it was opened. Even if the user saves it and closes, it should remain the same.
Is there a function of formula in Excel that will show me which cells are protected. I have a worksheet that needs certain cells to be protected. I don't know how i can get on good look to see if the are all protected.
Is there a conditional formatting formula possible.
Is there any way I can protect my sheets properly..? I know you can use Tools > Protection.. but I've found (and used, for good, not evil!) macros on the web that will crack these in seconds. Is there any way I can disable the 'Tools' menu so that other users can't load these password crackers?