does anyone know if I can do something like this:
Sub warehouse()
Dim Flag As Boolean
If .user = "martin.lucas" then _
Range("H:H").unprotect
Range("K:K").unprotect
Range("N:N").unprotect
Set Flag = True
End if
If Flag = False then _
Msgbox("you are not permittedd to do this. See Martin Lucas")
Set Flag = False
End sub.....
Any way to conditionally unlock and unprotect certain cells in a column. I have been unsuccessful with my attempts at customizing other people's vba code that is a little different than what I am trying to achieve. So here's what I would like to accomplish:
If A23 = "Please See DME", then unprotect (pw=richie13) and unlock D23, then protect (pw=richie13) sheet again
If A24 = "Please See DME", then unprotect (pw=richie13) and unlock D24, then protect (pw=richie13) sheet again
If A25 = "Please See DME", then unprotect (pw=richie13) and unlock D25, then protect (pw=richie13) sheet again
and so on and so on.
I have a workbook that no one seem to know who set a password on Unprotect Shared Workbook. I can't even select the cells to copy.
View 2 Replies View RelatedI 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 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.
Spreadsheet was created by me and worksheets locked by me with password. Data is maintained by another on the network. Since acquiring a new PC with xl 2003 replacing 2000, none of the worksheets will allow me to unprotect. The "unprotect" menu option is not available (shaded out), and it says "shared" at the top.
I have numerous spreadsheets that work this way, but this is the only one that has this problem. How can I unprotect it to make modifications?
When I protect the worksheet with the spinbutton, the spinbutton is no longer accessible. How can I modify the code below (provided by Leith Ross) to make the spinbutton accessible in a protected sheet?
View 3 Replies View RelatedI need a macro code to unprotect all sheets in a book. The sheets may range from 3 to 20 sheets, with a common password. I want to keep this as contol. Only one person will be using this.
View 9 Replies View RelatedI have a workbook that has several macros. When the workbook is saved, it cycles through and kicks off several of them.
This works fine, no errors. I have another macro that saves and then emails the workbook. When this macro runs, the same set of macros is launched due to the save method however it errors during one of the macros.
Basically what's happening is that the one macro (derivecountry) unprotects a sheet and inputs a data value. This macro works fine when run independantly but when it runs as a result of the email macr, the sheet does not get unprotected and the error results.
Here is the code line that runs to unprotect but doesn't always work
ThisWorkbook.Sheets(strShtName).Activate
ActiveSheet.Unprotect (strPassword)
I've also tried
ActiveSheet.Unprotect password:=strPassword
I'm working with this workbook and suddenly the worksheets protected! Although I didn't make any password. I checked it but, I coulden't find any password. see code maybe there is a mistake.
View 7 Replies View RelatedI 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.
We have a macro that opens a spreadsheet and pastes data from another sheet into it.
Is there a macro statement that will unprotect the spreadsheet if it is saved with protection (it is not password protected)?
It gives me a error and says my password is wrong. I have user names set to return the password so that it is unlocked for certain people. It works for unprotecting the sheets. I modified it and added it back in to try to do it for the workbook.
[Code] .........
I realize that they " and () around the password are differant, but that's how I last left them. I have tried several combinations and it just gives me more errors saying expected end of statement, etc.
I have a spread sheet that is protected. I would like to have cell G5 unprotected when a checkbox is checked. I am able to unprotect the entire sheet by checking the box but I only want G5 unprotected. My current macro that unprotects the entire sheet is
Sub Checkbox_Clear2()
Sheets("Input_Personnel").Unprotect ("1")
If Range("J12") = False Then Range("G5").ClearContents
End Sub
Is there a way to change the highlighted line where it only unprotects cell "G5".
I have a code that is supposed to go through a folder, open the xls file, replace all of the formulas with values only, then save as a xlsx file to get rid of the macros, however when we get into the next workbook it will not unprotect the sheet. instead it unprotects the origional sheet I sweat this worked last week when i was writing the sections and testing but maybe it didnt. The problem is without unprotecting the sheet i can not paste to it
Workbook 1 opens up, find that the date is expired, then goes through the folder and opens a workbook, is then supposed to unprotect the sheets ( this is where the problem comes in) save all the formulas as values and then as XLS in another folder.
I turned the text red in the offending area, everythign up to this point seems to work fine.
Private Sub Workbook_Open()
Dim printquotes As String
Dim OrigDate As Date
letmesave = True
OrigDate = ActiveSheet.Range("n2").Value
[Code] ......
I have a protected worksheet showing results of users maths tests. When users try to add data to this worksheet, a Windows Office window appears, advising it is a protected sheet, then gives instructions on how to Unprotect it.
I do not want users unprotecting this particular worksheet.
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.
way for other staff to do the following...
Open an exiting protected spreadsheet,
SaveAs [newfilename]
Unprotect new sheet
Edit new sheet
Protect new sheet
Save it.
Hence we how have two protected sheets with different filenames and different contents.
Then, next time, the teacher will start again with the new spreadsheet file resulting in yet another version of the same data. etc etc.
I am working on a worksheet that has been protected off and on. I unprotected this AM, but XL will not allow changes and instructs to unprotect the WS, but it is already unprotected. The WB is not protected and never was. I am at a loss as to how to work around this problem.
View 9 Replies View RelatedWe have created a spreadsheet that needs to be protected as it is viewed by several users. We would like, if possible, to update this daily with as little human intervention as possible.
Can we use VBE and the on worksheet open event to unlock the password protection, update the sheet and then lock it again? I know that you can read who opens the sheet using code similar to the following but don't know whether the password protection can be "unlocked" utilising this method.
I have an educational workbook which I am not familiar with. I cannot open vb project in vbe. When I try to open it says "project is unviewable". As far as I am concerned it should ask a password. But it doesn't. Can somebody tell me why. I am not asking somebody to crack it for me. I just want to learn why vbe doesn't ask a password. I think the only possibility is the creator of the program want nobody to open the code forever.
View 4 Replies View RelatedI need to be able to protect the sheets in this workbook but then I need them to be unprotected when I click the Save File As button and then have them saved again whenever the file is opened back up.
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 have a workbook that is protected by password. I have to unprotect sheets.
How can I unprotect sheets and workbooks while I am updating or changing?
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'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 RelatedI 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 a macro or other means to unprotect a sheet or workbook when the password is either forgotten or doesn't work?
View 1 Replies View Related