Protecting A Worksheet With A Macro
Oct 27, 2009I am looking for code which will protect a worksheet based on the value of a particular cell.
i.e. If cell A1=1, then the worksheet will be protected (ideally with a pswd)
ADVERTISEMENT
Protecting Worksheet With Macro (Run Time Error '1004')
Jan 28, 2009I am using the following code to enable users of a form to made make selections by adding a tick in one of the boxes by clicking on it. It works fine with thesheet unpotected but when I protect the sheet with cells E33:k33 unlocked i get Run Time Error '1004'
Unable to set the Name property of the font class.When I debug, Target.Font.Name = "Marlett" is highlighted.
Protecting A Worksheet That Contains Macros
Jun 5, 2009I need to protect the attached to stop people deleting the formulas. The problem is that there are hidden macros and when i protect the cells the conventional way they stop working and i lose all the data in the IAS sheet. I have highlighted all the cells in yellow that i want to be able to change once protected.
At this stage I would just like some one to explain how to protect it. Ideally I want to do it normally (unlock the cells individually that I want to remain unprotected, then protect the whole sheet). If its not then presumably its a macro alteration that's needed?? don't do it at this stage as depending on how it will be protected will mean that i need to make a few adjustments.
Password Protecting More Than One Worksheet
Dec 22, 2008I have set up a workbook with a tab for each month. in the workbook open module i have the following code (below marked Password protect) that allows for three passwords to unlock certain ranges of cells, what i would like to do is for this code to apply to each worksheet.
Now in the main this works except for one range of cells H11 to H1000, on every sheet all cells are locked except for this range which is unlocked in every sheet except sheet one when you enter the password staff. The only other code i have is for a time stamp (marked time stamp) so that i can monitor when the user has entered data. this again has reference to the sheet name.
their must be something really straight forwad i am doing wrong here as everything works on an sheets except this range being locked.
I am in excel 2002 but my users are on 98, which is why i am protecting in VB rather than using the prtect range option.
Protecting Worksheet - With Password
Oct 26, 2009modify the code below to do the following:
When I recorded this macro shown below, I entered in a password (twice) as prompted by Excel 2003.
So when I run this code, it seems to work fine. However, when I want to unprotect the worksheet, I am not prompted to enter in the password.
modify the code so that when I need to unprotect the worksheet, I am prompted to enter in the password?
Protecting Worksheet But Only Cells
Jul 21, 2006I have a spreadsheet with about 2000 lines of vba code. My application needs to be protected so users don't mess themselves up; however, I really only needs the contents of cells to be protected. I have many macros that run, and in order to do this I need to unprotect and re-protect the worksheets. My problem is this: when I re-protect the worksheets, everything is locked up. I would like to allow users to change column widths, hide and un-hide rows, etc. I can't find the parameters needed to do this more controlled type of locking. Is there a list somewhere of all the parameters that can be passed to the Protect method? Here is my current subroutine:
Sub ProtectSheets()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Protect Password:="password"
Next
End Sub
Protecting Given Area Of Work In Worksheet?
Apr 17, 2014I am working in the D3:K20
I have set my column width at 3.6 and row height at 19.6
I want to protect this area so that the column width or row height don't change.
Using Text Box Whilst Protecting A Worksheet
Jan 25, 2008I wish to protect a worksheet to prevent loose fingered people from altering formulas, but I want the "text box" that is below these formulas (within the same worksheet) to be able to be typed in.
But when I protect the worksheet, the text box cannot be typed in.
Is there a way around this to allow typing in the text box whilst the sheet is protected?
Excel 2007 :: Protecting Worksheet / Workbook On Save?
Nov 24, 2011Intention is to (automatically) fully protect each completed worksheet/workbook on "save"/"save as" in MS Excel 2007... Is this possible by means of a macro? Which one? Saving in a ".xlsm" format is required?
View 9 Replies View RelatedMacro For Protecting A Sheet
Dec 23, 2009Excel 2002
I have a sheet that has some cells already protected by a password. I want another person to be able to make changes to the unprotected cells and then be able to push a button (macro) that will then unlock the sheet activesheet.unprotect ("mypassword"), change all the cells to "locked" and then have the protect sheet window pop open so that they can type in their own password and save it. Is that possible?
Protecting Sheets [Macro Fix]
Jan 22, 2010Private Sub Worksheet_Activate()
ActiveWindow.WindowState = xlMinimized
If InputBox("Pleas enter the password: ") "Password" Then Sheets("OkToSeeThisOne").Activate
ActiveWindow.WindowState = xlMaximized
End Sub
This is the code i enter into 3 sheets that i don't want ppl to see unless they know the password but if they press cancel the End on the debug error they can view the sheet lolz. How can i stop them actually viewing it at all?
Protecting Macro-Enabled Workbook
Apr 29, 2012is it possible to protect a macro-enabled workbook with password.
View 5 Replies View RelatedMacro For Protecting Sheet (with Same Name) On Multiple Files At Once?
Jul 19, 2014I have a macro that duplicates a excel file into how ever many files I need... so If I need 150 copies of the the file, it will make it 150 times.
However, each file the first tab called "Clients" has information that I want protected so users can't alter it. I can't protect it before I run the duplication because the data is added by a group of people and I need it unprotected until they add the information.
What I am looking for is a macro that I can click and run, allows me to select multiple files, then it will automatically Protect Sheet 1 "Clients" on all the files I select so I don't have to open all 150 files after data is entered and Protect sheet 1 manually.
Macro Protecting Multiple Sheets In Workbook?
Sep 6, 2012I have a large workbook with 10+ sheets i need to protect all at once. I have created a macro in VBA below. It works, but I would like another user to be able to change formatting on the sheets while they are protected. below is my macro.
Sub ProtectAll()
Dim wSheet As Worksheet
Dim Pwd As String
[Code].....
Macro To Prompt For Password When Protecting Sheet
Nov 12, 2009Macros are just not my strong suit. I can usually find my answers here but this one has me stumped. I have rewritten this thing everywhich way but loose and this is my latest version.
Sub SetProtect()
'
' protect Macro
' prompts for password protection
'
' Keyboard Shortcut: Ctrl+p
'
Dim Response As String
Dim Answer As Integer
SetProtect:
Response = InputBox("Enter password")
If Response = "safety2" Then.......
Worksheet_change: Macro In One Worksheet To Run When Any Cell (in A Given Range)on A Different Worksheet (dataentry) Is Updated
Mar 27, 2007I want a macro in one worksheet to run when any cell (in a given range)on a different worksheet (dataentry) is updated. I have spent along time trying to make it work with no avail. The code I use to start my macro is as follows.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count <> 1 Then Exit Sub
If Target(1, 1).Address = "dataentry!H5:IV72" Then
If Not Intersect(Target(1, 1), Range("dataentry!H5:IV72")) Is Nothing Then
On Error Resume Next
Application.EnableEvents = False
Macro Which Saves Data From Temp Worksheet To Customers Worksheet?
Mar 17, 2013Dropbox - Final.xlsm
Here is the above link. Am looking at a button which saves whatever is the temp worksheet row in the customers worksheet. The temp worksheet basically takes the data from the Quotations worksheet and places it in a row.
Macro To Copy Six Specific Columns From One Worksheet To Another Worksheet
May 12, 2008What I am trying to do is to write a macro that will automatically copy six columns from worksheet (Sheet 1) to another worksheet (Sheet 2). i.e. ‘Description of Project’, ‘WBS Code’, ‘Rate’, ‘Employee Name’, ‘Premium’, ‘Invoice’, ‘Status’, ‘Total Cumulative Hours’, ‘Total Cumulative Amount’ from Worksheet (from Sheet 1 to Sheet 2)
The problem arises as I know the names of the columns to be copied in Sheet 1 (as details above) but they can be in any order in sheet 1.
In additional the columns ‘Total Cumulative Hours’, ‘Total Cumulative Amount’ are total columns so when they are copied from ‘Sheet 1’ to ‘Sheet 2’ their values should be copied as opposed to the formulas
Protecting From Modification
May 18, 2009I would like to protect the worksheet from modification by other users except column A ( suppose A1: A15).
I have been doing this by removing Locked on format cell and then Protect Sheet on Review tab.
Is it the right way or there are other more reliable way?
Protecting And Unprotecting With VBA
Feb 6, 2010I have created a form that allows users to input data to an excel sheet, and also will enable users to view the data once they are done.
Is there a way to unprotect the sheet using VBA so that the data can be written, and then protecting once the data has been input?
Protecting Workbook
Feb 4, 2009I've got about 50 worksheets in a workbook and I'd like to protect them all.
Problem however is that I've got buttons in my worksheets that insert rows, delete rows.
I've also got a dynamic chart with listboxes that select data.
When I protect my worksheets all this is disabled.
Is there a way around this?
This is what I've done so far:
[url]
Protecting Cells
Jul 17, 2002Is there a way to just protect just a single cell, in order to lock & hide a formula within that cell, without having to password protect the whole worksheet?
View 9 Replies View RelatedProtecting Worksheets
Nov 27, 2006I have a bunch of worksheets in my workbook say around 50
Each worksheet has the same template layout!
I am trying to figure out a macro that will select all worksheets from 'KLKN':'KFSN' and protect the cells L45:L52 accross to AL45:AL52
Unfortunately I cannot select all worksheets and protect them, instead I would have to manually do this, or create a macros that will do it for me...
Protecting All Sheets
Jul 1, 2007I am trying to protect all sheets on a Workbook Close event.
I don't want the user to even be able to Select the Loked cells so I set it up that way through formating of cells.
This is what I have tried:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim sh As Worksheet
For Each sh In ThisWorkbook.Worksheets
sh.Protect Password:="P@ssword", DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells
' sh.Protect Password:="P@ssword"
Next sh
End Sub
I even tried the commented out line in that postion instead of where it is now.
It does protect but still allows selection of locked cells.
Protecting The Workbook
Aug 12, 2007when protect a workbook and select the option for window the window then only appears as about two thirds of a full window?
Is there a way in which you can protect the workbook, select the window option and have the window appear as full size?
Protecting The Document
Jan 15, 2008Version used: Excel 2003
I have a spreadsheet that everyone in my department uses. My boss has asked me to protect certain columns so that only I can edit them but leave others so that the rest of the depratment can use those.
I have tried locking the ones I am to use and unlocking the ones the others can use then protecting the document. This works well enough as far as it goes, however, it is combersome having to unprotect then reprotect it every time I need to work on it. Also, that method makes the drop down lists that we are using for easy sorting no longer function.
Protecting A Workbook
Feb 6, 2008I have a very large workbook - there are only five sheets of this workbook that should be viewed by anyone. What I would like to do and just can't seem to find a solution is...
I would like to set the workbook so that when opening I use a password which will give me full access to anything in the workbook - upon read only, a user would be able to open the entire workbook without a password - but only view it's contents - not be able to do any adjustments.
I have locked cells which I do not want anyone to adjust the formulas to, then protected the sheet - this works, however, because there are so many sheets, it becomes very tedious to unlock and lock sheets to work in them.
Protecting A Sheet ..
Mar 24, 2008I have a Sheet with some Macro Buttons for the boss to use.
How can I protect the sheet from any Data Entry changes, but still allow the Buttons to be used?
I tried a few scenarios and in all, the Buttons can't be used.
Protecting Fomulars
Apr 22, 2008I have a work sheet with formulars on column A,B,E and F,(from row 19-119) Column C and D are for inputing data. How do I protect the the columns with formulars at the same time be able to input in column c and D.When I try using protect worksheet it protects all including the one for imputing data..
Protection Not Quite Protecting
Jul 24, 2008I have a workbook and i have spent time protecting all the formula cells and allowing user to only be able to select unlocked cells (so they can quickly tab to where they need to input info).
I have lots of codes running and always use Activesheet=unprotect If i am modifying protected cells and then at the end of the code i put back the activesheet=protect.
HOWEVER
When i transfer this book to another machine or to someone else to run it on their machine - ALL of my protection GOES OUT THE WINDOW
Also - is there a way to hide or protect all my code so that users cannot modify it?
If so - how do i get it back to modify state when i want to edit it?