Macro Wont Work When Sheet Protected
Sep 19, 2006
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?
Intersect(ActiveSheet.UsedRange, Range("O:O")).Offset(0, 1).Select
Range("O39:O155").Select
Selection.EntireRow.Hidden = True
Columns("O:O").Select
Range("O21").Activate
Selection.EntireColumn.Hidden = True
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Cells.Select
Range("A21").Activate
Selection.EntireRow.Hidden = False
Columns("N:P").Select
Range("N21").Activate
Selection.EntireColumn.Hidden = False
End Sub
View 4 Replies
ADVERTISEMENT
Feb 3, 2014
I am using Outlining on a sheet called "Today".The sheet is manually protected but now the outlining does not work as it says sheet protected.Is there a code i can use so that i can still have the Outlining working in a protected sheet.
View 5 Replies
View Related
Jun 5, 2014
I cannot run a macro because I am encountering error 1004, intersect of object. i am using a protected workbook, and I dont have the password to unprotect it, but it needs to be fill out using a macro from a different workbook. Is there a way around this?
View 1 Replies
View Related
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?
View 4 Replies
View Related
Mar 27, 2009
I have protected several sheets in my workbook to protect the forumlas and data for being tempered with by the users.
When I try to run my macro, I get an error message stating:
Runtime '1004'
You can not use this command on a protected sheet
And the debug is pointing to this part of my code
View 14 Replies
View Related
Sep 19, 2013
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).
- Type
ActiveSheet.Unprotect
- Press Enter.
- Type
ActiveSheet.Protect UserInterfaceOnly:=True
- 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.
View 9 Replies
View Related
Mar 6, 2007
I'd like my button-triggered macro to exit and quit IF the sheet it is on is protected when someone presses the button. Here's the idea:
IF the active sheet protected THEN
Display "Can't do this on protected sheet" (and exit macro after click OK)
Else
run the rest of the macro.
View 4 Replies
View Related
Aug 22, 2007
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.
View 9 Replies
View Related
Aug 5, 2008
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
Application.DisplayAlerts = False
into the code, but it does not stop the message.
View 9 Replies
View Related
Nov 22, 2009
I am currently using a macro to copy a sheet from a closed workbook in to my current workbook. However this copying is based on the sheet name. At present when I run the following code
View 11 Replies
View Related
Jan 14, 2010
I tried to use the code that allows macro to run even when the sheet is protected.
View 3 Replies
View Related
Sep 9, 2008
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.
View 9 Replies
View Related
May 5, 2014
I have the following macro so that we can use the outline feature without having to unprotect a sheet.
Private Sub Workbook_Open()
With Worksheets("Schedule")
.Protect Password:="APQP", Userinterfaceonly:=True[code]....
However once the macro is run, filtering will not work without unprotecting the sheet first. If you choose not to run the macro, filtering will work without unprotecting the sheet however the outline feature will not. what I need to add and where to make both the outline and filter features to work without having to unptotect the sheet?
View 1 Replies
View Related
Feb 23, 2007
I've got a work book with numourous password protected sheets which i quite often have to update (as i use around 10 of these with at least 10 sheets in each its time consuming). I've had several goes at creating 2 macros to unlock and then lock the sheets (with my password). I've managed to create on that unlocks the sheets using the password but i can't get a macro to lock the sheets up again using the password.
View 9 Replies
View Related
Apr 1, 2008
how to unprotect the sheets in order to be able copying datas to to certain cells.
The unprotection works but only when I start the macro the second time. I do not understand why.
After I did the updates I want to reprotect the sheets but that seems not to work in my case.
ActiveSheet.Unprotect Password:="my_password_here"
'unprotect the sheet
ActiveSheet.Protect DrawingObjects:=True, _
Contents:=True, Scenarios:=True, Password:="my_password_here"
' Reprotect the Sheet
View 7 Replies
View Related
Dec 13, 2007
I have a Workbook that I use to log invoices and credit notes and it is password protected so that the cells that contain formulas are locked and therefore not selectable.
What I wanted to know is. Is it possible to lock other cells depending on what values are in certain cells?
Let me explain. I have Columns G & H which contain the heading 'Credit Note Number' & 'Credit Note Amount'. Now in columns I & J I have the heading 'Invoice Number' & 'Invoice Amount' respectively.
What I have done is to make a condional formatting so that If I put a value in cells in column G it will change the colour of the adjacent cells in columns I & J, likewise If I fill in cells in column I the conditional formatting will change the colour of adjacent cells in coulumn G & H. This helps me to see whether a row contains a credit note or an invoice.
What I would ideally like is for a Macro that would automatically locks cells G & H in a particular row that I am currently working on if I enter data into the cell in column I. Likewise if I am entering data in column G in the row I am currently working on for a credit note, then lock columns I & J in the row I am currently in.
Any Ideas how this can be done? Also can locking and unlocking of cells be done in VBA while the sheet is protected or would the macro need to temporarily unprotect sheet, lock the cells then protect sheet again?
PS: I have uploaded a Jpeg of what my conditional formatting does to try and illustrate what it is I need.
View 14 Replies
View Related
Feb 21, 2007
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.
Sub AgedStockParameters()
nmb = ActiveWorkbook.Name
ndx = ActiveSheet.Name
Workbooks(nmb).Activate
Worksheets(ndx).Activate
ActiveSheet.Range("A1").Select
WkshtQryCon = Workbooks(nmb).Worksheets(ndx).QueryTables(1).Connection
ActiveCell.Value = WkshtQryCon
Range("A2").Select
WkshtQryFld = Workbooks(nmb).Worksheets(ndx).QueryTables(1).CommandText
ActiveCell.Value = WkshtQryFld.......................
View 2 Replies
View Related
Oct 24, 2012
Excel 07
I inserted a hyperlink into a cell that goes to a web page. It works when the sheet is NOT protected, as soon as I protect the sheet it stops working. How do can I lock the workbook and that cell so nobody can change it but the hyperlink still work?
View 2 Replies
View Related
Dec 29, 2009
I have a range of cells in a work sheet "sheet 1 " my objective is to filter this range according to certain criteria (i ve succeeded to do this ) yet what i want to do now is copy this data to another existing worksheet in a certain range .
note :the existing worksheet to which i 'll copy the filtered data has some cells out of the range that i dont want to over write ..
Simply :how to copy a selected range of cells in a work sheet to already existing work sheet in a specific range aswell .
View 9 Replies
View Related
Jun 24, 2014
I have the following Macro:
Code:
Private Sub Worksheet_Change(ByVal Target As Range) ' Code goes in the Worksheet specific module
Dim rng As Range
' Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")
Set rng = Target.Parent.Range("C13:D25")
' Only look at single cell changes
If Target.Count > 1 Then Exit Sub
' Only look at that range
If Intersect(Target, rng) Is Nothing Then Exit Sub
[code]....
Which does not allow entry to range C13:D25 if D12 is empty. Works great!But I need it to work across ranges E13:F25 if F12 is empty, G13:H25 if G12 is empty etc.
I tried copy pasting the same macro over and over again but renaming it to e.g. Worksheet_Change2 causes the Macro to stop working completely.What am I doing wrong?
View 5 Replies
View Related
Apr 13, 2009
I have a macro code for conditional formatting. The first 2 lines of the macro are
Private Sub Worksheet_Change(ByVal Target As Range) ....
View 9 Replies
View Related
Jul 9, 2009
We have a sheet which we use to cost products. To ensure that people don't enter prices incorrectly, I have created a lookup to another sheet which shows current prices. Therefore, I have a macro that if I click a button, it looks up the prices from the relevant document. The way I have written the macro is to clear the sheet of what is already there and then to open up, vlookup and then close the lookup sheet.
The problem I had with this was that if i renamed the sheet, the macro wouldn't work - I sussed that one out by changing filename in the macro to 'ThisWorkbook'.
The problem I now have, is that the boss would like the sheet to magically do the following;
Lookup the prices on two external sheets. IF the main sheet doesn't have the corresponding reference THEN automatically go and Lookup on the other sheet for it.
He doesn't want any references to filenames so that, as long as the sheets are stored in the same place, they can be copied/renamed and moved anywhere.
View 9 Replies
View Related
May 24, 2006
Well this excel workbook has lots of sheets each for a specific region eg, Europe, austin etc and also sheets which have relevant data that gets used in the formula.
Now, say for eg lets considered the sheet called austin.
it does the calculations on the headcount needed for call center.
so the columns refer to a formula called gets() which then calls the erlanc function.
Now the problem here is this gets() function takes a value called calc
and this calc just a cell in one of the sheets called table. Now if I change the headcount value in the austin sheet nothing happens. Then I need to change the value of calc cell say make it to 5 or 6 and hit enter. It starts calulating the values to forecast thye headcount, but it does so for all the sheets . so it is taking a lot of time.
how to get make it run only for one sheet.
View 9 Replies
View Related
Nov 23, 2009
I have a macro code which gets the file list from a folder i specify and puts it in to an excel sheet as a column. I have then made a drop down list from this so the user can select the file they want.
From this file i wish to copy the data on a sheet that i specify. For example the sheet "dump" from file FR7_19.11.2009.xls (which will be a closed workbook) and paste its content in to the sheet "dump" in Summary.xls
I have a macro which opens up the closed workbook FR7_19.11.2009.xls and copies the sheet "dump" and then creates a new sheet of the same name and content in my current workbook (Summary.xls). However when i wish to select a different file to load in to Summary.xls "dump" the formulas i have been calculating information from this sheet all come up with #!Ref errors. I know this is because the macro i use deletes the old dump sheet before re adding a new one containing new data.
I am therefore looking for a macro which will simply just copy and paste the data from any file i select in to a sheet named "dump" as the data is always set out the same in every file but the values are different. I assume this will then mean that any formulas i use relating to this "dump" sheet in Summary.xls will work because the sheet is no longer being deleted and re-added the data within it has just simply been copied over.
View 2 Replies
View Related
Aug 17, 2006
When you enable macros the sheets 2, 3, 4 ect are visible but if you disable macros, you only see sheet 1 and and you can place a message on the sheet saying this will only work with macros enabled.
View 2 Replies
View Related
Jan 2, 2009
I am want to copy a formula across several work sheet and have the formula always take data from previous work sheet.
2) I am working with this formula =C12+INDIRECT((MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)-1)&"!"&"C12")
and it comes from this thread http://www.excelforum.com/excel-gene...orksheets.html. I have included a worksheet attachment that has explanations
View 2 Replies
View Related
Aug 24, 2009
i have a made a macro that copies info to a new sheet now that is working great but if i change the name of the work book it wont work any more so i need the macro to work with what ever name i give the workbook
the current name is
AVERAGE PRICE (update 2009) Mimmos Armico 170809.xls
i have attached the code in notepad ...
View 8 Replies
View Related
Apr 29, 2014
I have spent ages creating a work diary and have protected the sheets using VBA however on finishing the sheet one important thing that needs to happen is the ability to copy and paste information from the boxes to another worksheet. i.e MONDAY - SUNDAY
View 1 Replies
View Related
Jan 18, 2008
Does anyone know if there is a way to unprotect a sheet. The person who created this spreadsheet no longer works for our company and although we can see the spreadsheet, the formulas are hidden so we can't figure out where the data is being pulled in from. Any ideas?
View 9 Replies
View Related
Jun 11, 2008
is there a code to still allow me to move between sheet tabs by using [alt] pg up or [alt] pg dn on a protected sheet?
View 9 Replies
View Related
