Excel 2010 :: Macro Runs On Protected Sheet But Changes Protect Sheet Options?
Feb 24, 2011
I am using Excel 2010 and I have a password protected workbook with password protected sheets that uses several macros. Most of them, in order to run, have to un-protect the sheet and then re-protect it again. This has been accomplished easily enough by adding ActiveSheet.Unprotect Password:= "mypassword" and ActiveSheet.Protect Password:= "mypassword" to the appropriate places in the script. All of my macros, which do various things like sorting and moving data, deleting blank rows, displaying dialog boxes containing warning messages etc. run fine.
My problem is this: when I password protect the sheets manually, I have checked the following options in the "Protect Sheet" dialog box. Under "Allow users of this worksheet to" I have checked 1)Select unlocked cells and 2)Format cells. After entering my password and closing the dialog box my sheet is protected, but I can edit cells in the manner my allowances permit. However, once I run any of the macros that un-protect and re-protect the sheet, I remain able to select and edit unlocked cells (practically, for my purposes, this means that I can input data which will appear in the default font size and color of the sheet) but I cannot format cells (which, practically, for my purposes would allow me to occasionally change the font color and size of the data). Naturally, after running a macro, the other cell-formatting options are unavailable to me as well. Is there any way to get my manual selections to remain in place after running a macro that functions as mine do? Or is there any way to make my manual selections the default settings for a protected sheet?
I have a protected worksheet containing an SQL query. I have two cells on the worksheet unlocked so that users can enter fresh parameters to feed the query. Because I have to use MS Query to interrogate our SQL server (current IT decree), the query won't support parameters directly when the query cannot be represented graphically - so I have to manually edit the sql command text and then refresh the query - or at least that was why I wrote the code below
The problem: My macro won't unprotect the sheet before it calls the SQL.it therefore won't update the sheet to detail the DSN, command text and number of parameters (latter should be 0) - info just used to verify queryit won't run the query / return the data as the sheet is still protected Comment: I have had to remove sheet protection in order to allow the sql to execute and return data. If protection is removed, the query does execute and return data ( changes in the "parameter" cell contents do cause the query to be correctly modified) I could comment out the debug info but the query still doesn't return data if the sheet is protected.
Activesheet And worksheets(ndx) where ndx has been defined As ActiveSheet.Name I apologise If my post Is difficult To follow - especially the code.
I'm using Excel 2010 and I've got a chart on a worksheet and the worksheet needs to be protected. I'm able to manipulate the chart in any fashion EXCEPT the position of the Data Labels. Is this a bug in Excel 2010?
I need to make a excel costing model to calculate various products prices. I am using Excel 2010.
I have many products and do not need all of them displaying at the same time, therefor I inserted a Checkbox (ActiveX Control) that hides my columns that I do not need. I did that by inserting the following and it works fine :
The problem is I now need to lock certain cells so that they can remain fixed and the recipe cannot change.
Once locked my checkbox no longer works and I get the following error :
"Run-time error '1004': Unable to set hidden property of the range class"...
I am running excel 2010 with windows 7. I created a macro in sheet 1 and I wish to activate the macro from sheet 2 using a form button. I have entered the code below. I know how to perform this function on a more simple macro like adding names to cells. This code is a bit more complex I just dont know where to start.
I have an automatic macro that runs from every sheet but is only based on one sheet. I had to protect the worksheet and in order to make the macro work I had to add an unlock and a lock code. The problem now is every time a user enters something it looks back to the other sheet and then comes back. Is there a way to prevent the user from seeing this. See code below
Private Sub Worksheet_Calculate() Sheets("Profit Calc Local Currency").Unprotect Password:="hspricing1" Application.EnableEvents = False Dim lr As Long lr = Cells(Rows.Count, "XEA").End(xlUp).Row Range("$XEA$1:$XEA$" & lr).AutoFilter Field:=1, Criteria1:="1" Sheets("Profit Calc Local Currency").Protect Password:="hspricing1" Application.EnableEvents = True End Sub
On my work computer (Excel 2010, Win 7) when I am stepping through my VBA code using F8 and come to a Workbooks.Open line, the macro automatically kicks in and runs through to the end instead of just stepping to the next line of code. This happens nearly every time but occasionally it works as it should. To prevent this, I have to put a breakpoint on the next line following Workbooks.Open and after that I can step (F8) through the rest of the code. I have never had this problem before and it doesn't happen on my home computer.
I want a sheet to copy and paste into another worksheet, I have that code and its working fine, but what I also want to do is for the sheet to be protected,so the code would be, copy, unlock, paste, lock, save.
Here is the code I have so far. I know i have to include ActiveSheet.Unprotect "passowrd" ActiveSheet.Protect "password", but I dont know where to include that last part of the code.
I have 2 workbooks in Excel 2010, each contain just 1 sheet. (see attached) I need to compare on sheet 1, cell D1 and column A:A (this column will be much longer), with the data in columns C:C & A:A on sheet 2, if a corresponding match is found, the data contained in column D on the same row on sheet 2 is written to the cell with the matching data in sheet 1.
So I have a worksheet that has a lot of macros in it. I have just barely run into a problem that happens anytime a change a cell in another workbook that I have open. The second workbook I have open isn't even a macro enabled workbook. But for some reason it gives me an error and says I am trying to run this code.
It gives me the runtime error 9. This code is supposed to run when I click on a combobox in my main workbook but it is running anytime I change something in my other simple workbook. Why this happens and how to stop it? I have had a lot of similar problems in the past but I just dealt with it by only having one workbook open but in this case I will need both.
Is there a way to prevent the Workbook_SheetChange event macro from triggering when I run Excel's Solver?
The solver goes through 100 or so iterations changing multiple cells each of which seemed to trigger the Workbook_SheetChange event. If there was a Solver Run event, I could turn off application events, but as far as I know such an event does not exist.
Okay, I have this code and it works very well - but it only works if my WORKBOOK is unprotected. I know how to Unprotect then Protect an ActiveSheet, but I can't figure out how to Unprotect then Protect my workbook when the macro runs..
Sub BLM_RENAME_SHEET() Dim WS As Worksheet Application.ScreenUpdating = False For Each WS In ActiveWindow.SelectedSheets
The sheet shown, Complaints, lists on each row information on a single complaint. This information extends from Col A to Col Y (though not that way here, the data actually begins in row 3). I want to highlight a Customer cell, say H227 for Customer A, then activate a macro that (1) recognizes the active customer (here A), searches the list, identifies each row where A is the customer and then writes each row into sheet "Event" beginning in row 4. I should then see the same A to Y cells of information, beginning in row 4, filled in sheet Event for the six customer A complaints. Note, the actual data in sheet Complaints involves 300 rows of data, and over 100 customers can be on the list, some up to 15 times. I know that I could do this by filtering the data, copying and pasting, but a simple macro would be quicker for the group of individuals involved. Note, I then use this information to populate a chart.
Greetings I have a workbook with many sheets and i want a macro to protect all the worksheets with certain settings except for one sheet, sheet 3.
I have borrowed some code from this page that I cannot get to work and I would appreciate any help. the code should also have an if statement that unprotects sheet 3 but i don't know how to do that either. I just get syntax errors
I have certain cells which I want the formulas hidden to the end user and protected so they can't update them.
I've locked and hidden these cells and protected the sheet however my Macro's fail to work thereafter.
Protect the sheet for the user interface only. This has to be done in VBA:
- Activate the sheet.
- Activate the Visual Basic Editor (Alt+F11).
- Activate the Immediate window (Ctrl+G).
- Press Enter. - Type
- Press Enter.
The sheet is now protected for the end user, but VBA code can freely manipulate it.This works well however when I go to another page and do the same it stops working on the original sheet.I would like to know if there is an easy way to:
protect/unprotect all sheets in one go and have the macros still work?
When I protect pages individually I want the following to be allowed:
Select unlocked/locked cells Format Sort Use autofilter use pivottable
I will first save which cells are un/locked & hidden.
I have a sheet for data entry. On that sheet a button to run macro to scale the graphs on sheet 2. If I protect sheet 2 though because I am thinking of letting others use this program. It gives me an error 400.
I am creating a series of macros for a workbook, and at the beginning of each macro I unprotect all sheets and at the end of each macro I protect the sheets once more. The issue I am having is that even with these macros in place, I continue to receive an Excel alert message saying:
"The cell or chart that you are trying to change is protected and therefore read-only. To modify a protected cell or chart, first remove protection using the Unprotect Sheet command (Review tab, Changes group). You may be prompted for a password."
The macro will then run. Everything works as it should, but I do not want this error message to pop up. I have tried inserting
I have two macro buttons on a page that hide and unhide columns. When I got to protect the sheet .. the buttons no longer work and ask me to DEBUG. Is there a way to have macro buttons performing tasks AND have the sheet still protected?
In the protect sheet window .. am I have ticked is
I am getting an error when I protect a worksheet and save it. Upon re-opening the worksheet, I try to use the cells that are being formatted by a macro, but I get the following error...
Run-time error '1004':
Unable to set the NumberFormat property of the Range Class.
If I open the worksheet and it is already protected from when I last closed it, then I get the error. If I unprotect the sheet, then close it and reopen it, then I don't get the error. Does anyone know what I might be missing or doing wrong.
I have used a small macro to format the sheet to hide cells with no entries and then print. The problem is that when the sheet is protected, then macro wont work. The sheet needs to be protected by the end user. I am only really learning excel, I could add to the following so the sheet can remain protected?
Using excel 2003, Workbook has several sheets, all sheets are protected workbook is not. I have my cells containing formulas locked, I have unlocked the other cells that require input of various data. When the user goes to type in a "unlocked" cell, (to over write the now obsolete data) it will not allow the user to delete the data, the curser remains a cross and the formula bar or the cell itself will now allow any changes.
I have double checked that the cells are unlocked. I can enter data after the last entry in the unlocked row and or column, but I want to be able to "cut or delete the data that is no longer need and begin entering data from the beginning cell of choice. I want to copy this "old" data to an archive sheet, but it will now allow me to select it.
Example: I have 6 columns starting, (a to f) the formulas are in columns e and f and start at row 4 These columns are locked and hidden. Columns a,b,c and d are unlocked for user input. All data is started from A5 which I want it to start from each time the old data is deleted and new data entered. If I place my curser on any of the cells in the e and f rows, the cursor remains a cross which is what I expect it to do, but columns a to d are doing the same thing, they are acting as if they are locked OR atleast those cells in the a to d areas which have existing data, as I can enter below.
I am trying to design a tool which dynamically results in different cells depending on users inputs. Yet, I am willing to protect most of the cells to avoid any user involuntarily (or voluntarily!) modifying the formulas/contents of the calculated (and locked) cells. Is there any way to protect my sheets, allowing only a certain macro to modify the locked and protected cells?