following code so as to have certain cell ranges protected and unprotected USING COMMANDBUTTON/PASSWORD
Code:
Private Sub CommandButton1_Click()
Dim Password As String
JamesPassword = "MyPassword" '
[Code]......
i'm looking for a vba code to solve the following problem a .xlsm workbook, the protect workbook passwork is known as "111111", the VBA project password is "222222", and how to write vba code to insert a new sheet, suppose the workbook has three worksheet, now using vba code to unprotect workbook, and auto insert a newsheet, then protect workbook again, whcih the password remains as "111111" using a commondbutton to do this, everytime you click, insert a new sheet.
View 9 Replies View RelatedOkay, 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
[Code] ......
I am working on my spreadsheet and was wondering if anyone knows if cells can be Protected or Unprotected based on the value of another cell?
Here is what I am looking for:
If cell "A16" on the "Input Sheet" equals AR then Protect Cells J16, K16, L16, M16, N16, and O16
If the value of A16 on the Input Sheet DOES NOT equal AR then leave those cells unprotected.
Has anyone ever done anything like this? On my spreadsheet it would allow the user to skip the unnecessary cells as opposed to having to Tab through them. Not a big deal but definitely would be a time saver and a lot more user friendly.
If you have any questions or concerns just let me know. I will wait to see what the Excel Guru's come up with.
I am using the following code to protect/unprotect a sheet in a tool that i am working on, which seems to work great. It unprotects the sheet runs the code inbetween the two liens of code and then protects the sheet again.
VB:
ActiveSheet.Unprotect Password = "mypassword"
ActiveSheet.Protect Password = "mypassword"
The issue I am having is if i want to go in and edit the sheet without running the code it will not let me. When I go in and type the password to unprotect it says it is invalid. I have typed it exactly how the code reads. I have even tried including the quotes but no luck.
I have a worksheet where the users need to enter time. I have a macro that captures the time when the user hits Ctrl + t
What I want now is a code which protects the cell after entering the time with Ctrl + t.
I tried to do it, but my macro only works when I don't set any passwords to protect the sheet. How can I pass the password to the code and protect the sheet after the code to capture the time is being executed.
Is there is any way we can protect sheet in such a way that; if user - Mr. A inputs his password he can only go and is able to input his data in sheet 1.
I've a file which have multiple sheets say about 80 tabs are present. My colleague only works on one of the tab (sheet) enters data, that file is placed on our general sharing folder I want to know if it is possible that I can assign protection in such a way that when she enters her password she is able to input data only on the sheet in which she works and cannot roam around to other sheets?
Or is it possible that on first sheet there is username or password can appear? And if the data inputting person opens that file and enter her password option appear which can take her to that particular sheet? And if a guest open that file he/she can only view particular sheets which contain reports?
I want the excel spreadsheet to "BE Protected" in standard form, and only be "Unprotected" when macros are enabled -- BUT DONT JUMP TO CONCLUSIONS, hear me out, it gets deeper than that
If the user does not enable macros, I want the book to be protected. If they do enable macros, it becomes unprotected.... (On load is good enough, no need to be real-time)..... But, they can save the book, so it would no longer be protected , which is where my question comes in ...
I could use the -unprotect "password"- on load when macros are enabled but this will only work if the spreadsheet was originally protected and is never saved again. This is a problem because this workbook will be given to others and will be saved time and time again.
So, I had a vision :-) .... function 'before save' , protect the sheet using -protect "password"- , allow the sheet to save, and then unprotect back using 'unprotect "password"' so you can continue using - BUT, the problem with this is now I have created an endless loop in theory, because after it unprotects it would want to re-save again because it changed
I am looking for a way to protect and unprotect all worksheets with a password through a macro. I have excel 2007.
View 2 Replies View RelatedI have a file that needs to be locked down so that the end user does not have the ability to enter data where they shouldn't. The end users are very Excel illiterate, and have proven that I need to have high security in place. Here is my current VBA code - the purpose of which is to hide/unhide rows depending on data entries. (Disclaimer: this code may not be pretty as I am a VBA rookie, and I've begged borrowed and stolen from about 10 other threads on this board to get where I am, and yes it works).
Code:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "C7" Then Call Changeto1
If Not Intersect(Range("c9"), Target) Is Nothing Then
Rows("15:44").EntireRow.Hidden = True
Rows("15:" & Range("c9").Value + 14).EntireRow.Hidden = False
End If
[Code]...
The question is now....how can I unprotect the sheet so that this code works without allowing them to enter data where they shouldn't. I've been at this for days already.
I have a workbook where I password protect each sheet. Is there any way to quickly unprotect all sheets, make changes then protect all sheets. I cannot seem to do this by grouping multiple sheets. the option to unpropect/protect is not available.
View 6 Replies View RelatedIs there an easy way (using a macro) to unprotect and protect sheets?
Most sheets in a certain workbook are protected except a few cells that are open for manual entry. Because I have links to other cells in other workbooks, I must unprotect those sheets/fields before I can edit the links. And I MUST ensure all sheets are protected again when I'm done with my work.
Also, is it possible to password-protect that specific macro so I'm the only one who can run it to unprotect and protect?
I have a series of workbooks where multiple users are entering data. Most of the cells are locked, and the worksheets are password protected. From time to time, I have to make global changes to all the workbooks. I've recorded a macro to open all the workbooks, but now, I'm running into a problem because for speed, I'd like to unprotect the worksheets and "re-protect" them without having to enter the password 3 million times (exagerration, but not much). What is the proper syntax of code to have VBA enter the password automatically (it is the same for all sheets).
View 4 Replies View RelatedThe first macro protects all sheets in a Spreadsheet and the second unprotects. The the next Macro Protects the enitre workbook and likewise the next one Unprotects. All using passwords.
VB:
Dim ws As Worksheet
Sub ProtectAll()
Dim S As Object
[Code].....
I have this macro code that can protect and unprotect, It will show the input box and ask for a password. my problem is when I choose cancel, it still protect the file, the same thing with my unprotect code. here is my code below
Protect Code :
[Code]...
Unprotect Code :
[Code]...
I am having some 60+ workbooks in which the end-users are entering data. Each workbook is protected with different passwords.
We have the systems networked. The main compilation file is in the server D:MAIN folder. The workbooks for users are in D:Mainusers sub-folder. The files in users folder are only allowed to have access by the users.
Now, in the compilation sheet I have linked the data from all the individual files (which is in its subfolder). I am facing a trouble that the excel asks password for each and every workbook to update the links in the compilation workbook.
Any possibility to overcome this trouble? A VBA code is the solution? or any other alternative measure is there to solve?
Create a button for protecting the worksheet and a button for unprotecting the worksheet
View 13 Replies View RelatedStumbling over syntax on what should be easy. I want to password protect the active sheet using plain vanilla user input box; and then unprotect ALSO via an input box. Doesn't matter about masking the input with **** or whatever. Based on responses below, here is macro code I used:
To protect the sheet:
Sub ProtectSecurity()
Dim pword As String
pword = InputBox("Enter Password", "Password")
ActiveSheet.Protect pword
End Sub
---------------------------------
To Unprotect the sheet:
Sub UnprotectSecurity()
Dim pword As String
pword = InputBox("Enter Password", "Password")
ActiveSheet.Unprotect pword
End Sub
I experience it takes a lot of time to protect/unprotect sheets in Excel 2013. However, only if the operation is made WITH PASSWORD. This was not the case in Excel 2007. Operation was fast independent with/witout password.
In attached file I have made an example where the time to protect/unprotect 25 sheets is investigated. Can make the test in Excel2013 to double check it is not my computer causing the time delay.
Result test - 25 sheets:
Protect - No password: 0,02 s
Unprotect - No password: 0,06 s
Protect - With password: 6,9 s
Unprotect - With password: 6,9 s
I need a macro that I can have in any workbook enabling me to protect / unprotect the workbook that I have currently opened with a set password (let say "Password")
I would link this macro to a button in excel 2002. I have try the following but it doesnt work
Would it be possible that the button (first) works (second) understand whether or not protect / unprotected and do the opposite?
Sub Protect
Activeworkbook.protect password:="password", structure:=FALSE, Windows:=false
End Sub
I am trying to create a way to protect and unprotect all sheets and the worbook by means of a Userform pop up interface.
I have managed to create the Userform but what the code will look like and where to place the VBA code that will make it work, Also i would like to automatically save the sheet/s as i switch between them. so that any updates made to the sheet will be automatically saved when i move to the next sheet and also when i protect or close the workbook.
I have an annual leave (vacation) work book with a summary page work sheet and separate work sheets for each month i.e. Jan, Feb, Mar.....to......Dec. In order to protect the formulas I have protected them by allowing only access to the input cells on each work sheet and the protect each work sheet.
When someone either joins or leaves the team I have to manually unprotect each sheet and protect again when I have completed the amendments to each of the 13 tabs.
macro code I would need to unprotect all the works sheets in one go (as I use the same password for all the sheets) and reset the passwords (protect) the sheets with more macro code.
I will be running the two macros from my own personal.xls file and ideally they would be fully automatic i.e. I would not need to input the passwords in to unlock or lock the work sheets as the password would be written in the code already
Excel version 2003
I have been really trying to get this sorted myself and I can get it to work with one exception. I can't get the sheet to lock with a password. It locks, but if I try to unlock it again, it does so without prompting me to enter a password.
Essentially I am wanting to unprotect two sheets, unlock the cells that were previously locked (so users couldn't enter data in them when the sheet is protected), then reapply the same password to protect the sheet, but now they will be able to enter data in the unlocked range of cells).
The second macro is to reverse the changes made in the first and 're-lock' the unlocked cells) (and again apply the same password to the sheet).
this is the code I have used.
Sub PART_YEAR()
Sheets("2012 Calculator 1 week").Select
ActiveSheet.Unprotect "taado"
Range("D20:D23").Select
Selection.Locked = False
Selection.FormulaHidden = False
Sheets("2012 Calculator 2 week").Select
[Code]...
I am assuming I have superfluous code in there, in addition to my mistake with the reapplication of the password.
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.......................
I currently have a button then when pressed automatically sends a summary report taken from the first page of Sheet 1.
Worksheet needs to be protected all the time, but Macro only works on an unprotected worksheet.
I was wondering what additional code and where to put in so that when
protected back again after Macro has been executed?
Here’s the Macro taken from [url]
Toggle "Protect/Unprotect sheet" in macro
Correct the following macro. It does not work.
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 a single row with 52 columns. On single entry on each of these cells in the the row, the content of the cell has to be made uneditable. The data entered should be visiable for review.
View 5 Replies View RelatedWorkbook performs perfectly 95% of the time but every now and again I have this issue where the formular do not calculate (it is set to automatic).
I have a workaround of doing a find and replace across all the worksheets of "=" replaced with "=" and this solves the issue.
The problem I am having is that some of the sheets are protected and some are not. The code I am using for the find and replace is as follows...
VB:
Option Explicit
Sub ChgInfo()
Dim WS As Worksheet
Dim Search As String
Dim Replacement As String
[Code] .....
I need to add to this code the unprotect command and then reprotect only the originally protected sheets?
I am sure these is a better way of doing this but im really short on time. This issue only occurs 5% of the time. the other 95% the formula all calculate fine!
I just need a way of doing the find and replace for = to = across all sheets in the workbook regardless of the sheet protection!!
I have an email vba code set up in my workbook that emails the worksheet in an attachment to someone. However, I keep getting an error code saying permission denied when the code tries to finish. It highlights the "Kill Tempfile" field...which in my mind is saying permission denied because I have the worksheet password protected. I kind of want to keep it that way, so is there a way I can enter a line of code in my current script to unprotect what is being emailed in the beginning of the code so that the tempfile created when it is being emailed can be deleted?
Here is my current
PHP
Private Sub CommandButton1_Click()If Len(Range("H59")) = 0 Or Len(Range("H61")) = 0 Then MsgBox "Please enter customer/employee submitting request and click SUBMIT again." Exit SubElse' Enter the rest of your code hereActiveSheet.PageSetup.BlackAndWhite = TrueApplication.Dialogs(xlDialogPrint).ShowSet appWrd = CreateObject("Word.Application")Set docOpen = appWrd.Documents.Open("F:groupsdeptservCDTISA.doc")docOpen.PrintOutdocOpen.CloseappWrd.QuitDim FileExtStr As String Dim FileFormatNum As Long Dim Sourcewb As Workbook Dim Destwb As Workbook Dim TempFilePath As String