Excel 2013 VBA Code for?
1. User inputs data in row A2:U2 without needing password
2. Press Save - Data in Row A2:U2 is then locked (password protected)
3. User inputs data in row A3:U3 without needing a password
4. Press Save - Data in Row A3:U3 (and A2:U2) is then locked (password protected)
5. Repeat steps for rows A4:U4…A65XXX:U65XXX
Re: Protecting/Locking Rows with Data while Rows without data are Unprotected/Unlocke
Could you not lock the whole A2:U65 (if data is present) on save?
Re: Protecting/Locking Rows with Data while Rows without data are Unprotected/Unlocke
Technically yes, but the worksheet is filled out one row at a time and needs to be saved (password protected) after each row is complete. The next (blank) row needs to be unprotected so the user can fill out without providing a password.
Re: Protecting/Locking Rows with Data while Rows without data are Unprotected/Unlocke
Hmm, are you wanting this to be saved automatically, or is the save to be down by user? If automatic, at what point should it be saved (when cells A-U are populated? Should there be blanks?). What is the definition of a completed row?
Re: Protecting/Locking Rows with Data while Rows without data are Unprotected/Unlocke
Not automatically. The user only knows how to input data and hit the save button. Completed row: The user will first populate row 2 (A2 thru U2) with number data. The user will then hit save. Row 2 then needs to be locked. The user then populates row 3 (A3 thru U3), hits save, etc. for all rows until the end of the spreadsheet. The user does not know the password and can't change a 'saved' row, but can input data in the next blank (no inputted data) row.
Re: Protecting/Locking Rows with Data while Rows without data are Unprotected/Unlocke
does this suffice? You need to put the code in "ThisWorkbook" in the Excel Objects NOT the module. You also need to change "Sheet1" to whatever sheet you are protecting, and also the "Password" needs to be changed. Remember to also add a password onto the VBA otherwise a user could read the sheet password...
Let me know what you think
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
With ThisWorkbook.Worksheets("Sheet1").Columns(1)
LstRw = .Find(What:="*", LookIn:=xlValues, _
After:=.Cells(1), SearchDirection:=xlPrevious).Row
End With
ThisWorkbook.Sheets("Sheet1").Unprotect Password:="Password"
ThisWorkbook.Sheets("Sheet1").Activate
Cells.Select
Selection.Locked = False
ThisWorkbook.Sheets("Sheet1").Rows("1:" & LstRw).Select
Selection.Locked = True
ThisWorkbook.Sheets("Sheet1").Cells(LstRw + 1, 1).Select
ThisWorkbook.Sheets("Sheet1").Protect Password:="Password"
End SubRe: Protecting/Locking Rows with Data while Rows without data are Unprotected/Unlocke
Works Perfectly! Appreciated.
Don’t have an account yet? Register yourself now and be a part of our community!